How Does MySQL's INSERT ... ON DUPLICATE KEY UPDATE Handle Upsert Operations?

MySQL’s efficient Upsert operation: use INSERT ... ON DUPLICATE KEY UPDATE

In MySQL database management, it is often necessary to insert new rows or update existing rows based on whether the data exists. This is called an "Upsert" operation (insert or update).

MySQL provides the INSERT ... ON DUPLICATE KEY UPDATE syntax to efficiently implement the Upsert function. Here’s how it works:

<code class="language-sql">INSERT INTO `table_name`
(`column1`, `column2`, ...)
VALUES
(value1, value2, ...)
ON DUPLICATE KEY UPDATE
`column1` = value1_updated,
`column2` = value2_updated,
...</code>

The

INSERT clause specifies the value to be inserted into the table. The ON DUPLICATE KEY UPDATE clause defines what to do when a row with the same primary key (or unique index) already exists in the table.

For example, consider the following table:

<code class="language-sql">CREATE TABLE `usage` (
    `thing_id` INT NOT NULL PRIMARY KEY,
    `times_used` INT DEFAULT 0,
    `first_time_used` TIMESTAMP
);</code>

If you want to insert new data for the row thing_id for 4815162342, increment the times_used column by 1, and set first_time_used to the current timestamp, you can use the following Upsert query:

<code class="language-sql">INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used` + 1</code>

If a row with thing_id for 4815162342 already exists, the query will update the times_used column while first_time_used remains unchanged. Otherwise, a new row will be inserted.

INSERT ... ON DUPLICATE KEY UPDATE is a flexible and efficient technology for performing Upsert operations in MySQL. It allows you to perform insert and update operations using a single query, simplifying your code and improving performance.

The above is the detailed content of How Does MySQL's INSERT ... ON DUPLICATE KEY UPDATE Handle Upsert Operations?. For more information, please follow other related articles on the PHP Chinese website!