DBI/MySQL and LAST_INSERT_ID()

[Spidy]

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"

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

[ikegami]

I'll bring Perl into the thread by pointing out that using $dbh->{mysql_insertid} will save you from parsing and executing a query.

[perrin]

Read the MySQL docs! It's the last insert on this connection.

[RandomWalk]

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