Mysql 简明教程

MySQL − Insert on Duplicate Key Update

INSERT INTO 语句在 MySQL 中用于向特定表中插入新记录。

The INSERT INTO statement in MySQL is used to insert new records into a specific table.

MySQL Insert on Duplicate Key Update Statement

当我们尝试使用 UNIQUE INDEX 或 PRIMARY KEY 向 MySQL 表列中插入新行时,如果要插入的值在该列中已经存在,MySQL 会发出错误。这是因为这些约束需要唯一值,不允许重复值。

When we are trying to insert a new row into a MySQL table column with a UNIQUE INDEX or PRIMARY KEY, MySQL will issue an error, if the value being inserted already exists in the column. This will happen because these constraints require unique values, and duplicate values are not allowed.

但是,如果我们将 MySQL ON DUPLICATE KEY UPDATE 子句与 INSERT INTO 语句一起使用,MySQL 会使用新值更新现有行,而不是显示错误。

However, if we use the MySQL ON DUPLICATE KEY UPDATE clause with with the INSERT INTO statement, MySQL will update the existing rows with the new values instead of showing an error.

Syntax

以下是 MySQL 中 ON DUPLICATE KEY UPDATE 子句的基本语法:

Following is the basic syntax of ON DUPLICATE KEY UPDATE clause in MySQL −

INSERT INTO my_table (col1, col2, ...)
VALUES (val1, val2), (val3, val4), ...
ON DUPLICATE KEY UPDATE <col1>=<val1>, <col2>=<val2>,...;

Example

首先,让我们使用以下查询创建一个名为 CUSTOMERS 的表:

First of all, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID int NOT NULL,
   NAME varchar(20) NOT NULL,
   AGE int NOT NULL,
   ADDRESS char (25),
   SALARY decimal (18, 2),
   PRIMARY KEY (ID)
);

这里,我们将使用 INSERT INTO 语句向上述创建的表中插入部分记录,如下所示:

Here, we are inserting some records into the above-created table using the INSERT INTO statement as shown below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 );

执行以下查询以显示上述创建的 CUSTOMERS 表中存在的记录:

Execute the following query to display the records present in the above created CUSTOMERS table −

SELECT * FROM CUSTOMERS;

以下是 CUSTOMERS 表中的记录:

Following are the records in CUSTOMERS table −

在这里,我们使用 INSERT INTO 语句向 CUSTOMERS 表中插入另一行,其中 ID 值为 3

Here, we are inserting another row into the CUSTOMERS table with an ID value 3 using the INSERT INTO statement −

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (3, 'Chaitali', 25, 'Mumbai', 6500.00);

因此,MySQL 会发出错误,因为我们正在插入一个重复的 ID 值:

As a result, MySQL will issue an error because we are inserting a duplicate ID value −

ERROR 1062 (23000): Duplicate entry '3' for key 'customers.PRIMARY'

我们可以避免上述错误,并使用 ON DUPLICATE KEY UPDATE 子句和 INSERT INTO 语句来更新现有行,如下所示:

We can avoid the above error and update the existing row with the new information using the ON DUPLICATE KEY UPDATE clause along with INSERT INTO statement as shown below −

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (3, 'Chaitali', 25, 'Mumbai', 6500.00)
ON DUPLICATE KEY UPDATE NAME = "Chaitali",
AGE = 25,
ADDRESS = "Mumbai",
SALARY = 6500.00;

Output

正如我们在输出中看到的那样,上述查询更新了 CUSTOMERS 表中现有的行。因此,它返回两个受影响行。

As we can see in the output, the above query updated the existing row in the CUSTOMERS table. As a result, it returns two affected-rows.

Query OK, 2 rows affected (0.01 sec)

Verification

执行以下查询以验证现有行是否已使用新信息更新:

Execute the following query to verify whether the existing row got updated with new information or not −

SELECT * FROM CUSTOMERS;

当我们观察表中的第三行时,记录已更新。

