DBI/MySQL and LAST_INSERT

Spidy has asked for the wisdom of the Perl Monks concerning the following question:

Greetings fellow monks,

In order to retrieve the ID number for something I've just inserted into a MySQL table, I've been using the following query:

"SELECT LAST_INSERT_ID() FROM table"
[download]

Which has been working well and fine while I'm testing, but I've been wondering: if there are a lot of simultaneous actions, and another row is inserted before the above statement is called, which ID is returned? Is it the ID for the last insert from that specific database connection, or is it the global last ID?

Thanks,
Spidy

My Website
Get Eated!

Comment on DBI/MySQL and LAST_INSERT_ID() Download Code

Replies are listed 'Best First'.
Re: DBI/MySQL and LAST_INSERT_ID() by ikegami (Patriarch) on Nov 07, 2006 at 17:54 UTC
I'll bring Perl into the thread by pointing out that using `$dbh->{mysql_insertid}` will save you from parsing and executing a query.	[reply] [d/l]
Re: DBI/MySQL and LAST_INSERT_ID() by perrin (Chancellor) on Nov 07, 2006 at 17:36 UTC
Read the MySQL docs! It's the last insert on this connection.	[reply]
Re: DBI/MySQL and LAST_INSERT_ID() by RandomWalk (Beadle) on Jan 05, 2008 at 22:13 UTC
There is also a dedicated DBI method for this, last_insert_id. The calling parameters are DB dependent, so be sure to check the DBI documentation. In addition to the caveats mentioned there, one should not wait too long after the insertion to grab the id; the DB handle may still be connected but the id lost. This might be a common problem. (Just tidying up since this old thread shows up in searches).	[reply]