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

Retrieving Row ID for INSERT/UPDATE with ON DUPLICATE KEY

Question:

How can you retrieve the ID of a row after performing an INSERT or UPDATE with ON DUPLICATE KEY in MySQL, without having to run separate queries?

Answer:

To achieve this, you can use the LAST_INSERT_ID(expr) function as a workaround. By passing an expression to this function, you can make LAST_INSERT_ID meaningful for updates.

Explanation:

As mentioned in the documentation linked (https://web.archive.org/web/20150329004325/https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html), LAST_INSERT_ID() normally only returns the 'inserted' ID for successful INSERTs.

However, by using LAST_INSERT_ID(expr) in the ON DUPLICATE KEY UPDATE clause, you can make it meaningful for updates as well.

For example, if 'id' is your AUTO_INCREMENT column, you can modify your query as follows:

INSERT INTO table (a) VALUES (0)
ON DUPLICATE KEY UPDATE>

In this case, LAST_INSERT_ID(id) will return the updated ID if an existing record was found and updated, or it will return the ID of the newly inserted record if no duplicate was found.

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