php mysql_insert_id on multiple rows?

Question

Hi, Is it possible to get the auto incremented ids from a query which inserts multiple rows? eg:

INSERT INTO table (col1, col2) VALUES (1, 2), (3, 4), (5, 6);

Alteratively, is there a way to find the NEXT auto increment value WITHOUT inserting anything into the table?

Thanks

Answer 1

As far as I know auto_increment does not fill gaps between ids and the operation is atomic. So you can assume that they will be in a row.

Answer 2

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only.

So no, you can't get all the ids easily, but you can assume that they are continuous. You should never rely on assumptions though. It sounds like you want to guess the next id that would come after your bulk insert? If so, do not even attempt this and think of another way to do whatever it is you want to do. :)

Answer 3

To get the next auto_increment id you can use:

SELECT auto_increment FROM information_schema.tables WHERE table_name = 'table-you-want-to-check';

However, you may not want to rely on the auto_increment id or last_insert_id() in your code's logic. For example, let's say two people, A & B, are adding questions to your db at close to the same moment and you're using the last_insert_id() to determine the id of the person's question. What happens when they both submit their question at the same time? Is the last_insert_id() from person A or B?

One method to overcome this issue is to use a "pre-defined" id or guid in addition to or in place of the auto incremented id.