-1

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

  • 1
    Please tag your MySQL version. – McNets Feb 26 at 8:45
3

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

  • 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. – Akina Feb 26 at 9:48
  • @Akina thanks, I've added it to the answer. – McNets Feb 26 at 11:07
0

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) ;
  • Nice, grammar correction: in case you don't delete any... – Luc H Feb 26 at 9:05
  • @ypercubeᵀᴹ Could you detail some of these use cases please? I only see the one in which you delete rows. – Kimo Feb 26 at 9:13
  • inserts happen asynchronously - so it's possible that between 1st and 2nd inserts more records will be inserted. – Akina Feb 26 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.