How can I Insert many rows into a MySQL table and return the new IDs?
Solution 1:
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.
Solution 2:
The only way I can think it could be done is if you store a unique identifier for each set of rows inserted (guid) then select the row ids. e.g:
INSERT INTO t1
(SELECT col1,col2,col3,'3aee88e2-a981-1027-a396-84f02afe7c70' FROM a_very_large_table);
COMMIT;
SELECT id FROM t1
WHERE guid='3aee88e2-a981-1027-a396-84f02afe7c70';
You could also generate the guid in the database by using uuid()
Solution 3:
Lets assume we have a table called temptable with two cols uid, col1 where uid is an auto increment field. Doing something like below will return all the inserted id's in the resultset. You can loop through the resultset and get your id's. I realize that this is an old post and this solution might not work for every case. But for others it might and that's why I'm replying to it.
# lock the table
lock tables temptable write;
#bulk insert the rows;
insert into temptable(col1) values(1),(2),(3),(4);
#get the value of first inserted row. when bulk inserting last_insert_id() #should give the value of first inserted row from bulk op.
set @first_id = last_insert_id();
#now select the auto increment field whose value is greater than equal to #the first row. Remember since you have write lock on that table other #sessions can't write to it. This resultset should have all the inserted #id's
select uid from temptable where uid >=@first_id;
#now that you are done don't forget to unlock the table.
unlock tables;
Solution 4:
It's worth noting that @Dag Sondre Hansen's answer can also be implemented in case you have innodb_autoinc_lock_mode set to 2 by simply locking the table before insert.
LOCK TABLE my_table WRITE;
INSERT INTO my_table (col_a, col_b, col_c) VALUES (1,2,3), (4,5,6), (7,8,9);
SET @row_count = ROW_COUNT();
SET @last_insert_id = LAST_INSERT_ID();
UNLOCK TABLES;
SELECT id FROM my_table WHERE id >= @last_insert_id AND id <= @last_insert_id + (@row_count - 1);
Here's a fiddle demonstrating: https://www.db-fiddle.com/f/ahXAhosYkkRmwqR9Y4mAsr/0