How Can I Retrieve the ID of an Affected Row After an INSERT ON DUPLICATE KEY UPDATE in MySQL?

Retrieving the ID for ON DUPLICATE KEY Updates in MySQL

When executing an INSERT ON DUPLICATE KEY query, obtaining the ID of the affected row can be challenging. Traditionally, this requires running a separate query, as LAST_INSERT_ID() typically only returns the ID for newly inserted rows.

However, there is a workaround that leverages the LAST_INSERT_ID(expr) function. By passing an expression to this function, you can make its output meaningful even for updated rows.

The Solution

To retrieve the ID of the inserted or updated row using a single query:

1. Ensure your table has an AUTO_INCREMENT column, such as id.
2. Use the following query syntax:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE>

By setting the id column as LAST_INSERT_ID(id) in the UPDATE clause, MySQL will store the new AUTO_INCREMENT value in the id column for both inserted and updated rows.

This allows you to retrieve the ID of the most recent operation (either an insert or an update) by simply calling LAST_INSERT_ID(). It effectively combines the functionality of both INSERT and UPDATE statements into a single query.

The above is the detailed content of How Can I Retrieve the ID of an Affected Row After an INSERT ON DUPLICATE KEY UPDATE in MySQL?. For more information, please follow other related articles on the PHP Chinese website!