How to Get the Row ID After a MySQL INSERT/UPDATE with ON DUPLICATE KEY?

How to Retrieve the ID of a Row after INSERT/UPDATE Using MySQL ON DUPLICATE KEY

Inserting or updating data using the ON DUPLICATE KEY clause in MySQL can be a convenient way to handle duplicate key errors. However, it can be challenging to obtain the ID of the affected row after the operation.

One common assumption is that the LAST_INSERT_ID() function only returns the ID of newly inserted rows. However, in an ON DUPLICATE KEY scenario, it may also return the updated ID if a row was modified.

The Solution: LAST_INSERT_ID(expr)

To make LAST_INSERT_ID() meaningful for both inserts and updates, MySQL provides the following workaround:

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

In this example, the id column is assumed to be the AUTO_INCREMENT primary key. By assigning LAST_INSERT_ID(id) to the id column on update, the function returns the updated ID.

Retrieving the Last Insert/Update ID

Once the workaround is in place, you can use the LAST_INSERT_ID() function to retrieve the ID of the row that was inserted or updated:

Copy after login

This approach allows you to retrieve the ID of the row affected by the INSERT/UPDATE operation without the need to run a separate query to fetch it.

The above is the detailed content of How to Get the Row ID After a MySQL INSERT/UPDATE with ON DUPLICATE KEY?. For more information, please follow other related articles on the PHP Chinese website!