MySQL - UPSERT 操作

• MySQL UPSERT 操作
• 使用 INSERT IGNORE 进行 UPSERT
• 使用 REPLACE 进行 UPSERT
• 使用 INSERT 和 ON DUPLICATE KEY UPDATE 进行 UPSERT

MySQL UPSERT 操作

MySQL UPSERT 操作将 INSERT 和 UPDATE 合并为一个语句，允许您在表中插入新行或更新现有行(如果行已存在)。我们可以从名称 (UPSERT) 本身理解，其中 UP 代表更新 (UPDATE)，SERT 代表插入 (INSERT)。

本教程介绍在 MySQL 中执行 UPSERT 操作的三种常用方法:INSERT IGNORE、REPLACE 和使用 ON DUPLICATE KEY UPDATE 的 INSERT。

使用 INSERT IGNORE 执行 UPSERT

MySQL 中的 INSERT IGNORE 语句允许您向表中插入新记录。如果已存在具有相同主键的记录，则会忽略错误并且不会插入新记录。

示例

首先，让我们使用以下查询创建一个名为 COURSES 的表 -

CREATE TABLE COURSES(
   ID int,
   COURSE varchar(50) primary key,
   COST int
);

在这里，我们将记录插入到 COURSES 表中 -

INSERT INTO COURSES VALUES 
(1, "HTML", 3000),
(2, "CSS", 4000),
(3, "JavaScript", 6000),
(4, "Node.js", 10000),
(5, "React.js", 12000),
(6, "Angular", 8000),
(7, "Php", 9000);

获得的COURSES表如下 −

现在，我们尝试在以下查询中使用 INSERT INTO 语句插入一条重复记录 -

INSERT INTO COURSES VALUES (6, 'Angular', 9000);

由于无法插入重复记录，因此会导致错误 -

ERROR 1062 (23000): Duplicate entry 'Angular' for key 'courses.PRIMARY'

使用 INSERT IGNORE -

现在，让我们使用 INSERT IGNORE 语句执行相同的操作 -

INSERT IGNORE INTO COURSES VALUES (6, 'Angular', 9000);

输出

正如我们在下面的输出中看到的，INSERT IGNORE 语句忽略了错误 -

Query OK, 0 rows affected, 1 warning (0.00 sec)

验证

我们可以使用以下 SELECT 查询来验证 COURSES 表，看看错误是否被忽略了 -

SELECT * FROM COURSES;

得到的表如下所示 -

使用 REPLACE 进行 UPSERT

MySQL REPLACE 语句首先尝试删除现有行(如果存在)，然后插入具有相同主键的新行。如果该行不存在，则直接插入新行。

示例

替换或更新 COURSES 表中的一行。如果 COURSE 为"Angular"的行已存在，则将使用提供的新值更新其 ID 和 COST 值。否则，将插入一个包含查询中指定值的新行 -

REPLACE INTO COURSES VALUES (6, 'Angular', 9000);

输出

上述查询的输出如下所示 -

Query OK, 2 rows affected (0.01 sec)

验证

现在，我们使用以下 SELECT 查询来验证 COURSES 表 -

SELECT * FROM COURSES;

我们可以在下表中看到，REPLACE 语句在删除重复行后添加了一个新行 -

使用 INSERT 语句和 ON DUPLICATE KEY UPDATE 语句进行 UPSERT

MySQL 中的 INSERT ... ON DUPLICATE KEY UPDATE 语句会尝试插入新行。如果该行已存在，则会使用语句中指定的新值更新现有行。

示例

此处，我们使用以下查询更新重复记录 -

INSERT INTO COURSES VALUES (6, 'Angular', 9000)
ON DUPLICATE KEY UPDATE
ID = 6, COURSE = 'Angular', COST = 20000;

输出

正如我们在下面的输出中看到的，没有生成任何错误，并且重复的行得到了更新。

Query OK, 2 rows affected (0.01 sec)

验证

我们使用以下 SELECT 查询来验证 COURSES 表 -

SELECT * FROM COURSES;

如下表所示，INSERT INTO... ON DUPLICATE KEY UPDATE 语句更新了重复记录 -