List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 3 2000 10:47pm
Subject:Re: sequence table: Definitive answer please!
View as plain text  
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)
>>);
>>
>>...now:
>>
>>"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
Thread
sequence table: Definitive answer please!Bob Strouper4 Jan
  • Re: sequence table: Definitive answer please!Paul DuBois4 Jan
    • Re: sequence table: Definitive answer please!Paul DuBois4 Jan
  • Re: sequence table: Definitive answer please!Tonu Samuel4 Jan
    • Re: sequence table: Definitive answer please!Benjamin Pflugmann4 Jan
  • Re: sequence table: Definitive answer please!Benjamin Pflugmann4 Jan
  • Re: sequence table: Definitive answer please!hypnos4 Jan
Re: sequence table: Definitive answer please!Bob Strouper4 Jan
  • Re: sequence table: Definitive answer please!Paul DuBois4 Jan
  • Re: sequence table: Definitive answer please!Benjamin Pflugmann4 Jan
Re: sequence table: Definitive answer please!Benjamin Pflugmann4 Jan
Re: sequence table: Definitive answer please!Bob Strouper4 Jan
  • Re: sequence table: Definitive answer please!Benjamin Pflugmann4 Jan
Re: sequence table: Definitive answer please!Bob Strouper4 Jan