How can I Insert many rows into a MySQL table and return the new IDs?

mysql bulkinsert
Peacemoon picture Peacemoon · Sep 7, 2011 · Viewed 44.3k times · Source

Normally I can insert a row into a MySQL table and get the last_insert_id back. Now, though, I want to bulk insert many rows into the table and get back an array of IDs. Does anyone know how I can do this?

There are some similar questions, but they are not exactly the same. I don't want to insert the new ID to any temporary table; I just want to get back the array of IDs.

Can I retrieve the lastInsertId from a bulk insert?

Mysql mulitple row insert-select statement with last_insert_id()

Answer

Dag Sondre Hansen picture Dag Sondre Hansen · May 16, 2013

Old thread but just looked into this, so here goes: if you are using InnoDB on a recent version of MySQL, you can get the list of IDs using LAST_INSERT_ID() and ROW_COUNT().

InnoDB guarantees sequential numbers for AUTO INCREMENT when doing bulk inserts, provided innodb_autoinc_lock_mode is set to 0 (traditional) or 1 (consecutive). Consequently you can get the first ID from LAST_INSERT_ID() and the last by adding ROW_COUNT()-1.

Related questions

insert multiple rows via a php array into mysql

I'm passing a large dataset into a MySQL table via PHP using insert commands and I'm wondering if its possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of a …

Read More
MySQL LOAD DATA Error (Errcode: 2 - "No such file or directory")

I am trying to load data into a table of my MySQL database, and getting this error. LOAD DATA LOCAL INFILE 'C:\Users\Myself\Desktop\Blah Blah\LOAD DATA\week.txt' INTO TABLE week; Reference: this The path is hundred …

Read More
MySql Bulk insert

I want to insert some 4K rows in the MySql db. I don't want to fire 4k 'insert' queries. Is there any way by which I can fire only one insert query to store those 4k rows in the db. …

Read More