How Does LAST_INSERT_ID() Behave in Multi-Row INSERT Statements in MySQL?

Returning the Last Insert ID in Multi-Row INSERT Statements Using LAST_INSERT_ID() in MySQL

When executing multiple record insertions in MySQL, understanding the behavior of the LAST_INSERT_ID() function becomes crucial. Unlike single-row insertions where it provides the ID of the inserted row, LAST_INSERT_ID() behaves differently with multi-row INSERT statements.

For example, consider the following query:

INSERT INTO people (name, age)
VALUES ('William', 25), ('Bart', 15), ('Mary', 12);

After executing this statement, one might expect the LAST_INSERT_ID() function to return the ID of the last inserted row (3). However, the behavior of LAST_INSERT_ID() diverges from this expectation.

LAST_INSERT_ID() Returns the First Insert ID

In the context of multiple record INSERT statements, LAST_INSERT_ID() returns the ID generated for the first inserted row only. This behavior is explicitly documented in MySQL's documentation:

Important
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only.

This design decision ensures that the same INSERT statement can be easily reproduced on another server without any issues. By using the ID of the first inserted row, reproducing the intended behavior becomes straightforward.

Implications for Code Design

Understanding this behavior is crucial when designing code that relies on the LAST_INSERT_ID() function for multi-row insertions. Developers should avoid relying on LAST_INSERT_ID() to determine the specific IDs of inserted rows in such scenarios. Alternative approaches for tracking or accessing row IDs in multi-row insertions should be considered.

The above is the detailed content of How Does LAST_INSERT_ID() Behave in Multi-Row INSERT Statements in MySQL?. For more information, please follow other related articles on the PHP Chinese website!