Comments - INSERT ON DUPLICATE KEY UPDATE
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
I'm running on MariaDB 10.6.5.
ON DUPLICATE KEY UPDATE does increment the AUTO_INCREMENT of the table even if the row is only updated. This behaviour renders ON DUPLICATE KEY UPDATE useless if you have tables that are updated very often. Say you have a table with 1 million rows and update all the rows daily but only add one row, the AUTO_INCREMENT will be incremented by 1'000'001 every day.
Also, LAST_INSERT_ID() does always return either 0 (if there was no previous INSERT in this session) or the last AUTO_INCREMENT value. Do not expect that LAST_INSERT_ID() will return the PK of an updated row.
The expected unbroken behaviour would be:
- AUTO_INCREMENT will not be increased by an operation that results in an update - LAST_INSERT_ID() should return the PK of the row either if it was INSERTed or UPDATEd.