MySQL INSERT ON DUPLICATE KEY UPDATE

目录

概要

介绍

举例

小结

Reference

---

概要

        在本教程中，你将学会使用"INSERT ON DUPLICATE KEY UPDATE"语句。向存在主键索引或唯一索引的表插入重复数据而发生错误时，可以使用"INSERT ON DUPLICATE KEY UPDATE"更新老数据

介绍

        “INSERT ON DUPLICATE KEY UPDATE”语句是MYSQL标准INSERT语法的一种扩展，当你插入一行数据到表中时，如果导致UNIQUE索引或PRIMARY KEY重复，MYSQL将发生错误。然而，如果你在INSERT语句中指定了重复KEY更新的项（INSERT ON DUPLICATE KEY UPDATE），MYSQL将使用新值更新已存在的行数据。

        语法如下所示：

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根据执行的操作返回受影响的行数:     

        （1）如果新的一行数据被插入，影响行数为 1

        （2）如果已存在的数据（row）被更新，影响行数为 2

        （3）如果使用当前值更新现有行，则受影响的行数为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指定的当前值。

举例Demo

（1）创建表并插入数据

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


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

（2）查询插入数据结果

SELECT 
    id, 
    name
FROM	
    devices;

（3）插入一条普通记录后查看结果

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

表中目前有四条记录，由于没有发生UK 或 Primary KEY的重复，所以数据正常插入，其实此时语句执行效果和普通INERT语句一样：

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

（4） 插入一条ID列重复的数据

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

2 row(s) affected

        由于id=4的数据已经存在，primary key冲突，所以Printer 被更新为 Central Printer，影响行数为2

 小结

        本教程中，你学会了如何使用 INSERT 语句的 ON DUPLICATE KEY UPDATE 选项更新和插入数据

Reference

[1]https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/