As we observe the third row in the table, the records got updated.

Example

在以下查询中,我们正尝试向 CUSTOMERS 表中插入新行,使用 INSERT INTO 语句以及 ON DUPLICATE KEY UPDATE 子句:

In the following query, we are trying to insert a new row into the CUSTOMERS table using the INSERT INTO statement along with the ON DUPLICATE KEY UPDATE clause −

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (4, 'Hardik', 27, 'Bhopal', 8500.00)
ON DUPLICATE KEY UPDATE NAME = "Hardik",
AGE = 27,
ADDRESS = "Bhopal",
SALARY = 8500.00;

Output

正如我们在输出中看到的那样,在插入新行时未发生冲突。因此,它返回一个受影响行。

As we can see in the output, there is no conflict occurred while inserting the new row. As a result, it returns one affected-row.

Query OK, 1 row affected (0.01 sec)

Verification

我们可以使用以下查询验证新行是否已插入 CUSTOMERS 表:

We can verify whether the new row is inserted in the CUSTOMERS table or not using the following query −

SELECT * FROM CUSTOMERS;

当我们观察输出时,新行已插入。

As we observe the output below, the new row has been inserted.

INSERT or UPDATE multiple records at once

在 MySQL 中同时插入或更新多条记录时,要为每列设置的值可能因存在冲突的记录而异。

While inserting or updating multiple records at the same time in MySQL, the value to set for each column may vary depending on which record or records have a conflict.

例如,如果我们尝试插入四行新行,但第三行具有与现有记录相冲突的 ID 列,则很可能希望根据第三行中预期的信息更新现有行。

For example, if we are trying to insert four new rows, but the third has an ID column that conflicts with an existing record, we most likely want to update the existing row based on the data you had in mind for the third row.

Example

在执行下一个操作之前,我们先查看更新的 CUSTOMERS 表中的记录:

Before we perform the next operation, let’s look into the records of updated CUSTOMERS table −

SELECT * FROM CUSTOMERS;

以下是更新后的 CUSTOMERS 表:

Following is the updated CUSTOMERS table −

以下查询向 CUSTOMERS 表中添加两行新行:

The following query adds two new rows into the CUSTOMERS table −

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (5, "Komal", 22, "Hyderabad", 4500.00),
(4, "Kaushik", 23, "Kota", 2000.00)
ON DUPLICATE KEY UPDATE
NAME = VALUES(NAME),
AGE = VALUES(AGE),
ADDRESS = VALUES(ADDRESS),
SALARY = VALUES(SALARY);

Output

正如我们在输出中看到的那样,有两个新行(ID 5 和 4)和一行更新的行(ID 4),它与现有行相冲突(已经有一行 ID 为“4”)。

As we can see in the output, there are two new rows (ID 5, and 4) and one updated row (ID 4) where it conflicated with an existing row (there is already a row with an ID of "4").

Query OK, 3 rows affected, 4 warnings (0.01 sec)
Records: 2  Duplicates: 1  Warnings: 4

Verification

执行以下查询来验证是否已将记录插入到 CUSTOMERS 表中。

Execute the following query to verify whether the records have been inserted into the CUSTOMERS table.

SELECT * FROM CUSTOMERS;

如果我们查看下面的 “CUSTOMERS” 表,我们可以看到按预期添加了两行新行,冲突行的值已使用新信息更新。

If we look at the "CUSTOMERS" table below, we can see that the two new rows added as expected and the values of the conflicted rows have been updated with the new information.

Client Program

除了使用 MySQL 查询在 MySQL 表中执行 Insert On Duplicate key Update 查询外,我们还可以使用客户端程序对表执行相同操作。

In addition to perform the Insert On Duplicate key Update Query in MySQL table using MySQL query, we can also perform the same operation on a table using a client program.

Syntax

以下是此操作在各种编程语言中的语法 −

Following are the syntaxes of this operation in various programming languages −

Example

以下是这些程序 −

Following are the programs −