I noticed that if I prepare a multi-insert statement and execute it into MySQL via PDO, and then request the last_insert_id, I get the first ID of the multiple inserted rows, not the last one. Specifically:
"INSERT INTO test_table (value1, value2, value3) VALUES (1, 2, 3), (1, 2, 3)";
will create these rows on an empty table:
ID value1 value2 value3 1 1 2 3 2 1 2 3
But the last_insert_id will return “1”. Is this a known issue or am I doing something wrong? Could someone verify/test/explain this? I am at a loss at what to do to get the proper last ID, save for doing an actual select which would be WAY slower.
Advertisement
Answer
It’s correct mysql behavior
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
mysql> USE test; Database changed mysql> CREATE TABLE t ( -> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -> name VARCHAR(10) NOT NULL -> ); Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO t VALUES (NULL, 'Bob'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | +----+------+ 1 row in set (0.01 sec) mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO t VALUES -> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | | 2 | Mary | | 3 | Jane | | 4 | Lisa | +----+------+ 4 rows in set (0.01 sec) mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec)