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
INSERT ... ON DUPLICATE KEY UPDATEinserts or updates a row, the
LAST_INSERT_ID()function returns the
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
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 by
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.