How much to insert and get identifiers

Question

I want to insert some data into a table

(id PK autoincrement, val) 

using multi insert

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

Can I get a table of the last inserted identifiers?

I ask because I'm not sure everything is in this form: (n, n + 1, n + 2).

I am using mysql inodb.

Answer 1

From 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 that you can easily reproduce the same INSERT expression against some other server.

Answer 2

Here, someone asks the same question on mysql forums, but there is no really authoritative answer. They land on 'To be safe, first lock the table. Then you can count on the identifiers to be consistent.'

My advice is this:

If you insert a lot of rows and do not mind blocking other threads, lock the table, do inserts, and then open. Since the table has been locked, you can rely on the inserted identifiers to be sequential (or regardless of your auto-increment setting).

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