How to Retrieve IDs After Inserting Multiple Rows in MySQL?

Inserting Multiple Rows with ID Retrieval in MySQL

Inserting data into a MySQL table typically returns the ID of the newly created row via the LAST_INSERT_ID() function. However, when inserting multiple rows, obtaining an array of IDs can be challenging.

Bulk Insertion with InnoDB

For InnoDB tables using recent MySQL versions, you can utilize the combination of LAST_INSERT_ID() and ROW_COUNT() to retrieve the inserted IDs. InnoDB provides sequential auto-increment IDs for bulk inserts when the innodb_autoinc_lock_mode setting is 0 (traditional) or 1 (consecutive).

Retrieving ID Range

Assuming a bulk insert statement has been executed:

INSERT INTO table (`column1`, `column2`) VALUES ..., ..., ...

You can calculate the range of IDs for the newly inserted rows using the following formula:

INSERTED_ID_RANGE = [LAST_INSERT_ID(), LAST_INSERT_ID() + ROW_COUNT() - 1]

Example

Consider the following bulk insert:

INSERT INTO table (`column1`, `column2`)
VALUES (1, 'value 1'), (2, 'value 2'), (3, 'value 3');

Assuming the LAST_INSERT_ID() after the insertion is 5, the inserted ID range would be:

[5, 5 + ROW_COUNT() - 1] = [5, 5 + 3 - 1] = [5, 7]

Therefore, the array of inserted IDs would be [5, 6, 7].

The above is the detailed content of How to Retrieve IDs After Inserting Multiple Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!