At 4:16 PM -0600 2000-01-03, Paul DuBois wrote:
>At 4:17 PM -0500 2000-01-03, Bob Strouper wrote:
>>This is what I am doing at the moment:
>>CREATE TABLE sequence (
>>ID mediumint(12) DEFAULT '0' NOT NULL auto_increment,
>>PRIMARY KEY (ID)
>>"UPDATE sequence SET id=last_insert_id(id+1)
>>"select last_insert_id() as id from sequence";
>>Will id now be unigue no matter what?
>Yes. See above.
Arggghhh. I read the example incorrectly. When you have
a PRIMARY KEY that is an AUTO_INCREMENT value, you should
insert a row with the id column set to NULL to generate a
new id number. In this case, each row in the table will have
a unique value in the id column.
Another technique, which you can use for a table with a
single row, is to create the table with your MEDIUMINT
column (non-AUTO_INCREMENT or PRIMARY KEY), and set it
to 0 initially. Thereafter, when you need a new sequence number,
use your UPDATE and SELECT queries as you are doing above. The
form of the LAST_INSERT_ID() function that is used with an
argument isn't used with a PRIMARY KEY.
CREATE TABLE sequence (id MEDIUMINT(12)); # create table
INSERT INTO sequence SET id = 0; # initialize it
UPDATE sequence SET id=LAST_INSERT_ID(id+1); # generate new number
SELECT LAST_INSERT_ID() AS id FROM sequence; # find out what it is
Paul DuBois, paul@stripped