Mysql 简明教程

MySQL - Upsert

The MySQL UPSERT Operation

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

The MySQL UPSERT operation combines INSERT and UPDATE into a single statement, allowing you to insert a new row into a table or update an existing row if it already exists. We can understand in the name (UPSERT) itself, where UP stands for UPDATE and SERT stands for INSERT.

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

This tutorial covers three common methods to perform UPSERT operations in MySQL: INSERT IGNORE, REPLACE, and INSERT with ON DUPLICATE KEY UPDATE.

UPSERT Using INSERT IGNORE

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

The INSERT IGNORE statement in MySQL allows you to insert a new record into a table. If a record with the same primary key already exists, it ignores the error and doesn’t insert the new record.

Example

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

First, let us create a table with the name COURSES using the following query −

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

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

Here, we are inserting records into the COURSES table −

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 表如下 −

The COURSES table obtained is as follows −

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

Now, we attempt to insert a duplicate record using the INSERT INTO statement in the following query −

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

这会导致错误，因为无法插入重复记录 −

This results in an error because a duplicate record cannot be inserted −

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

Using INSERT IGNORE −

Using INSERT IGNORE −

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

Now, let us perform the same operation using INSERT IGNORE statement −

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

Output

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

As we can see in the output below, the INSERT IGNORE statement ignores the error −

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

Verification

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

We can verify the COURSES table to see that the error was ignored using the following SELECT query −

SELECT * FROM COURSES;

获得的表如下 −

The table obtained is as follows −

UPSERT Using REPLACE

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

The MySQL REPLACE statement first attempts to delete the existing row if it exists and then inserts the new row with the same primary key. If the row does not exist, it simply inserts the new row.

Example

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

Let us replace or update a row in the COURSES table. If a row with COURSE "Angular" already exists, it will update its values for ID and COST with the new values provided. Else, a new row will be inserted with the specified values in the query −

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

Output

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

The output for the query above produced is as given below −

Query OK, 2 rows affected (0.01 sec)

Verification

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

Now, let us verify the COURSES table using the following SELECT query −

SELECT * FROM COURSES;

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

We can see in the following table, the REPLACE statement added a new row after deleting the duplicate row −

UPSERT Using INSERT with ON DUPLICATE KEY UPDATE

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

The INSERT …​ ON DUPLICATE KEY UPDATE statement in MySQL attempts to insert a new row. If the row already exists, it updates the existing row with the new values specified in the statement.

Example

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

Here, we are updating the duplicate record using the following query −

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

Output

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

As we can see in the output below, no error is generated and the duplicate row gets updated.

Query OK, 2 rows affected (0.01 sec)

Verification

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

Let us verify the COURSES table using the following SELECT query −

SELECT * FROM COURSES;

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

As we can see the table below, the INSERT INTO…​ ON DUPLICATE KEY UPDATE statement updated the duplicate record −