A Little Noise

LAST_INSERT_ID and multi-row inserts

Written by

snoyes

in

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;

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 | 
+----+-------+

Comments

3 responses to “LAST_INSERT_ID and multi-row inserts”

  1. Augusto Avatar
    Augusto

    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. snoyes Avatar
    snoyes

    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

  3. Log Buffer #7: A Carnival of the Vanities for DBAs

    […] by his blog, A Little Noise. He offers two short gotchas with MySQL stuff this week: the first, a sneaky ORDER BY quiz; the second involving an auto_increment puzzle. Go and see if you need to click his “Show […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More posts