MySQL / 插入和修改数据

如何在 MySQL 中使用 `ON DUPLICATE KEY UPDATE` 来插入数据

内容

简介
如何使用 INSERT...ON DUPLICATE KEY 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 语句之外必须使用的检查和条件逻辑的数量。

关于作者