get ids of multiple inserted rows

Question

I need to get the list of new ID inserted with a single query(these are autoincremental, I'm making a

INSERT INTO xtable(value1,value2,value3,value4) VALUES (val1,val2,val3,val4), (val1,val2,val3,val4), (val1,val2,val3,val4), (val1,val2,val3,val4), (val1,val2,val3,val4)

So as you see in a single query im inserting 5 rows, how can I get these IDs? I don't want to make a select after insert query to get lastest IDS or scope because it could go wrong if it goes slows and another functionality makes also insert query, there is any way that in the same query INSERT it returns IDs of inserted rows? Thanks!

' there is any way that in the same query INSERT it returns IDs of inserted rows' - no there isn't. Consider adding a column to capture inserted date (or batch) and query on that — P.Salmon
– P.Salmon, Commented Apr 3, 2020 at 13:22

J. Schmale · Accepted Answer · 2020-04-03 13:39:33Z

The MySQL documentation page: https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html says:

For a multiple-row insert, LAST_INSERT_ID() and mysql_insert_id() actually return the AUTO_INCREMENT key from the first of the inserted rows. This enables multiple-row inserts to be reproduced correctly on other servers in a replication setup.

So then if you have the ID of the first one - I would expect that each record in order is assigned the next greater value since the mult-row insert would be completed in a single atomic transaction. So if you know the first ID you just increment the id to get the values assigned to each row in turn. Example if you have 4 rows and the first one gets ID 45 then the rest would be assigne 46, 47, 48.

You might then want to also refer to this documentation page that describes more and gives samples of how to use LAST_INSERT_ID(). https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

Collectives™ on Stack Overflow

get ids of multiple inserted rows

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related