How do i get a field of the last inserted row?

Question

Using mysql: I am inserting a row into a table with one auto_increment field left as null (so that gets auto-filled). How would i go about getting the auto incrementing field in a new query that happens right after the insert?

Example: INSERT INTO Food VALUES (1, "pizza");
? SELECT * FROM Food WHERE ? (Get the last inserted row)
Just looking for "pizza" won't work in my case as the only unique field is the auto incrementing one.

I also don't want to just select and order by because of race conditions (these inserts happen asynchronously.)

Answer 1

You can use LAST_INSERT_ID().

_{Quoted from docs:}

LAST_INSERT_ID(), LAST_INSERT_ID(expr)

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

With an argument, LAST_INSERT_ID() returns an unsigned integer.

SELECT LAST_INSERT_ID();

You can find an extended example in this chapter:

28.7.29.3 How to Get the Unique ID for the Last Inserted Row

CREATE TABLE T
(
    ID INT AUTO_INCREMENT PRIMARY KEY,
    FOO INT
);

INSERT INTO T (FOO) VALUES (100);
INSERT INTO T (FOO) VALUES (200);
INSERT INTO T (FOO) VALUES (300);

SELECT * FROM T WHERE ID = LAST_INSERT_ID();

ID | FOO
-: | --:
 3 | 300

Let me add the advice Akina has pointed out in comments:

There is a problem. LAST_INSERT_ID() scope is connection. If connection pool used or reconnection occured, zero will be returned. One of the solutions is to execute all queries within stored procedure.

db<>fiddle here

Answer 2

In case you don't delete any of the rows in your table, this should work :

SELECT * FROM Food WHERE id = (SELECT MAX(id) FROM Food) ;