Can I extract 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') 

(displayed as PK details table)

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

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

Answer 1

As far as I know, not in mysql .

Answer 2

The MySQL manual contains a detailed description of the behavior of last_insert_id () with instructions for inserting multiple rows . Scroll down a bit to the part with a red vertical bar starting with the word "important."

Answer 3

I think you need to do the first insert one at a time. MySQL last_insert_id will simply provide you with the identifier of the first element that you insert using a statement with multiple insert lines. There may be problems if you have multiple lines of process insertion at the same time.

However (and I did not check this), if you use InnoDB tables, then the statement must be executed in an implicit transaction, so you must be sure that the generated identifiers were sequential.

Another way, again, if you have transactions, is to lock the table and manually update its AUTO_INCREMENT property. If you have 50 rows to insert, for example, then raise it to 50. Then open the table and insert your rows, explicitly using the identifiers that you reserved. You will need transactions for this (or at least table locks) so that you do not read AUTO_INCREMENT, and then ask someone to insert a new row before updating it.

Answer 4

If you used a date-time, not just a date, you can find the inserted identifier based on user values ​​and date-time values, but probably better, so just don’t do a bulk insert if you need this data. Otherwise, you run more queries anyway.

Answer 5

You can add a database field for the GUID and save a random record with each record. then another request can extract identifiers

Answer 6

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