How Can I Efficiently Insert Multiple Rows into MySQL and Retrieve All Their IDs?

Inserting Multiple Rows into MySQL and Retrieving their IDs

Many developers encounter the need to insert numerous rows into a MySQL table efficiently and obtain their corresponding IDs. While conventionally, retrieving the last inserted ID is straightforward using LAST_INSERT_ID(), bulk insertion procedures require a revised approach.

Solution:

For MySQL InnoDB tables, a solution emerges utilizing LAST_INSERT_ID() in conjunction with ROW_COUNT(). This strategy relies on InnoDB's inherent ability to maintain sequential numbering during bulk insertions, granted that the innodb_autoinc_lock_mode parameter is set to either 0 (traditional) or 1 (consecutive).

Therefore, by obtaining the LAST_INSERT_ID(), which represents the first inserted ID, and adding ROW_COUNT() - 1, we acquire the final inserted ID. This method enables us to establish an array of IDs seamlessly.

The above is the detailed content of How Can I Efficiently Insert Multiple Rows into MySQL and Retrieve All Their IDs?. For more information, please follow other related articles on the PHP Chinese website!