MySQL INSERT ON DUPLICATE KEY UPDATE

摘要：在本教程中，您将学习如何使用 MySQL INSERT ON DUPLICATE KEY UPDATE语句在向表中插入行时发生UNIQUE索引中的重复或PRIMARY KEY错误时更新数据。

MySQL INSERT ON DUPLICATE KEY UPDATE 语句简介

INSERT ON DUPLICATE KEY UPDATE是 MySQL 对 SQL 标准INSERT语句的扩展。

当您向表中插入新行时，如果该行导致UNIQUE索引或PRIMARY KEY中出现重复，MySQL 将发出错误。

但是，如果您在INSERT语句中指定ON DUPLICATE KEY UPDATE选项，MySQL 将使用新值更新现有行。

INSERT ON DUPLICATE KEY UPDATE语句的语法如下：

INSERT INTO table (column_list)
VALUES (value_list)
ON DUPLICATE KEY UPDATE
   c1 = v1, 
   c2 = v2,
   ...;Code language: SQL (Structured Query Language) (sql)

INSERT语句的唯一补充是ON DUPLICATE KEY UPDATE子句，您可以在其中指定列值对分配的列表，以防重复。

基本上，该语句首先尝试将新行插入表中。如果发生重复错误，它将ON DUPLICATE KEY UPDATE子句中指定的值更新现有行。

MySQL 根据其执行的操作返回受影响的行数：

• 如果插入新行，则受影响的行数为 1。
• 如果更新现有行，则受影响的行数为 2。
• 如果使用其当前值更新现有行，则受影响的行数为 0。

要在DUPLICATE KEY UPDATE子句中使用INSERT子句中的值，请使用VALUES()函数，如下所示：

INSERT INTO table_name(c1)
VALUES(c1)
ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + 1;

如果UNIQUE索引或PRIMARY KEY中有重复项，上面的语句将c1的值设置为其由表达式VALUES(c1)指定的当前值加 1。

MySQL INSERT ON DUPLICATE KEY UPDATE 示例

让我们看一下使用INSERT ON DUPLICATE KEY UPDATE的示例来了解它的工作原理。

首先，创建一个名为devices的表来存储网络设备：

CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);Code language: SQL (Structured Query Language) (sql)

接下来，将行插入devices表。

INSERT INTO devices(name)
VALUES('Router F1'),('Switch 1'),('Switch 2');Code language: SQL (Structured Query Language) (sql)

然后，从devices表中查询数据以验证插入：

SELECT 
    id, 
    name
FROM	
    devices;Code language: SQL (Structured Query Language) (sql)

现在， devices表中有三行。

之后，在devices表中再插入一行。

INSERT INTO 
   devices(name) 
VALUES 
   ('Printer') 
ON DUPLICATE KEY UPDATE name = 'Printer';Code language: SQL (Structured Query Language) (sql)

由于没有重复项，MySQL 会在devices表中插入一个新行。上面的语句与下面的语句具有相同的效果：

INSERT INTO devices(name) 
VALUES ('Printer');Code language: SQL (Structured Query Language) (sql)

最后，在id列中插入具有重复值的行。

INSERT INTO devices(id,name) 
VALUES 
   (4,'Printer') 
ON DUPLICATE KEY UPDATE name = 'Central Printer';Code language: SQL (Structured Query Language) (sql)

MySQL 发出以下消息：

2 row(s) affectedCode language: SQL (Structured Query Language) (sql)

由于devices表中已存在 id 为 4 的行，因此该语句将名称从Printer更新为Central Printer 。

在本教程中，您学习了如何使用INSERT语句的ON DUPLICATE KEY UPDATE选项在表中插入或更新数据。