MySQL 的高效 Upsert 操作：使用 INSERT ... ON DUPLICATE KEY UPDATE

在 MySQL 数据库管理中，常常需要根据数据是否存在，执行插入新行或更新现有行操作。这就是所谓的“Upsert”操作（插入或更新）。

MySQL 提供了 INSERT ... ON DUPLICATE KEY UPDATE 语法，高效地实现 Upsert 功能。其工作原理如下：

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

INSERT 子句指定要插入表中的值。ON DUPLICATE KEY UPDATE 子句定义当表中已存在具有相同主键（或唯一索引）的行时要执行的操作。

例如，考虑以下表格：

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

如果要为 thing_id 为 4815162342 的行插入新数据，将 times_used 列加 1，并将 first_time_used 设置为当前时间戳，可以使用以下 Upsert 查询：

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

如果 thing_id 为 4815162342 的行已存在，则查询将更新 times_used 列，而 first_time_used 保持不变。否则，将插入新行。

INSERT ... ON DUPLICATE KEY UPDATE 是在 MySQL 中执行 Upsert 操作的一种灵活高效的技术。它允许您使用单个查询执行插入和更新操作，从而简化代码并提高性能。