How to Retrieve the Last Insert/Update ID with MySQL's ON DUPLICATE KEY?

MySQL ON DUPLICATE KEY: Retrieving the Last Insert/Update ID

When performing an INSERT or UPDATE statement with ON DUPLICATE KEY, MySQL's insert_id() function typically returns the ID of the inserted row. However, this approach is problematic if the statement updates an existing row instead.

To overcome this limitation and retrieve the ID of both inserted and updated rows without executing multiple queries, MySQL provides a solution:

As mentioned in the MySQL documentation, by passing an expression to LAST_INSERT_ID(), you can make it meaningful for updates. Specifically, if you want to retrieve the last insert or update ID when using ON DUPLICATE KEY, you can employ the following technique:

Assuming "id" is an AUTO_INCREMENT column, modify the INSERT statement to include the following:

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

In this example, for insertions, LAST_INSERT_ID() will return the ID of the newly created row. For updates, it will return the updated row's ID, effectively providing a consistent method to retrieve the last inserted or updated ID for both scenarios.

The above is the detailed content of How to Retrieve the Last Insert/Update ID with MySQL's ON DUPLICATE KEY?. For more information, please follow other related articles on the PHP Chinese website!