Efficient Upsert operation for MySQL: Using INSERT ... ON DUPLICATE KEY UPDATE

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

MySQL provides the INSERT ... ON DUPLICATE KEY UPDATE syntax to efficiently implement the Upsert function. Its working principle is as follows:

INSERT INTO `table_name`
(`column1`, `column2`, ...)
VALUES
(value1, value2, ...)
ON DUPLICATE KEY UPDATE
`column1` = value1_updated,
`column2` = value2_updated,
...

INSERT clause specifies the value to be inserted into the table. ON DUPLICATE KEY UPDATE clause defines the action to be performed when there is a row with the same primary key (or unique index) in the table.

For example, consider the following table:

CREATE TABLE `usage` (
    `thing_id` INT NOT NULL PRIMARY KEY,
    `times_used` INT DEFAULT 0,
    `first_time_used` TIMESTAMP
);

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

INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used`   1

If the row with thing_id of 4815162342 already exists, the query will update the times_used column, while first_time_used remains unchanged. Otherwise, a new line 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 with a single query, simplifying code and improving performance.