Retrieving inserted row ID from insert? (MySQL)

Grofit

Hi there,

This is something that im sure is easy enough to do but could never find any examples when looking. Here is a simple stored proc that shows the problem...

Begin

// Insert a new row into the table 
INSERT INTO random_table (name) VALUES ("someuser");

// Declare a new var to store the new rows id (sorry if the syntax is a bit off)
DECLARE VAR_UserID INT DEFAULT 0
SELECT id FROM random_table INTO VAR_UserID WHERE name = "someuser" LIMIT 1;

// Use the new ID to insert more data into a linking table
INSERT INTO random_table_two(user_id, some_foreign_id) VALUES (VAR_UserID, 1);


End

Now sorry if some of my syntax is slightly off im doing this all off top of my head, but you can see the principle there... Insert, get inserted ID, Insert again...

Now that 2nd statement which does a select for the row isnt the nicest as if by some fluke there is a username with the same name, then you may end up taking their ID instead.

So is there a way to get the ID from the insert statement, which would not only be faster but would make it alot easier to read...

Hope this explains the problem!

CoderDan

I think this si what you are looking for:

INSERT INTO random_table (name) VALUES ("someuser");

INSERT INTO random_table_two(user_id, some_foreign_id) VALUES (LAST_INSERT_ID(), 1);

Grofit

Ah brilliant, is that only valid for the current session? i mean if someone was to have inserted an ID into a different table previously and the first insert didnt succeed and you tried inserting on the 2nd one, would it just not find an ID or would it give the one from before we started trying... if that makes sense...

CoderDan

It's only for the previous insert statement, and if the previous statement had an error, it is undefined, so you can check for this.

for full reference:
Mysql developer reference

Grofit

You sir deserve an award!