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.

the second two insert ids are easily computatable. just add one for each record after the first. — dqhendricks
– dqhendricks, Commented Jan 9, 2011 at 3:42
@dqhendricks 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 ... — Timo Huovinen
– Timo Huovinen, Commented Oct 19, 2013 at 18:15
@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. — OCDev
– OCDev, Commented Oct 30, 2013 at 12:52
@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. — Timo Huovinen
– Timo Huovinen, Commented Oct 30, 2013 at 13:28

Asaph · Accepted Answer · 2011-10-31 21:00:47Z

52

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.

answered Oct 31, 2011 at 21:00

Asaph

163k25 gold badges204 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Larry Lustig · Accepted Answer · 2011-01-09 03:44:38Z

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.

answered Jan 9, 2011 at 3:44

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

1 Comment

Asaph Over a year ago

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

Marcel Zúbrik · Accepted Answer · 2018-09-24 20:58:33Z

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:

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

    SELECT MAX(id) AS before_max_id FROM table_name`

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

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

SELECT MAX(id) AS after_max_id FROM table_name`

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

Collectives™ on Stack Overflow

MySQL LAST_INSERT_ID() used with multiple records INSERT statement

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related