Messing with LAST_INSERT_ID()

July 5, 2007

Messing with LAST_INSERT_ID()

Filed under: MySQL Gotchas — snoyes @ 10:56 am

Time for another MySQL Community Quiz:

Everybody knows that LAST_INSERT_ID() returns the value most recently created in an auto_increment column. Fewer of us know that you can pass a value to LAST_INSERT_ID(), and that value will be returned for the next call to LAST_INSERT_ID(). For example,

INSERT INTO table (someNonAutoIncrementField) VALUES (LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

The INSERT inserts ’42’ into the table, and the SELECT returns 42.

So, here’s the question: What if we pass an expression to LAST_INSERT_ID and also use an auto_increment field in the same statement?

CREATE TABLE test (id int auto_increment primary key, field int);
INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

Show Answer ▼

You get the auto_increment field, at least in this case:

mysql> INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

And for a follow up, does it matter if you swap the insertion around?

INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
SELECT LAST_INSERT_ID();

Show Answer ▼

Nope.

mysql> INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

The manual warns about mixing LAST_INSERT_ID() and LAST_INSERT_ID(expr). This may fall under that same caveat.

Comments (0)

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

A Little Noise

July 5, 2007