Mysql 简明教程

MySQL - Upsert

The MySQL UPSERT Operation

MySQL UPSERT 操作将 INSERT 和 UPDATE 合并到单个语句中，这样你就可以将新行插入到表中，或者在已存在时更新现有行。我们可以从名称（UPSERT）本身理解，其中 UP 代表 UPDATE，而 SERT 代表 INSERT。

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

UPSERT Using INSERT IGNORE

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

Example

首先，让我们使用以下查询创建名为 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'

Using INSERT IGNORE −

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

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

Output

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

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

Verification

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

SELECT * FROM COURSES;

获得的表如下 −

UPSERT Using REPLACE

如果存在现有的行，则 MySQL REPLACE 语句首先尝试删除该行，然后再插入具有相同主键的新行。如果该行不存在，它会简单地插入新行。

Example

让我们替换或更新 COURSES 表中的行。如果 COURSE 为“Angular”的行已存在，则它将使用提供的新的值更新它的 ID 和 COST 值。否则，将使用查询中指定的值插入新行 −

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

Output

以上查询产生的输出如下所示 −

Query OK, 2 rows affected (0.01 sec)

Verification

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

SELECT * FROM COURSES;

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

UPSERT Using INSERT with ON DUPLICATE KEY UPDATE

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

Example

这里，我们使用以下查询更新重复记录 −

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

Output

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

Query OK, 2 rows affected (0.01 sec)

Verification

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

SELECT * FROM COURSES;

正如我们在下面的表中看到的那样，INSERT INTO… ON DUPLICATE KEY UPDATE 语句更新了重复记录 -