如何使用 `ON DUPLICATE KEY UPDATE` 在 MySQL 中执行 upsert 数据

导论

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

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

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

insert 或 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 语句之外使用的检查和条件逻辑的数量。