LAST_INSERT_ID and multi-row inserts

22Aug/062

LAST_INSERT_ID and multi-row inserts

A bit of a quiz to see if you're paying attention:

First, we'll set up a couple tables with some auto increment fields:

CREATE TABLE t1 (id INT AUTO_INCREMENT, INDEX(id));
CREATE TABLE t2 (id INT AUTO_INCREMENT, refT1 INT, INDEX(id));

Next, we'll insert a row to one table:

INSERT INTO t1 VALUES (NULL);

Then, we'll see what happens when we insert two rows using LAST_INSERT_ID():

INSERT INTO t2 VALUES (LAST_INSERT_ID(), 1), (LAST_INSERT_ID(), 2);

What happens here? Did the insert go ok, or did we get an error? What happens if we now issue

SELECT * FROM t2;

Show Answer ▼

No errors, because we defined the auto_increment field for t2 not as a primary or unique key, but just with index. Remember that LAST_INSERT_ID() only picks up the last value you inserted using NULL, and not explicitly defined values. So we get two copies of the value '1' in our id field.

+----+-------+
| id | refT1 |
+----+-------+
|  1 |     1 | 
|  1 |     2 | 
+----+-------+

Filed under: MySQL Gotchas Leave a comment

Comments (2) Trackbacks (1) ( subscribe to comments on this post )

Augusto
August 22nd, 2006 - 23:25

all the times i’ve tried such things it gave me the ‘duplicate key’ (i’m sure untill v5.0 MySQL behaves this way).
the only way i’ve achieved this was with:

CREATE TABLE t1 (id INT AUTO_INCREMENT, INDEX(id));
CREATE TABLE t2 (id INT AUTO_INCREMENT, refT1 INT, PRIMARY KEY (id,refT1)) ENGINE=MyISAM;

innodb does not support such things (like a composed primary key: auto_inc + plain_int)

which version are you running?
where’s the catch ? :-)
snoyes
August 23rd, 2006 - 02:42

Yes, if you create t2.id as a primary key, you’ll get the duplicate key error. That’s why the example uses a non-unique index.

The storage engine does not matter – this behavior is the same with MyISAM and InnoDB.

Tested in 4.1.21 and 5.0.22

A Little Noise