mysql 避免数据重复插入的三种方式

在进行 mysql 数据插入时，有时要先查询当前数据是否存在相同的主键，
或是唯一键是否冲突，根据情况，数据插入策略也不同，一般有以下三种避免方法。

1. insert ignore（先查询，不存在则插入，存在则忽略）
2. replace into（先查询，不存在则直接插入，存在则直接删除然后插入）
3. insert on duplicate key update（先查询，不存在则插入，存在则更新）

注意，除非表有一个PRIMARY KEY或UNIQUE索引，否则，使用以上三个语句没有意义，与使用单纯的INSERT INTO相同。

新建数据表，插入测试数据

新建表

CREATE TABLE `websites` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) NOT NULL COMMENT '名称',
  `url` varchar(255) NOT NULL DEFAULT '' COMMENT '链接',
  `alexa` int(10) DEFAULT '0' COMMENT '点击数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入测试数据

INSERT INTO `websites` VALUES (1, '淘宝', 'https://www.taobao.com/', 80);
INSERT INTO `websites` VALUES (2, '冯奎博客', 'https://fengkui.net/', 20);

一、insert ignore

insert ignore 会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，
如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。

测试插入唯一键冲突的数据：

insert ignore into websites (name,url,alexa) values ('冯奎博客', 'https://fengkui.net/', 60),('腾讯', 'https://www.tencent.com/', 50);

执行结果：

Query OK, 1 row affected (0.05 sec)
Records: 2  Duplicates: 1  Warnings: 1

查看数据表，可以看到只插入了('腾讯', 'https://www.tencent.com/', 50)这条，
同时有一条 warning 提示有重复的值（'冯奎博客' 唯一键冲突，插入失败）。

重新查询表结构，发现虽然只增加了一条记录，但是 AUTO_INCREMENT 还是增加了2个（3变成5）

二、replace into

replace into 首先尝试插入数据到表中。如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。
（使用 replace into，你必须具有 delete 和 insert 权限）

测试插入唯一键冲突的数据：

replace into websites (name,url,alexa) values ('冯奎博客', 'https://fengkui.net/', 60),('Github', 'https://github.com/', 55);

执行结果：

Query OK, 3 rows affected (0.05 sec)
Records: 2  Duplicates: 1  Warnings: 0

可以看到原有 id=2，name=冯奎博客 的记录不见了，
新增了一条 id=5，name=冯奎博客 的记录 和 另一条新插入的数据。
replace into 语句执行完会返回一个数，来指示受影响的行的数目。
该数是被删除和被插入的行数的和，上面的例子中 3 rows affected。

重新查询表结构，增加了两条记录， AUTO_INCREMENT 增加了2个（5变成7）

三、insert on duplicate key update

如果在 insert into 语句末尾指定了 on duplicate key update，
并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，
则在出现重复值的行执行 update，update 后面跟着需要更新的数据；
如果不会导致重复的问题，则插入新行，跟普通的 insert into 一样。
（使用insert into，你必须具有insert和update权限）

如果有新记录被插入，则受影响行的值显示1；
如果原有的记录被更新，则受影响行的值显示2；
如果记录被更新前后值是一样的，则受影响行数的值显示0

测试插入唯一键冲突的数据：

insert into websites (name, url, alexa) values ('冯奎博客', 'https://fengkui.net/', 60),('新浪','https://weibo.com/', 75) on duplicate key update alexa=alexa+100;

执行结果：

Query OK, 3 rows affected (0.07 sec)
Records: 2  Duplicates: 1  Warnings: 0

可以看到，id=5 冯奎博客的记录发生了改变，alexa=原有的alexa+100，
其他列没有改变，并且插入新的一行 name=新浪 的数据。

重新查询表结构，发现虽然只增加了一条记录，但是 AUTO_INCREMENT 还是增加了2个（7变成9）

结论：

这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
insert ignore能忽略重复数据，只插入不重复的数据。
replace into和insert … on duplicate key update，都是替换原有的重复数据，区别在于
replace into是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变；
insert … on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句。

原文地址：mysql 避免重复写入数据的三种方式 和insert ...on duplicate updt... 死锁

分割线分割线分割线分割线分割线分割线分割线分割线

我们在实际业务场景中，经常会有一个这样的需求，插入某条记录，如果已经存在了则更新它如果更新日期或者某些列上的累加操作等，我们肯定会想到使用INSERT … ON DUPLICATE KEY UPDATE语句，一条语句就搞定了查询是否存在和插入或者更新这几个步骤，但是使用这条语句在msyql的innodb5.0以上版本有很多的陷阱，即有可能导致death lock死锁也有可能导致主从模式下的replication产生数据不一致。

正文

正如前言说的那样，在实际业务中，曾经有过一个需求就是插入一条业务数据，如果不存在则新增，存在则累加更新某一个字段的值，于是乎就想到了使用insert… on duplicate key update这个语句，但是有一天去测试环境查看错误日志时，却发现了在多个事务并发执行同一条insert…on duplicate key update 语句时，也就是insert的内容相同时，发生 了死锁。

对于insert…on duplicate key update这个语句会引发dealth lock问题，官方文档也没有相关描述，只是进行如下描述：

An INSERT … ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)

也就是如果一个表定义有多个唯一键或者主键时，是不安全的，这又引发了以一个问题，见https://bugs.mysql.com/bug.php?id=58637

也就是

当mysql执行INSERT ON DUPLICATE KEY的 INSERT时，存储引擎会检查插入的行是否会产生重复键错误。如果是的话，它会将现有的

行返回给mysql，mysql会更新它并将其发送回存储引擎。当表具有多个唯一或主键时，此语句对存储引擎检查密钥的顺序非常敏感。根据这个顺序，

存储引擎可以确定不同的行数据给到mysql，因此mysql可以更新不同的行。存储引擎检查key的顺序不是确定性的。例如，InnoDB按照索引添加到

表的顺序检查键。首先检查第一个添加的索引。

所以，如果主站和从站按不同的顺序添加索引，那么如果主从复制是基于语句的复制，那么可能最后同一个语句在master上执行和slaver上执行的

结果不一致.

回到死锁的问题

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X(排他锁)，最后进行update写入。

如果有两个事务并发的执行同样的语句，那么就会产生death lock，如：