Search code examples
sqlmariadblast-insert-idsql-returning

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

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.

Solution

  • 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.