LAST_INSERT_ID returning wrong value after multiple row insert in a single query

[Michael Widenius]

Hi!

>>>>> "Luc" == Luc Van der Veken <lucvdv@stripped> writes:

Luc> # This occurs in MySQL 3.23.36, win32 version.
Luc> # I haven't seen anything about it in the 3.23.37 change list,
Luc> # nor have I seen a report of it in the win32 list
Luc> # or in the bug tracker on sourceforge.
Luc> #
Luc> # Statements to reproduce (you can use this message as script):

Luc> # drop table if exists test9;
Luc> create table test9 (i1 int auto_increment primary key, i2 int);
Luc> insert into test9 (i2) values (11),(12),(13);
Luc> select last_insert_id();
Luc> insert into test9 (i2) values (14),(15),(16);
Luc> select * from test9;
Luc> select last_insert_id();

Luc> # The last_insert_id() function returns the value corresponding
Luc> # to the _first_ inserted row for each INSERT query,
Luc> # instead of the last (i.e. values 1 and 4 instead of 3 and 6
Luc> # here, respectively).

This is done intentionally.

I have now added the following to our manual:

-------
If you insert many rows at the same time with an insert statement,
LAST_INSERT_ID() returns the value for the first inserted row.
The reason for this is so that you it makes it possible to easily reproduce
the same INSERT statement against some other server.
-------

Why did you need the value for the last inserted row?

LAST_INSERT_ID() is mainly meant to make it possible to easily get a reference
to a inserted row, (to be saved into another table).  When you insert
many rows at a time, LAST_INSERT_ID() is not usable in this context.

Regards,
Monty