Tutorial :how to do multi insert and obtain ids



Question:

I want to insert some data into a table

(id PK autoincrement, val)  

with use multi insert

INSERT INTO tab (val) VALUES (1), (2), (3)   

Is it possible to obtain a table of last inserted ids?

I'm asking becouse I'm not sure if all will in this form: (n, n+1, n+2).

I use mysql inodb.


Solution:1

From the mysql docs:

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.


Solution:2

Here's someone asking the same question on the mysql forums, but no really authoritative answer is given. They land on 'To be safe, lock the table first. Then you can count in the ids being sequential.'

My advice is this:

If you're inserting many rows and don't mind blocking other threads, lock the table, do the inserts, and then unlock. Since the table was locked, you can count on the inserted ids being sequential (or whatever your autoincrement setting is).

If you're doing a small number of inserts or don't want to block other threads, just do them one at a time and call last_insert_id() after each.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »