如何在 MySQL 中使用 `ON DUPLICATE KEY UPDATE` 进行 upsert 操作

简介

在许多情况下，您可能希望确保表中的记录存在且没有冲突条目。实质上，您希望查找并修改当前记录（如果存在），或者在不存在时添加一个包含您所需值的新记录。这通常被称为“upsert”操作（“insert”和“update”的组合）。

MySQL 允许您使用 ON DUPLICATE KEY UPDATE 子句来修改 INSERT 命令，从而执行此操作。在本指南中，我们将介绍如何使用此结构来更新现有条目的值（如果存在），否则将其作为新行添加到表中。

如何使用 INSERT...ON DUPLICATE KEY UPDATE 结构

插入或更新操作的基本语法如下：

INSERT INTO my_table (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6),
    (value7, value8)
ON DUPLICATE KEY UPDATE
    <column1> = <value1>,
    <column2> = <value2>;

在 ON DUPLICATE KEY UPDATE 子句之后可以提供多个列，每个列都定义了如果与现有记录发生冲突时新值应该是什么。

为了演示此功能，我们假设有一个名为 director 的表，其列和填充数据如下：

CREATE TABLE director (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    latest_film VARCHAR(200)
);

INSERT INTO director (name)
VALUES
    ('frank'),
    ('bob'),
    ('sue');

表中的数据如下所示：

SELECT * FROM director;

+----+-------+-------------+
  id | name  | latest_film |
+----+-------+-------------+
   1 | frank | NULL        |
   2 | bob   | NULL        |
   3 | sue   | NULL        |
+----+-------+-------------+
3 rows in set (0.00 sec)

如果您尝试插入另一行，其 id 列为“3”，MySQL 将通知您与现有行存在冲突：

INSERT INTO director (id, name) VALUES (3, 'susan');

ERROR 1062 (23000): Duplicate entry '3' for key 'director.PRIMARY'

如果我们预料到这种可能性并希望用新信息更新现有行，我们可以避免这个错误。ON DUPLICATE KEY UPDATE 子句允许我们这样做：

INSERT INTO director (id, name) VALUES (3, 'susan')
ON DUPLICATE KEY UPDATE name = 'susan';

Query OK, 2 rows affected (0.00 sec)

MySQL 将发生更新的 ON DUPLICATE KEY UPDATE 视为影响了两行。如果没有发生冲突并且添加了新记录，则会显示影响了一行。如果找到现有记录但列已具有正确值，则不会报告任何行受影响。

您可以通过输入以下内容确认该行已用新信息更新：

SELECT * FROM director;

+----+-------+-------------+
  id | name  | latest_film |
+----+-------+-------------+
   1 | frank | NULL        |
   2 | bob   | NULL        |
   3 | susan | NULL        |
+----+-------+-------------+
3 rows in set (0.00 sec)

如何一次性插入或更新多条记录

如果您尝试同时插入或更新多条记录，则要设置的每个列的值可能取决于哪些记录发生了冲突。例如，如果您要插入四行新数据，但第三行的 id 列与现有记录冲突，则您很可能希望根据您为第三行准备的数据来更新现有行。

MySQL 允许您使用 VALUES() 函数来引用这些提议的数据。该函数接受列名作为参数，并提供 INSERT 语句部分中给定的值。

基本语法如下：

INSERT INTO my_table (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6),
    (value7, value8)
ON DUPLICATE KEY UPDATE
    <column1> = VALUES(<column1>),
    <column2> = VALUES(<column2>);

这告诉 MySQL 使用与冲突行关联的数据来更新值。

要了解这是如何工作的，我们回到之前的 director 表：

SELECT * FROM director;

+----+-------+-------------+
  id | name  | latest_film |
+----+-------+-------------+
   1 | frank | NULL        |
   2 | bob   | NULL        |
   3 | susan | NULL        |
+----+-------+-------------+
3 rows in set (0.00 sec)

假设我们想确保以下记录存在于表中：

+----+--------+-------------+
  id | name   | latest_film |
+----+--------+-------------+
   4 | meg    | NULL        |
   2 | robert | NULL        |
   5 | tamara | NULL        |
+----+--------+-------------+

我们可以创建一个 INSERT...ON DUPLICATE KEY UPDATE 语句，如下所示：

INSERT INTO director (id, name)
VALUES
    (4, 'meg'),
    (2, 'robert'),
    (5, 'tamara')
ON DUPLICATE KEY UPDATE
    name = VALUES(name)

MySQL 将接受该语句，插入两行新数据并更新与现有记录冲突的一行（已有一条 id 为“2”的记录）：

Query OK, 4 rows affected, 1 warning (0.01 sec)
Records: 3  Duplicates: 1  Warnings: 1

如果您查看表数据，可以看到两条新行按预期出现，并且冲突行的值已更新为相应的新信息：

SELECT * FROM director;

+----+--------+-------------+
  id | name   | latest_film |
+----+--------+-------------+
   1 | frank  | NULL        |
   2 | robert | NULL        |
   3 | susan  | NULL        |
   4 | meg    | NULL        |
   5 | tamara | NULL        |
+----+--------+-------------+
5 rows in set (0.00 sec)

结论

MySQL 的 INSERT...ON DUPLICATE KEY UPDATE 结构允许您在插入数据时避免与现有记录发生冲突。结合 VALUES() 函数，您可以使用它对已存在的记录进行上下文更新，而无需发出多个语句。这一强大功能可以帮助您最大限度地减少在 SQL 语句之外必须使用的检查和条件逻辑。