How does LAST_INSERT_ID() behave with multiple record INSERT statements in MySQL?

LAST_INSERT_ID() Behavior with Multiple Record INSERT Statements

When inserting multiple records into a MySQL table with a single INSERT statement, LAST_INSERT_ID() typically returns the ID of the first inserted record rather than the last.

As confirmed in the MySQL documentation, this behavior ensures that the INSERT statement can be reproduced accurately on other servers. The ID value for subsequent records is not returned because it cannot be consistently assigned across different database servers.

Example:

Consider the following INSERT statement:

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

After executing this statement, LAST_INSERT_ID() will return 1, the ID of the first inserted record (William). This is the expected behavior and ensures that the statement can be replicated without unintended results.

Implications:

In code that handles multiple record insertions, it's important to be aware of this behavior and handle the ID values accordingly. For example, if you need to retrieve the IDs of all inserted records, you may need to use another approach, such as returning the inserted row count and then retrieving the IDs individually.

The above is the detailed content of How does LAST_INSERT_ID() behave with multiple record INSERT statements in MySQL?. For more information, please follow other related articles on the PHP Chinese website!