Difference between returning auto increment key and last insert ID in SQL

Question

In our codebase, we use two different ways to retrieve the auto incremented key of a newly inserted row:

1. INSERT ... RETURNING id

2. SELECT LAST_INSERT_ID()

Where id is INT UNSIGNED NOT NULL AUTO_INCREMENT when created. Both return the same value usually - checked by using the first, then running the second, and comparing the returned values - but apart from the obvious difference that RETURNING may return multiple rows on multiple row inserts, and that it allows returning more than just the key column, for the purpose of retrieving the inserted auto incremented key, what are the practical differences between them?

It may be relevant to know this is an InnoDB table in MariaDB.

Answer 1

The first one definitely returns the id of the inserted row. The second one might return the id of a different row if another row was inserted between the INSERT and the SELECT.

Answer 2

INSERT . . . RETURNING is much more flexible:

• It returns multiple expressions if you like, so you can return more values from the inserted row than just the auto-incremented id.
• It returns multiple rows.

LAST_INSERT_ID() is really designed for single row inserts and to just return one id. I recommend using INSERT . . . RETURNING.

Answer 3

In case of inserting multiple rows, the INSERT-RETURNING will return all IDs have been inserted whereas LAST_INSERTED_ID() will return only one