How to insert multiple rows in MySQL table and return new IDs?

Question

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

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

Can I retrieve lastInsertId from bulk insert?

Mysql multi-row insert selection statement with last_insert_id()

Answer 1

The only way I think it can be done is to store a unique identifier (guid) for each set of rows inserted Then select the row ID. For example:

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 can also use uuid() to generate guid

in the database

Answer 2

Old thread, but just looked into this, so here it is: If you're using InnoDB on a recent version of MySQL, you can get it using LAST_INSERT_ID() and ROW_COUNT() ID list.

InnoDB guarantees an automatically incrementing sequence number when doing bulk inserts, if innodb_autoinc_lock_mode is set to 0 (legacy) or 1 (continuous). So you can get the first ID from LAST_INSERT_ID() and get the last ID by adding ROW_COUNT()-1.