How Does `LAST_INSERT_ID()` Work with Multiple-Record `INSERT` Statements in MySQL?

LAST_INSERT_ID() Behavior with Multiple Record INSERT Statements in MySQL

MySQL's LAST_INSERT_ID() function retrieves the ID generated for the last inserted row. This behavior may differ when using a single-record INSERT statement compared to a multiple-record INSERT statement.

Single-Record INSERT Statements

With a single-record INSERT statement, LAST_INSERT_ID() returns the generated ID as expected, corresponding to the last record inserted.

Multiple-Record INSERT Statements

However, when using a multiple-record INSERT statement, LAST_INSERT_ID() returns the ID corresponding to the first inserted row. This behavior is explicitly documented in the MySQL docs, emphasizing that it allows reproducing the same INSERT statement easily on another server.

Example

Consider the following multiple-record INSERT statement:

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

After this statement executes, LAST_INSERT_ID() will return 1, not 3, indicating the ID generated for the first record ('William') inserted.

Reasoning

This behavior ensures data consistency across servers. If LAST_INSERT_ID() returned the ID for the last inserted row in a multiple-record statement, the INSERT statement would be unreproducible on other servers that assign different IDs to the same rows.

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