MySQL LAST_INSERT_ID() used with multiple records

问题:

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.

回答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.

回答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.

回答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.