MySQL concurrent multi-row inserts - Insert ID Assumption

Question

In my application, I have a lot of foreign key dependencies, and often insert large numbers of rows. What I have done up until now is run a single insert at a time, and record the insert ID. This tends to take a long time when inserting a large number of rows, even when apache and mysql are run on the same server.

My question is, if I were to alter my application to add a number of rows with a single INSERT, would I be able to assume the ids of each row based strictly upon the last insert id returned by the mysql connection? The issue is that there is the occasional situation where more than one person will be putting large amounts of information into the database at a time.

From what I have been able to determine, it seems safe to assume that when you insert 500 rows, your insert ids will range from (lastInsertID) to (lastInsertID+499), regardless of whether a query from another connection has begun or ended in the time it took to complete, but I want to be sure this is accepted as safe practice.

I am primarily running InnoDB, but there is the occasional MyISAM in there as well.

Thanks All!

-Jer

Answer 1

The mysql_insert_id and the now recommended alternative mysqli_insert_id returns the first entry id from the affected table's AUTO_INCREMENT column of the last query you've ran.

Mysql grouped INSERT statements are inserted iteratively from the first entry AUTO_INCREMENT index. So yes it is safe to assume that the entries inserted from a single INSERT statement are contiguous.

Comment from the Mysql reference "The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.