Re: Customized auto-increment serial number
On 25-Jan-2004 Hassan Shaikh wrote:
> Hi,
>
> I've a unique requirement where the transaction number should be
> automatically generated (increment). I can't use AUTO_INCREMENT
> column. The TRAN_NO is supposed to be in "MF" format, where:
>
> Field: TRAN_NO CHAR(10)
>
> M = Encoded current month (A: January, B: February, C: March . L:
> December)
> = Current year (e.g. 2004, 2005, ...)
> F = Running serial number in hexadecimal format (so the smallest
> number is 1 and the largest number would be F; the serial
> number will reset to 1 at the beginning of each month)
>
> How can I generate this number using MySQL without running into
> concurrency problem? Also, this is a web-based app and I am planning
> to use PHP for coding. My production environment is MySQL 4.0.17 on
> Linux. Development platform is Windows XP with MySQL 4.0.17.
>
CREATE TABLE stuff (
yr SMALLINT UNSIGNED,
mn ENUM('A",'B', ... 'L"),
seq INT UNSIGNED NOT NULL DEFAULT 0,
...
PRIMARY KEY (yr,mn,seq)
);
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A fun one
On 24-Jan-2004 [EMAIL PROTECTED] wrote: > Here's a fun one to tickle your brains: > > I've got a table holding the results of a bandwidth tester thingie. > Its > columns hold the date and time the test was run, the fully-qualified > machine > name of the computer that took it, its IP address, and the speed in > kilobits/sec. What I'd like to do is compute the average for each > domain: > for example, there are many machine names in the adelphia.com domain, > and > I'd like to get an average of all adelphia.com results...and all > other > domains found in the database, which may increase daily. > See SUBSTRING_INDEX() Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOCK TABLES and multi table UPDATE
On 22-Jan-2004 Michael McTernan wrote: > Hi there, > > Thanks for your quick response! > >> Why all the locks, temp tables and updates? You can just do: >> >> SELECT >> CASE WHEN B.y IS NULL THEN A.x ELSE B.y END AS newX >> FROM >> A LEFT JOIN B ON A.x = B.x > > Spot on - many thanks! I wasn't aware of the CASE function, but I've > certainly learnt something now :) > Also look at SELECT COALESCE(B.y, A.x) ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
