4

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.

liysd
  • 4,413
  • 13
  • 35
  • 38

2 Answers2

2

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.

webbiedave
  • 48,414
  • 8
  • 88
  • 101
  • ok, but the each next will be incremented by 1?, or is there a posibility of interleave ids from 2 distinctive inserts – liysd May 21 '10 at 14:54
  • 1
    I am 99% sure they're always consecutive. The only thing which would interfere with this is if you set the auto_increment value to a lower number and the `id` column has "holes" – Josh May 21 '10 at 14:57
  • Don't rely on alternative methods of retrieving last insert id (there will always be a race). If you require each id, do your inserts one at a time. – webbiedave May 21 '10 at 14:57
  • I would insert one at a time but it is very slow and I have to optimize it some way. – liysd May 21 '10 at 15:02
  • 1
    @Josh: DBAs can set mysql to not use consecutive ids (auto-increment-increment, auto-increment-offset). – webbiedave May 21 '10 at 15:02
  • 1
    @liysd: If you are guaranteed that ids are consecutive, you can look into locking the table to avoid a race. – webbiedave May 21 '10 at 15:03
  • @webbiedave: Nice. I never knew that! – Josh May 21 '10 at 15:19
  • For anyone else reading this, see [MySql autoincrement column jumps by 10 - why?](http://stackoverflow.com/questions/206751/mysql-autoincrement-column-jumps-by-10-why) – Josh May 21 '10 at 15:22
1

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.

grossvogel
  • 6,694
  • 1
  • 25
  • 36
  • You must first check the settings of auto-increment-increment and auto-increment-offset as they alter the increment rules. – webbiedave May 21 '10 at 15:06
  • Yeah, sure. This question seemed to me to imply that the column definition specifies incrementing by 1. – grossvogel May 21 '10 at 15:30