What happens when an INSERT ... ON DUPLICATE KEY statement is followed by LAST_INSERT_ID() when an update is made? Does LAST_INSERT_ID() return the ID of the row updated?
MySQL’s documentation on INSERT … ON DUPLICATE KEY states that…
If a table contains an
AUTO_INCREMENTcolumn andINSERT ... ON DUPLICATE KEY UPDATEinserts or updates a row, theLAST_INSERT_ID()function returns theAUTO_INCREMENTvalue.
It would appear that it does return the row’s ID on conflict when the update is called. Let’s test:
SELECT VERSION(); -- 8.0.19 at the time of writing
CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, other TEXT);
-- add some data to work with
INSERT INTO test (other) VALUES ('test');
SELECT LAST_INSERT_ID(); -- 1
-- "clear" the last insert ID by passing in a value
SELECT LAST_INSERT_ID(0);
-- this should show zero now
SELECT LAST_INSERT_ID();
-- now try an on duplicate key with an update
INSERT INTO test (id, other) VALUES (1, 'test')
ON DUPLICATE KEY UPDATE other = 'changed';
SELECT LAST_INSERT_ID(); -- still zero!
Even when the update is called, the last insert ID is still zero.
When I discovered this issue I was looking for a way to always get back a meaningful value from LAST_INSERT_ID() even when an update happened. This is where something like PostreSQL’s RETURNING clause is handy.
Making LAST_INSERT_ID() Meaningful
The solution is to fake an update on the AUTO_INCREMENT column by passing a value into LAST_INSERT_ID():
INSERT INTO test (id, other) VALUES (1, 'test') ON DUPLICATE KEY
UPDATE id = LAST_INSERT_ID(id), other = 'changed';
SELECT LAST_INSERT_ID(); -- 1 !!
From the docs:
If expr is given as an argument to
LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned byLAST_INSERT_ID().
This solution can even be found in very old versions of the docs. I’m not sure why the current documentation state the last insert ID is meaningful with an update. Maybe a regression. Maybe just some confusion.
Christopher Davis 