Home >Database >Mysql Tutorial >How Can I Efficiently Insert Multiple Rows into MySQL and Retrieve All 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!