MySQL LAST_INSERT_ID() used with multiple records INSERT statement

Question

If I insert multiple records with a loop that executes a single record insert, the last insert id returned is, as expected, the last one. But if I do a multiple records insert statement:

INSERT INTO people (name,age)
VALUES ('William',25), ('Bart',15), ('Mary',12);

Let's say the three above are the first records inserted in the table. After the insert statement I expected the last insert id to return 3, but it returned 1. The first insert id for the statement in question.

So can someone please confirm if this is the normal behavior of LAST_INSERT_ID() in the context of multiple records INSERT statements. So I can base my code on it.

Artillery

Does your table have an auto-incrementID? – Halsey

the second two insert ids are easily computatable. just add one for each record after the first. – Frymire

@Frymire are you sure the ids will be right? as far as I'm aware inserts with innodb do not lock a certain set of ids and another process might insert an entry in between, although I'm not sure about this for multiple values in INSERT ... VALUES ... – Barracoon

@TimoHuovinen - I agree and understand the concern. However I think it is certain that if all 3 inserts are happening within the exact same insert statement, it is happening within the same transaction and therefore excludes all other inserts from other transactions while they are being inserted. So the inserts within this same statement will always directly follow one another and you can safely rely on numerically incrementing the last insert id to give you the ids for the others that follow. – Ascidian

@FriendlyDev Yes, you are right, thank you for clarifying. Also another note: for INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE MySQL does not know the number of rows that will be inserted, so it will increase by and reserve one autoinc id for every possible insert under InnoDB. Though I am not sure if the ids assigned and gaps are in the same order as the rows in VALUES in this case. – Barracoon

You probably want to wrap this in a transaction to be safe. START TRANSACTION; ... COMMIT; – Fisherman

You can configure MySQL to have an auto-increment-offset of something besides 1. In that case, computing the IDs by incrementing a counter wouldn't work unless you hard-coded the auto-increment-offset value in your code as well. – Petrol

Answer 1

Yes. This behavior of last_insert_id() is documented in the MySQL docs:

Important
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

Northerner

Answer 2

This behavior is mentioned on the man page for MySQL. It's in the comments but is not challenged, so I'm guessing it's the expected behavior.

Pantywaist

FYI: This behavior is documented in the actual MySQL manual, not just user comments. See my answer for details. – Northerner

Answer 3

I think it's possible if your table has unique autoincrement column (ID) and you don't require them to be returned by mysql itself. I would cost you 3 more DB requests and some processing. It would require these steps:

1. Get "Before MAX(ID)" right before your insert:

    SELECT MAX(id) AS before_max_id FROM table_name`

2. Make multiple INSERT ... VALUES () query with your data and keep them:

   INSERT INTO table_name
   (col1, col2)
   VALUES 
   ("value1-1" , "value1-2"), 
   ("value2-1" , "value2-2"), 
   ("value3-1" , "value3-2"), 
   ON DUPLICATE KEY UPDATE
   

3. Get "After MAX(ID)" right after your insert:

   SELECT MAX(id) AS after_max_id FROM table_name`
   

4. Get records with IDs between "Before MAX(ID)" and "After MAX(ID)" including:

   SELECT * FROM table_name WHERE id>$before_max_id AND id<=$after_max_id`
   

5. Do a check of retrieved data with data you inserted to match them and remove any records that were not inserted by you. The remaining records have your IDs:

    foreach ($after_collection as $after_item) {
      foreach ($input_collection as $input_item) {
        if ( $after_item->compare_content($input_item) ) {
          $intersection_array[] = $after_item;
        }
      }
    }

This is just how a common person would solve it in a real world, with parts of code. Thanks to autoincrement it should get smallest possible amount of records to check against, so they will not take lot of processing. This is not the final "copy & paste" code - eg. you have to create your own function compare_content() according you your needs.

Laing

Edit select max(id) with quotes in table name properly. – Cons