Get LAST_INSERT_ID() from a multi-insert in MySQL

Question

I need to get my id value after inserting multiply rows in my MySQL database

INSERT INTO table (`row1`, `row2`, `row3`, `row4`)
VALUES
('value1','value2','value3','value4'),
('value1','value2','value3','value4');

in the fact I need to something like SQL Server - Return value after INSERT but for MySQL and I don't need to return last id because I can do it by myself but I thought it is not good for what I doing is any better way exist for my question?

Does this answer your question? https://stackoverflow.com/questions/55007957/am-i-guaranteed-to-get-consecutive-ids-with-a-single-insert-statement-in-mysql/55009330#55009330 — Bill Karwin, Aug 12 '20 at 22:28
That's not valid syntax. `VALUES` should be specified once and once only. — tadman, Aug 12 '20 at 22:29
no I need to return id value of my single Insert that have many value() — Ahmad Mahmoudi, Aug 12 '20 at 22:31
sorry I mean like this https://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query — Ahmad Mahmoudi, Aug 12 '20 at 22:33
you link shows an example for mysql, your code withou the second values — nbk, Aug 12 '20 at 22:35
When you do a multi-insert in MySQL you will only get the ID of the last inserted value. There is no `RETURNING` in MySQL like you'd get in Postgres and other databases. If you need individual IDs you need to do individual inserts or *presume* that the IDs are sequential and count backwards. — tadman, Aug 12 '20 at 22:35
you coild use A AFTER INSERT TRIGGER and save then ids in a temporary table — nbk, Aug 12 '20 at 22:36
My users can add so many rows for something and I was write a Insert for all things that they inserting in db, I thing it is faster than writing multi-insert and a loop for doing all of these in my server but for knowing which id was added by which user I need to get id what can I do? — Ahmad Mahmoudi, Aug 12 '20 at 22:40
You can insert users input, one by one in background(by a queue). Or save the user id as a secondary key in each row. — Amin Shojaei, Aug 14 '20 at 06:10

score 0 · Answer 1 · answered Aug 14 '20 at 11:27

If you want to insert blocks of rows into the parent and child table, your rows must have unique columns so you can look up the parent rows after insert:

INSERT INTO table (col1, col2, col3, col4)
VALUES
('value1','value2','value3','value4'),
('value5','value6','value7','value8');

INSERT INTO childtable (colx, coly, id_table)
SELECT 1, 2, id FROM table WHERE col1 = 'value1' and col2 = 'value2' and col3 = 'value3' and col4 = 'value4'
UNION ALL
SELECT 1, 2, id FROM table WHERE col1 = 'value5' and col2 = 'value2' and col3 = 'value3' and col4 = 'value4'

Even if there was a method for getting all inserted IDs, you'd have to do the same thing, because tables contain an unordered set of rows. If you got back IDs 11 and 12 for the first insert statement, which row would ID 11 refer to and which ID 12? There is no way to tell that, because the DBMS is free to insert the rows in any order.

If the row data is not unique as in your example, where both rows contain ('value1','value2','value3','value4'), then you must either insert all the data in a loop or use some CTE to number the rows first and play with those numbers.

Anyway, having said all that, I would just insert the data row by row in a loop (i.e. one parent, all its children, next parent, ...), as long as you don't get unbearable performance issues.

Get LAST_INSERT_ID() from a multi-insert in MySQL

1 Answers1