MySQL - 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 表如下：

ID	课程	费用
6	Angular	8000
2	CSS	4000
1	HTML	3000
3	JavaScript	6000
4	Node.js	10000
7	Php	9000
5	React.js	12000

现在，我们尝试使用以下查询中的 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)

验证

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

SELECT * FROM COURSES;

获得的表如下：

ID	课程	费用
6	Angular	8000
2	CSS	4000
1	HTML	3000
3	JavaScript	6000
4	Node.js	10000
7	Php	9000
5	React.js	12000

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

使用 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 语句在删除重复行后添加了一行：

ID	课程	费用
6	Angular	9000
2	CSS	4000
1	HTML	3000
3	JavaScript	6000
4	Node.js	10000
7	Php	9000
5	React.js	12000

使用带 ON DUPLICATE KEY UPDATE 的 INSERT 进行 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 语句更新了重复记录：

ID	课程	费用
6	Angular	20000
2	CSS	4000
1	HTML	3000
3	JavaScript	6000
4	Node.js	10000
7	Php	9000
5	React.js	12000