Mysql – Insert and select the row autoincremented id

innodbMySQL

What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment.

I know how to do it with a Stored Procedure.

Will this line of code work:

BEGIN TRANSACTION;
INSERT INTO table (r1) VALUES (...);
SELECT id FROM table ORDER BY id DESC LIMIT 1;
COMMIT;

And are there any other options?

Best Answer

No. Instead:

INSERT ...
SELECT LAST_INSERT_ID();

LAST_INSERT_ID() is session-specific, so there is never any confusion with other threads also inserting into the same table.

Also, there is no need for BEGIN ... COMMIT for this pair of statements. (You may need it if there are other statements.)

Related Question