How to Retrieve Auto-Generated IDs After a Bulk Insert in MySQL?

Bulk Insert with Returned IDs in MySQL

Inserting multiple rows into a MySQL table is a common task. However, if you need to retrieve the newly generated IDs after the bulk insert, the standard LAST_INSERT_ID() function won't suffice.

InnoDB Solution

If your table uses the InnoDB storage engine (a recommended setting for most workloads), MySQL provides a solution using the LAST_INSERT_ID() and ROW_COUNT() functions. Here's how it works:

In InnoDB, bulk inserts using AUTO INCREMENT guarantee sequential numbers when innodb_autoinc_lock_mode is set to 0 (traditional) or 1 (consecutive). This means you can retrieve the first ID using LAST_INSERT_ID() and the last ID by adding ROW_COUNT() - 1.

For example:

INSERT INTO example_table (name) VALUES ('John'), ('Mary'), ('Bob');

-- Get first ID
SELECT LAST_INSERT_ID() AS first_id;

-- Get row count
SELECT ROW_COUNT() AS row_count;

-- Get last ID
SELECT LAST_INSERT_ID() + ROW_COUNT() - 1 AS last_id;

The above is the detailed content of How to Retrieve Auto-Generated IDs After a Bulk Insert in MySQL?. For more information, please follow other related articles on the PHP Chinese website!