List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 18 1999 12:09pm
Subject:Re: auto_increment problem
View as plain text  
>>>>> "Sasha" == Sasha Pachev <sasha@stripped> writes:

Sasha> Michael Widenius wrote:
>> >>>>> "Lew" == Lew Barnesson <lewb@stripped> writes:
Lew> Hi,
Lew> I began using auto_increment in an extensive system (46 'C' CGI programs on
Lew> Win NT 4.0 server ) back in the 3.21.x days; back when I didn't know about
Lew> the problem of a delete freeing up a previously used auto_increment-assigned
Lew> number/id. My system assumes uniqueness accross not only the database, but
Lew> archived deleted rows.
Lew> I understand this problem will be resolved in 3.23.0. Is this correct, and
Lew> when can I expect to see this release? My system is going production very
Lew> shortly, so I need to decide to wait for resolution, or to create my own
Lew> unique id assignments.
Lew> My complements to Monty and crew for this very fine mysql.
Lew> Regards to all.
Lew> -Lew Barnesson
>> Yes, MySQL 3.23 will solve this problem.  I will also very shortly release an
>> alpha version of this.  The problem is of course when the 3.23 version
>> will be ready for production use. (We will of course test 3.23 very
>> throughly, but its very hard to test the new ISAM as good as the old
>> ISAM has been tested the past few years)
>> You can also in the MySQL manual find an example how to solve this
>> problem by using the LAST_INSERT_ID() function.
>> Regards,
>> Monty

Sasha> What Monty suggests is a cleaner coding practice. All that a defensive
Sasha> programmer would expect from auto_increment is that the id is unique for
Sasha> the table.

Sasha> -- 
Sasha> Sasha Pachev


I was acutally refering to the following manual section:


     If `expr' is given as an argument to `LAST_INSERT_ID()' in an
     `UPDATE' clause, then the value of the argument is returned as a
     `LAST_INSERT_ID()' value.  This can be used to simulate sequences:

     First create the table:

          mysql> create table sequence (id int not null);
          mysql> insert into sequence values (0);

     Then the table can be used to generate sequence numbers like this:

          mysql> update sequence set id=LAST_INSERT_ID(id+1);

     You can generate sequences without calling `LAST_INSERT_ID()', but
     the utility of using the function this way is that the ID value is
     maintained in the server as the last automatically-generated
     value.  You can retrieve the new ID as you would read any normal
     `AUTO_INCREMENT' value in *MySQL*.  For example,
     `LAST_INSERT_ID()' (without an argument) will return the new ID.
     The C API function `mysql_insert_id()' can also be used to get the


auto_increment problemLew Barnesson17 Apr
  • auto_increment problemMichael Widenius17 Apr
  • Re: auto_increment problemSasha Pachev18 Apr
    • Re: auto_increment problemMichael Widenius18 Apr