Retrieving the last inserted ids for multiple rows

Solution 1:

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

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