In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] writes:
> Dan Rossi <[EMAIL PROTECTED]> wrote on 04/20/2005 12:55:45 AM:
>> Hi there, I was wondering how its possible to get the MAX of a primary
>> key of a table during an insert. I basically want to create a ticket
>> number, but use the primary key as part of the ticket number ie
>> FAULT-0000001 or FAULT-00000002 . I tried during a sub query on an
>> insert but obviouslly not working :|
>>
>> Let me know.
>>
>>
> It sounds like you are generating primary keys based on some letters + an
> incrementing value. That is a very user-friendly method but does not lend
> itself well to MySQL. What you CAN do with mysql is to split your primary
> key into two columns, one text the other an auto_increment-ed numeric.
> Then, when you insert the new row of data you can use LAST_INSERT_ID() to
> get the numeric value assigned to the new row.
Although this trick does what Dan wanted, I would not recommend using
it. First of all, it's extremely unportable - even within MySQL (it
works only with the MyISAM backend). Secondly, it's the same kind of
mistake you do when you insist to assign consecutive numbers to your
rows. This just won't work in the long run - you'll get gaps in the
sequence, either by deletions or by a rollback of a transaction. You
just have to live with the gaps.
Applied to Dan's problem:
CREATE TABLE IncidentData (
IncidentType varchar(8) NOT NULL default '',
TypeSerial int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (TypeSerial)
);
INSERT INTO IncidentData (IncidentType) VALUES ('request');
INSERT INTO IncidentData (IncidentType) VALUES ('request');
INSERT INTO IncidentData (IncidentType) VALUES ('warning');
INSERT INTO IncidentData (IncidentType) VALUES ('fault');
INSERT INTO IncidentData (IncidentType) VALUES ('request');
SELECT CONCAT(UCASE(IncidentType),'-',LPAD(TypeSerial,8,'0')) as Serial
, IncidentType
, TypeSerial
FROM IncidentData;
This returns:
+------------------+--------------+------------+
| Serial | IncidentType | TypeSerial |
+------------------+--------------+------------+
| REQUEST-00000001 | request | 1 |
| REQUEST-00000002 | request | 2 |
| WARNING-00000003 | warning | 3 |
| FAULT-00000004 | fault | 4 |
| REQUEST-00000005 | request | 5 |
+------------------+--------------+------------+
As you can see, there's a gap between REQUEST-00000002 and REQUEST-00000005,
but what's wrong with that?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
