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',??)

Asaph · Answer 1 · 2012-10-10T15:58:18.297

6

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".

edited Oct 10 '12 at 15:58

answered Sep 17 '09 at 18:07

Asaph

159,146
25
197
199

link doesn't work anymore - here is the right one http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id – Horen Oct 10 '12 at 14:32
@Horen I just updated the link to the latest docs (5.6). I wonder why they deleted 5.4. – Asaph Oct 10 '12 at 15:59

score 3 · Answer 2 · answered Sep 17 '09 at 18:02

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.

"should be able to assume that the ids generated were sequential" - not without locking the table fist. — symcbean, Sep 09 '11 at 11:20

score 2 · Accepted Answer · answered Sep 17 '09 at 18:02

2

As far as I know, not in mysql.

answered Sep 17 '09 at 18:02

Michael Krelin - hacker

138,757
24
193
173

score 0 · Answer 4 · answered Sep 17 '09 at 18:21

0

If you used a datetime instead of just a date you could lookup the inserted id based on the user and the datetime values, but it is probably better so just not do a bulk insert if you need that data. Otherwise you end up running more queries anyway.

answered Sep 17 '09 at 18:21

Robert Swisher

1,300
11
12

Not unique - CONNECTION_ID() would be better, but could still cause aliasing with persistent connections unless combined with a transaction flag. – symcbean Sep 09 '11 at 11:19

score 0 · Answer 5 · answered Sep 17 '09 at 18:52

0

you could add a database field for a GUID and store a random one with each record. then another query could extract the IDs

answered Sep 17 '09 at 18:52

Ben Reisner

662
6
16

score -3 · Answer 6 · answered Sep 18 '09 at 09:39

-3

Using SELECT last_insert_id( ) ; query you can get the last inserted id.

answered Sep 18 '09 at 09:39

Can I retrieve the lastInsertId from a bulk insert?

6 Answers6

Linked