Why am I not getting back the correct id of my insert on an auto_increment table?

Oh, lovely MySQL. Everyone loves you and everyone forgives you for your subtle insanities. Like auto-increments.

If you have a table that is auto_incrementing, the id of your insert is returned using LAST_INSERT_ID(). This works jim dandy when mysql determines the id. If, on the other hand, you are so presumptuous as to overrule mysql and set your own value of the primary key, then the database goes and pouts in a corner.


mysgl> create temporary table test( id integer not null primary key auto_increment);
mysql> insert into test values (null); select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 | JUST as we expect. Life is good ...
+------------------+

mysql> insert into test values (null); select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 | Things are going good ...
+------------------+

mysql> insert into test values (33); select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |  !! WTF! 
+------------------+

mysql> insert into test values (null); select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                34| !! and now we're back to normal!
+------------------+

For more discussion of LAST_INSERT_ID() gotchas: http://dba.stackexchange.com/questions/21181/is-mysqls-last-insert-id-function-guaranteed-to-be-correct

No comments:

Post a Comment