How to Efficiently Retrieve Auto-Increment IDs After Bulk Inserts in MySQL?

Getting Auto-Increment IDs from Bulk Inserts in MySQL

One of the common tasks in database management is inserting multiple rows into a table and retrieving the auto-increment IDs generated for each row. While retrieving the last inserted ID is straightforward using the LAST_INSERT_ID() function, bulk insertions pose a challenge in obtaining an array of all generated IDs.

Traditional Method: Insertion Throgh a Temporary Table

Previously, a common technique involved inserting the new IDs into a temporary table and using a subquery to retrieve them. However, this approach requires additional steps and introduces performance overhead.

Using LAST_INSERT_ID() and ROW_COUNT() with InnoDB

Recent versions of MySQL, particularly those utilizing the InnoDB storage engine, provide an alternative method for retrieving bulk insert IDs. This method leverages the LAST_INSERT_ID() function along with ROW_COUNT() to extract the range of auto-increment values assigned.

Procedure:

1. Configure the innodb_autoinc_lock_mode system variable to 0 (traditional) or 1 (consecutive). This ensures that auto-increment values are assigned sequentially for bulk inserts.
2. Execute the bulk insert statement.
3. Retrieve the first ID from LAST_INSERT_ID().
4. Calculate the last ID by adding ROW_COUNT() - 1 to the first ID.

Example:

INSERT INTO `my_table` (`name`, `email`) VALUES
('John Doe', 'john.doe@example.com'),
('Jane Doe', 'jane.doe@example.com'),
('Peter Jones', 'peter.jones@example.com');

SET @first_id = LAST_INSERT_ID();
SET @last_id = @first_id + ROW_COUNT() - 1;

SELECT * FROM `my_table` WHERE `id` BETWEEN @first_id AND @last_id;

Benefits:

• No temporary tables: Eliminates the need for additional insert and subquery operations.
• Improved performance: Provides a more efficient way to retrieve bulk insert IDs.
• Compatibility with InnoDB: Leverages the features of the InnoDB storage engine to ensure sequential ID assignment during bulk insertions.

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