PHP/MySQL: Retrieving the last Inserted Ids for multiple rows

Question

Hi

When inserting data into a table which has an auto-incremented PK, I need to obtain that key for use in another statement. As many questions show on SO this can be done in php using mysql_insert_id().

However, I have been grouping my inserts together so I insert more than one row at a time. I did this as I guessed there would probably be some performance issue, please advise if I am wrong. Anyway, as far as I am aware however, mysql_insert_id() only returns the last id, when I need the ids for all the inserted rows.

I guess in this case I could:

1. Do some simple maths to calculate all the ids using mysql_insert_id() and the number of rows I have entered. But is this guaranteed be consistently correct?

2. Use multiple insert statements, one for each row

3. Generate my IDs before and no use auto-increment.

I am sure this must be a classic problem, so I am wondering on what the most common and advisable approaches are.

Thanks

Zenna

Answer 1

I advise you to use multiple insert statements and retrieve the insert ID each time. Your option #1 is absolutely not guaranteed to be correct, and #3 is just giving up on the benefits of auto_increment.

Answer 2

There are a couple of approaches that I'd use, if I were me:

First, there's the CreateDate field with a default of CURRENT_TIMESTAMP method. You essentially select CURRENT_TIMESTAMP to get the date, run the insert, and then select id from table where CreateDate > $mytimestamp.

Secondly, you could create a trigger on the table to log after insert and put the new IDs in a little audit table. Truncate the table before checking those IDs, though. I would use this method if your table is millions upon millions of rows.

Answer 3

Calling last_insert_id() gives you the id of the FIRST row inserted in the last batch. All others inserted, are guaranteed to be sequential.

Unless you're doing something very strange, this allows you to calulate the ID of each row easily enough.

Actually the behaviour varies in 5.1 depending on the setting of the innodb auto increment mode parameter; this should not matter. As long as you don't change it from the default, you will see the expected behaviour.

There are occasional cases where this doesn't do what you expect and is not useful - such as if you do an ON DUPLICATE KEY UPDATE or INSERT IGNORE. In these cases, you'll need to do something else to work out the IDs of each row.

But for a plain vanilla INSERT batch, with no values specified for the auto-inc column, it's easy.

A full description of how auto-increments are handled in innodb is here