Home >Database >Mysql Tutorial >How Can I Efficiently Insert Multiple Rows into MySQL and Retrieve All Their IDs?

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

Susan Sarandon
Susan SarandonOriginal
2024-12-06 17:43:12340browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn