Mysql 简明教程

MySQL − Insert on Duplicate Key Update

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

MySQL Insert on Duplicate Key Update Statement

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

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

Syntax

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

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

Example

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

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 语句向上述创建的表中插入部分记录，如下所示：

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 表中存在的记录：

SELECT * FROM CUSTOMERS;

以下是 CUSTOMERS 表中的记录：

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

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

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

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

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

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 表中现有的行。因此，它返回两个受影响行。

Query OK, 2 rows affected (0.01 sec)

Verification

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

SELECT * FROM CUSTOMERS;

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

Example

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

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

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

Query OK, 1 row affected (0.01 sec)

Verification

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

SELECT * FROM CUSTOMERS;

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

INSERT or UPDATE multiple records at once

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

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

Example

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

SELECT * FROM CUSTOMERS;

以下是更新后的 CUSTOMERS 表：

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

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”）。

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

Verification

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

SELECT * FROM CUSTOMERS;

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

Client Program

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

Syntax

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

Example

以下是这些程序 −