How Can MySQL's ON DUPLICATE KEY UPDATE Return the Affected Row ID?

Can MySQL ON DUPLICATE KEY Return the Last Inserted or Updated ID?

In MySQL, the INSERT ... ON DUPLICATE KEY UPDATE statement allows insert or update operations within a single query. However, obtaining the ID of the affected row can be challenging.

Normally, the LAST_INSERT_ID() function returns the ID of the newly inserted record. After an update operation, it remains meaningless. To resolve this, you can leverage an expression with LAST_INSERT_ID(), as described by the MySQL documentation.

Specifically, create an expression that updates the id field (an AUTO_INCREMENT column in this example) and includes LAST_INSERT_ID(expr) as an expression to make it meaningful for updates.

Example:

Consider a table with an id column as AUTO_INCREMENT. To update or insert rows and retrieve the affected ID:

INSERT INTO table (a, b, c) VALUES (1, 2, 3)
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), c = 3;

This approach ensures LAST_INSERT_ID() provides the ID of the affected row, regardless of insert or update operations, eliminating the need for a second query to obtain the ID.

The above is the detailed content of How Can MySQL's ON DUPLICATE KEY UPDATE Return the Affected Row ID?. For more information, please follow other related articles on the PHP Chinese website!