| Bug #55463 | incorrect value returned by last_insert_id() function | ||
|---|---|---|---|
| Submitted: | 22 Jul 2010 0:25 | Modified: | 22 Jul 2010 4:38 |
| Reporter: | Liliya Strupinskaya | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S5 (Performance) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[22 Jul 2010 4:38]
Valeriy Kravchuk
This is clearly documented in the manual, http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id: "Important 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."
[7 Sep 2016 20:04]
Don Cohen
This clear documentation really ought to be mentioned in other places where people (like me) are likely to look, such as http://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html which seems to very clearly state the exact opposite.
Description: When multiple values are inserted in one insert statement then last_insert_id() displays the first value inserted: mysql> CREATE TABLE animals ( -> id MEDIUMINT NOT NULL AUTO_INCREMENT, -> name CHAR(30) NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.09 sec) mysql> mysql> INSERT INTO animals (name) VALUES -> ('dog'),('cat'),('penguin'), -> ('lax'),('whale'),('ostrich'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> select * from animals; +----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+ 6 rows in set (0.00 sec) How to repeat: mysql> CREATE TABLE animals ( -> id MEDIUMINT NOT NULL AUTO_INCREMENT, -> name CHAR(30) NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.09 sec) mysql> mysql> INSERT INTO animals (name) VALUES -> ('dog'),('cat'),('penguin'), -> ('lax'),('whale'),('ostrich'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> select * from animals; +----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+ 6 rows in set (0.00 sec)