MySQL LAST_INSERT_ID() used with multiple records INSERT statement

mysql get last insert id multiple rows
mysql use insert id in query
mysql last insert
how to get last inserted record in mysql
return value of insert query in mysql
insert multiple rows with same id
sql insert multiple rows and get ids
mysql insert output

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.

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.

MySQL LAST_INSERT_ID Function By Practical Examples, Hi, I have a problem: I have one query that does multiple inserts rows hmm… look like I have to do one insert at a time to get the id… too bad, mysql don't give an array you can use mysql_insert_id() as part of query to insert, can keep If so, last_insert_id() should give you the ID number of the last row  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

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.

Multiple inserts and mysql_insert_id - Databases, When inserting multiple values, the last automatically incremented value is returned. on LAST_INSERT_ID() , which can be used within an SQL statement, see  The LAST_INSERT_ID() function returns the first automatically generated integer (BIGINT UNSIGNED) successfully inserted for an AUTO_INCREMENT column. If you insert multiple rows into the table using a single INSERT statement, the LAST_INSERT_ID() function returns the first automatically generated value only.

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.

MySQL 5.7 Reference Manual :: 27.7.20.3 How to Get the , Use this function after you have performed an INSERT statement into a table that In the case of a multiple-row INSERT statement, mysql_insert_id() returns the this resets values such as the last insert ID and the number of affected rows. MySQL INSERT multiple rows limit In theory, you can insert any number of rows using a single INSERT statement. However, when MySQL server receives the INSERT statement whose size is bigger than max_allowed_packet, it will issue a packet too large error and terminates the connection.

MySQL 5.7 Reference Manual :: 27.7.6.38 mysql_insert_id(), Bug #65849, LAST_INSERT_ID() returns multiple rows under certain column as a result of the most recently executed INSERT statement. MySQL INSERT statement. As stated initially, the INSERT command is a built-in MySQL statement which inserts the specified records into a given table. So, let’s first check the details and see how to use the INSERT command. But first, create a sample table which we’ll use in our examples.

MySQL Bugs: #65849: LAST_INSERT_ID() returns multiple rows , You can also use LAST_INSERT_ID() to delete the last inserted row: LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with  MySQL: INSERT Statement. This MySQL tutorial explains how to use the MySQL INSERT statement with syntax and examples. The MySQL INSERT statement is used to insert a single record or multiple records into a table in MySQL.

LAST_INSERT_ID, Query the [code ]last_insert_id()[/code]. This returns the first id generated by your batch of rows. Then assume the next N consecutive id values are used by your batch of for MySQL's JDBC driver, it works exactly this way when you prepare a statement How do I get mysqli_insert_id() while inserting multiple rows of data? For no InnoDB solution: you can use a procedure don't forgot to set the the delimiter for storing the procedure with ; CREATE PROCEDURE myproc(OUT id INT, IN otherid INT, IN title VARCHAR(255)) BEGIN LOCK TABLES `table1` WRITE; INSERT INTO `table1` ( `title` ) VALUES ( @title ); SET @id = LAST_INSERT_ID(); UNLOCK TABLES; INSERT INTO `table2` ( `parentid`, `otherid`, `userid` ) VALUES (@id

Comments
  • Does your table have an auto-incrementID?
  • the second two insert ids are easily computatable. just add one for each record after the first.
  • @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 ...
  • @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.
  • @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.
  • FYI: This behavior is documented in the actual MySQL manual, not just user comments. See my answer for details.