At 11:02, 19990722, Christos J. Pontikis wrote:
>I have a problem when last row in a table is deleted and another
>row takes the same ID.
The manual has a discussion of this, under the documentation for
LAST_INSERT_ID():
The last ID that was generated is maintained in the server
on a per-connection basis. It will not be changed by
another client. It will not even be changed if you update
another AUTO_INCREMENT column with a non-magic value (that
is, a value that is not NULL and not 0). 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 value.
This is one way to handle it. Also, it's worth noting that the
behavior of auto_increment keys is going to change in the future,
so that the problem you're having does not exist. When in the
future, I'm not sure.
Tim
