4

I'm currently using $mysqli->insert_id to retrieve auto-allocated primary keys from a MySQL table when new rows are inserted. This works fine on single inserts. It's a simple case of 

$result = $mysqli->query($query);
$NewPrimaryKey = $mysqli->insert_id;

However, I've learned (from this site actually) that multiple inserts are better done with an extended insert:

insert into table (name1,name2,name3) values 
('blah1','blah2',blah3'), 
('blah1','blah2',blah3'), 
('blah1','blah2',blah3'))

This also works great – except when I want to know what keys were auto-allocated.

The PHP manual says:

When running extended inserts on a table with an AUTO_INCREMENT field, the value of mysqli_insert_id() will equal the value of the first row inserted.

My testing appears to confirm this.

Note: I don't want just the first or just the last.

I don't want to have to revert to thousands of single inserts just so that I can get the auto_allocated key on an individual basis.

Does anyone know of a way to do extended inserts and to get back all the keys that were allocated (presumably in a array)?

It did occur to me to just read and retain the latest existing key before doing the extended write and then to calculate the expected values of the allocated keys, but this does seem a little bodgified.

Tom Walters
  • 15,366
  • 7
  • 57
  • 74
Simon Roberts
  • 617
  • 1
  • 7
  • 14
  • Have you considered an ORM? – SoWhat Mar 08 '13 at 15:14
  • 1
    Are you sure you need to do a batch insert? If this is a scenario where you _need_ all of the IDs back in your application, the performance benefit of using a batched insert may be negligible (since I'm assuming you need these to issue follow-up queries for each of the items to insert related data)? – Colin M Mar 08 '13 at 15:18
  • I hadn't heard of ORM. I had a quick look but it seems like a new set of skills to learn. I don't really want to do that for this single problem, but thanks. – Simon Roberts Mar 08 '13 at 15:22
  • I find that batch inserts make a big difference to performance. I keep timings of my scripts and in this case, the difference in run times between using single inserts and batch inserts is 1.2 seconds against 0.2 seconds. An (unscientific) x6 difference. That will matter when processing thousands of records at once. – Simon Roberts Mar 08 '13 at 15:25

2 Answers2

0

I don't think that you can get all of the newly inserted row IDs, but if insert_id returns the first ID, you can just assume that the inserted IDs is the range of that ID to the number of inserts (which should be the case unless something goes wrong, but then the entire insert should fail since it is atomic). You could also select all of the auto_increment IDs that are greater than or equal to the new insert_id.

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • I did think of this but it just seems a little...manual, if you know what I mean. I was hoping that there would be some kind of MySQL-based solution but if not I may have to go this way. Thanks. – Simon Roberts Mar 08 '13 at 15:28
-1

You can calculate the keys, can't you?

$keys=array();
for($id=mysqli_insert_id();$id<=$id+$totalRecords;$id++)
   $keys[]=$id;

Or use an ORM like doctrine

SoWhat
  • 5,564
  • 2
  • 28
  • 59