Retrieving the index of an inserted row

Question

I'm trying to keep the database tables for a project I'm working on nice and normalized, but I've run into a problem. I'm trying to figure out how I can insert a row into a table and then find out what the value of the auto_incremented id column was set to so that I can insert additional data into another table. I know there are functions such as mysql_insert_id which "get the ID generated from the previous INSERT operation". However, if I'm not mistaken mysql_insert_id just returns the ID of the very last operation. So, if the site has enough traffic this wouldn't necessarily return the ID of the query you want since another query could have been run between when you inserted the row and look for the ID. Is this understanding of mysql_insert_id correct? Any suggestions on how to do this are greatly appreciated. Thanks.

Answer 1

LAST_INSERT_ID() has session scope.

It will return the identity value inserted in the current session.

If you don't insert any rows between INSERT and LAST_INSERT_ID, then it will work all right.

Note though that for multiple value inserts, it will return the identity of the first row inserted, not the last one:

INSERT
INTO    mytable (identity_column)
VALUES  (NULL)

SELECT  LAST_INSERT_ID()

--
1

INSERT
INTO    mytable (identity_column)
VALUES  (NULL), (NULL)

/* This inserts rows 2 and 3 */

SELECT  LAST_INSERT_ID()

--
2

/* But this returns 2, not 3 */

Answer 2

You could:

A. Assume that won't be a problem and use mysql_insert_id

or

B. Include a timestamp in the row and retrieve the last inserted ID before inserting into another table.

Answer 3

The general solution to this is to do one of two things:

1. Create a procedural query that does the insert and then retrieves the last inserted id (using, ie. LAST_INSERT_ID()) and returns it as output from the query.

2. Do the insert, do another insert where the id value is something like (select myid from table where somecolumnval='val')

2b. Or make the select explicit and standalone, and then do the other inserts using that value.

The disadvantage to the first is that you have to write a proc for each of these cases. The disadvantage to the second is that some db engines don't accept that, and it clutters your code, and can be slow if you have to do a where on multiple columns.

This assumes that there may be inserts between your calls that you have no control over. If you have explicit control, one of the other solutions above is probably better.