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

Bulk Insertion with ID Retrieval in MySQL

Inserting multiple rows into a MySQL table and retrieving the new IDs is a common requirement that may not be immediately obvious to implement. This document explains how to achieve this using MySQL's LAST_INSERT_ID() and ROW_COUNT() functions, particularly when using the InnoDB storage engine.

InnoDB's Sequential ID Generation

When utilizing the InnoDB storage engine with MySQL, bulk inserts maintain sequential assignment of AUTO INCREMENT IDs, given that the innodb_autoinc_lock_mode is set to 0 (traditional) or 1 (consecutive).

Retrieving an Array of IDs

To retrieve an array of IDs after a bulk insert, follow these steps:

1. Execute the bulk insert query.
2. Obtain the first ID using LAST_INSERT_ID().
3. Calculate the last ID by adding ROW_COUNT()-1 to the first ID.
4. Generate an array of IDs from the first to last ID incrementally.

Example:

-- Insert bulk rows
INSERT INTO table_name (column1, column2) VALUES (val1, val2), (val3, val4), (val5, val6);

-- Get the first ID
SET @first_id = LAST_INSERT_ID();

-- Get the number of rows inserted
SET @num_rows = ROW_COUNT();

-- Create an array of IDs
SET @ids = ARRAY();

-- Populate the array
WHILE (@i < @num_rows) DO
  SET @ids[@i] = @first_id + @i;
  SET @i = @i + 1;
END WHILE;

This technique efficiently provides an array of newly generated IDs after a bulk insert, allowing for further processing or use within the application logic.

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