ID from insert with multiple rows

Question

Is it safe to assume that the auto increment id from a insert with multiple rows will be incremented by one starting to the mysql_last_insert_id() value? Could there be a concurrent insert (from an other user session) between this multiple insert?

In this example tbl_name has an autoincrement field called id:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9); $nbAffectedRows = mysql_affected_rows(); $idFirstRowInserted = mysql_last_insert_id(); $arrayId = array(); if($nbAffectedRows &gt; 0){ for($i = 0; $i &lt;= $nbAffectedRows; $i++){ $arrayId[] = $idFirstRowInserted + $i; } }

Will $arrayId always contain values incremented by one correponding to the 'id' of the rows inserted ?

One thing you should check is auto_increment_increment, which specifies whether MySQL increments by 1 or another number: show variables like 'auto_increment_increment'; — Ike Walker
– Ike Walker, Commented Jan 12, 2011 at 22:33
Thanks for the information. I checked it and the value is 1 (the deault value). — Darm
– Darm, Commented Jan 12, 2011 at 23:20

German Rumm · Accepted Answer · 2011-01-12 21:05:41Z

1

It will be safe to assume that, if you wrap your INSERTs in a transaction. As a side effect your query will run faster too.

answered Jan 12, 2011 at 21:05

German Rumm

5,8171 gold badge27 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Darm Over a year ago

Do you mean by creating a queue?

German Rumm Over a year ago

I've just read more on auto_inc with InnoDB, it appears there's AUTO_INC lock that is ..kept to the end of current SQL statement. dev.mysql.com/doc/refman/5.1/en/… So your query should be safe by itself

Darm Over a year ago

Thanks for your time German Rumm. Unfortunately, I'm using MyISAM tables.

Steven Over a year ago

From dev.mysql.com/doc/refman/5.0/en/… "The ID that was generated is maintained in the server on a per-connection basis.(...) 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."

Collectives™ on Stack Overflow

ID from insert with multiple rows

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related