如何使用 `ON DUPLICATE KEY UPDATE` 在 MySQL 中进行 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 语句之外必须使用的检查和条件逻辑的数量。