>>>>> "Sasha" == Sasha Pachev <sasha@stripped> writes:
Sasha> Michael Widenius wrote:
>> >>>>> "Lew" == Lew Barnesson <lewb@stripped> writes:
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.
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 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