42

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.

T Zengerink
  • 3,983
  • 5
  • 27
  • 31
bogdan
  • 1,179
  • 3
  • 11
  • 18
  • Does your table have an auto-incrementID? – Pekka Jan 09 '11 at 02:44
  • 4
    the second two insert ids are easily computatable. just add one for each record after the first. – dqhendricks Jan 09 '11 at 03:42
  • 3
    @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 Oct 19 '13 at 18:15
  • 3
    @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 Oct 30 '13 at 12:52
  • 1
    @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 Oct 30 '13 at 13:28
  • You probably want to wrap this in a transaction to be safe. START TRANSACTION; ... COMMIT; – 111 Jun 09 '14 at 17:21
  • 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. – jergason Oct 13 '18 at 14:54

3 Answers3

42

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.

Asaph
  • 147,774
  • 24
  • 184
  • 187
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.

Larry Lustig
  • 46,058
  • 13
  • 95
  • 143
  • FYI: This behavior is documented in the actual MySQL manual, not just user comments. See [my answer](http://stackoverflow.com/questions/4637367/mysql-last-insert-id-used-with-multiple-records-insert-statement/7959695#7959695) for details. – Asaph Oct 31 '11 at 21:01
1

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`

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

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

    SELECT MAX(id) AS after_max_id FROM table_name`

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

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

Marcel Zúbrik
  • 21
  • 5