ansaurus

Question

Answer 1

+2 A:

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 2010-05-21 14:50:04

ok, but the each next will be incremented by 1?, or is there a posibility of interleave ids from 2 distinctive inserts

liysd 2010-05-21 14:54:33

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 2010-05-21 14:57:14

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 2010-05-21 14:57:38

I would insert one at a time but it is very slow and I have to optimize it some way.

liysd 2010-05-21 15:02:15

@Josh: DBAs can set mysql to not use consecutive ids (auto-increment-increment, auto-increment-offset).

webbiedave 2010-05-21 15:02:43

@liysd: If you are guaranteed that ids are consecutive, you can look into locking the table to avoid a race.

webbiedave 2010-05-21 15:03:32

@webbiedave: Nice. I never knew that!

Josh 2010-05-21 15:19:30

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 2010-05-21 15:22:08

Answer 2

+1 A:

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 2010-05-21 15:03:04

You must first check the settings of auto-increment-increment and auto-increment-offset as they alter the increment rules.

webbiedave 2010-05-21 15:06:24

Yeah, sure. This question seemed to me to imply that the column definition specifies incrementing by 1.

grossvogel 2010-05-21 15:30:03

ansaurus

tags:

views:

answers:

how to do multi insert and obtain ids

related questions