Can I retrieve the lastInsertId from a bulk insert?

Question

INSERT INTO details (idactivity,user,hours,val,date) VALUES ('981','133','0','10500','2008-07-01'),('981','184','0','2750','2008-07-01'),('981','184','0','2750','2008-07-01')

(iddetails as details table PK)

Is there a way to get the 3 new iddetails and get them to the next bulk insert query?

INSERT INTO activity (idactivity,iddetails) VALUES('981',??),('981',??),('981',??)

Answer 1

There is a detailed discussion of the behavior of last_insert_id() with multi-row insert statements in the MySQL manual. Scroll down a little to the part with the red vertical bar beginning with the word "important".

Answer 2

I think you have to do the initial inserts one at a time. MySQL's last_insert_id will just give you the id of the first element you insert with a multi-line insert statement like that. There may be issues if you have more than one process inserting lines at the same time.

However, (and I haven't verified this,) if you are using InnoDB tables, then the statement should be executed in an implicit transaction, so you should be able to assume that the ids generated were sequential.

Another way, again, if you have transactions, is to lock the table, and update it's AUTO_INCREMENT property manually. If you have 50 rows to insert, for instance, then bump it by 50. Then unlock the table, and insert your rows, explicitly using the ids you have reserved. You will need transations for this (or at least table locks) so that you don't read the AUTO_INCREMENT, and then have someone insert a new row before you update it.