A Little Noise

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 ▼

Comments (2) Trackbacks (1)
  1. 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 ? :-)

  2. 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


Leave a comment