MySQL INSERT ON DUPLICATE KEY UPDATE语句-IGI

时间：2019-11-20 08:52:31 　来源:igfitidea点击:

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

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,
   ...;

INSERT语句的唯一补充是ON DUPLICATE KEY UPDATE子句，在其中您指定了列值对分配的列表(如果重复)。

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

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

要使用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的当前值。

让我们看一个使用INSERT ON DUPLICATE KEY UPDATE的示例，以了解其工作原理。

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

CREATE TABLE devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

接下来，将行插入设备表。

INSERT INTO devices(name)
VALUES('Router F1'),('Switch 1'),('Switch 2');

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

SELECT 
    id, 
    name
FROM	
    devices;

现在，设备表中有三行。

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

INSERT INTO 
   devices(name) 
VALUES 
   ('Printer') 
ON DUPLICATE KEY UPDATE name = 'Printer';

由于没有重复项，因此MySQL会在devices表中插入新行。
上面的语句与以下语句具有相同的作用：

INSERT INTO devices(name) 
VALUES ('Printer');

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

INSERT INTO devices(id,name) 
VALUES 
   (4,'Printer') 
ON DUPLICATE KEY UPDATE name = 'Central Printer';

MySQL发出以下消息：

2 row(s) affected

因为设备表中已经存在ID为4的行，所以该语句将名称从"打印机"更新为"中央打印机"。

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