How Does LAST_INSERT_ID() Behave with Multiple Record Inserts in MySQL?

Understanding LAST_INSERT_ID() Behaviour with Multiple Record Inserts

When dealing with multiple record insertions in MySQL, it's essential to comprehend the behaviour of the LAST_INSERT_ID() function. Unlike single-record inserts, LAST_INSERT_ID() exhibits a distinct behaviour when used with multiple-record insert statements.

Expected Behaviour

Intuitively, one might expect the LAST_INSERT_ID() function to return the last inserted ID for a multiple-record insertion. However, this is not the case.

Actual Behaviour

As documented in the MySQL documentation, LAST_INSERT_ID() will return the ID generated for only the first row inserted in a multiple-record insert statement. This is deliberate to enable easy reproduction of the same statement on different servers.

For instance, consider the following multiple-record insert statement:

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

Even though it inserts three rows, the LAST_INSERT_ID() will return the ID for 'William', as it represents the first inserted record.

Implications for Code

This behaviour has implications for your code. If you rely on LAST_INSERT_ID() to capture the IDs of all inserted records in a multiple-record insert, you will only obtain the ID for the first record. To accurately retrieve the IDs of all inserted records, you could utilize the RETURNING clause in conjunction with the INSERT statement or consider alternative approaches such as using a loop to insert records one by one.

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