利用MySQL的IGNORE关键字避免重复插入的骚操作你见过么

我们在实际开发中总有这样的需求，表里的数据要保持唯一，所以我们开发中经常都是先查询这样的数据有没有，有的话跳过，没有的话再插入，然后在能表示数据记录唯一的列上加上唯一索引进行兜底。虽然有的同学基本上也是这么做的，但是又稍微的做了一点其他的骚操作😄😄😄。

# 😄😄😄 骚操作, 当有重复记录就会忽略错误返回影响行数0
INSERT IGNORE INTO tableName VALUES ()

有的同学是这么想的，在高并发的情况下，先查询再插入的话是有并发的问题的，我直接在重复插入的时候屏蔽错误，不用捕获或者抛出异常，直接用 insert 的返回影响行数来判断插入是否成功。这样的做法看上去感觉没啥问题，其实还是有很大的问题的。

语义上的不正确

你在表中定义了唯一索引，插入重复的记录直接屏蔽了错误，这就造成异常被吞掉了，导致功能有数据重复的bug就无法通过异常日志来定位。

破坏MySQL主从的数据一致性

在说这个IGNORE是如何造成 MySQL 主从数据不一致的原因之前，我们先说说 MySQL 中的一个作为开发很少听说的一个表级锁：AUTO-INC锁（即自增锁）。

在使用 MySQL 的过程中，我们可以为表的某一列添加 AUTO_INCREMENT属性，在插入记录时该列可以不指定，MySQL 会自动为该列赋予一个递增的值。MySQL 自动给AUTO_INCREMENT修饰的列进行递增赋值的实现方式主要有以下两个：

• 采用 AUTO_INC锁，在执行插入语句时就加一个表级别的AUTO_INC锁，然后为每条待插入的记录分配递增值。在语句执行结束后，再把AUTO_INC锁释放。所以在一个事务持有AUTO_INC锁的过程中，其他插入语句都要阻塞，从而保证一个语句分配的递增值是连续的。

• 采用一个轻量级的锁，在为INSERT语句生成递增值时获取这个轻量级的锁，然后在生成本次插入语句需要用到的递增值后就把轻量级锁释放掉，不需要等到整个插入语句执行完才释放锁。

如果我们的插入语句在执行前就可以确定具体要插入多少条记录，比如

INSERT INTO test(a) VALUES ('1'),('b');

此时我们可以在执行前就确定要插入2条记录，那么一般采用轻量级锁的方式对 AUTO_INCREMENT修饰的列进行赋值，用这种方式可以避免锁定表，可以提高插入性能。

Mysql 提供了一个名为 innodb_atuoinc_lock_mode 的系统变量，来控制 MySQL使用上述的两种方式中的哪一中来为 AUTO_INCREMENT修饰的列进行赋值。

• 当 innodb_atuoinc_lock_mode 的值为0时，一律采用AUTO_INC锁；
• 当 innodb_atuoinc_lock_mode 的值为1时，两种方式混合使用，插入记录确定时采用轻量级锁，不确定时使用AUTO_INC锁。
• 当 innodb_atuoinc_lock_mode 的值为2时，一律采用轻量级锁；

  当innodb_autoinc_lock_mode=0时，只有插入成功之后，AUTO_INCREMENT 值才会递增，插入失败不会递增。
  当innodb_autoinc_lock_mode=1时，无论插入是否成功，AUTO_INCREMENT的值都会递增。
  当innodb_atuoinc_lock_mode为2时，可能会造成不同事物中的插入语句为AUTO_INCREMENT修饰的列生的值是交叉的，这在主从复制的场景是不安全的。^[1]

我查看过我目前负责的系统，MySQL 采用的是主从模式，隔离级别设置的是读已提交(Read Committed)，binlog的格式设置为 ROW， innodb_atuoinc_lock_mode设置的是1，也就是说无论插入成功与否 AUTO_INCREMENT 的值都会递增。在本地搭建的主从模式的 mysql（安装本人博客中之前的文章搭建的 MySQL主从） 中的master库 新建一个如下的表：

查看一下 slave3307数据库中对应的表结构和自增值：确定和 master 一致

在 master库上执行 show master status 查看 master 的binglog 信息：

在 master 库上连续执行下图的三个 SQL，观察 master 与 salve 的情况。

master 的表的AUTO_INCREMENT值已经发生了变化，如下图：

master 的 binglog 如下图：

我们从上面两个图可以发现，使用ignore关键字，尽管待插入的记录因为唯一键冲突而没有插入成功，但AUTO_INCREMENT值却递增了，而且 binlog 中也没有 INSERT IGNORE 语句日志，这很明显导致数据库 slave 不会执行该语句，导致数据库 slave数据库中的表的主键和 master 数据库中的表主键不一致！

我们由此可以猜测一下 IGNORE 的实现机制：

1、尝试把新行插入到表中 ；

2、如果插入成功则返回正常的影响行数；如果唯一键冲突（错误）则忽略该错误，返回影响行数为0 ；

InnoDB表insert语句主要分为三种类型：

• Simple insert(简单插入)
  可以通过语句预先判断插入的行数。包括不包含子查询的单行、多行 INSERT 和 REPLACE 语句，还有语句INSERT ... ON DUPLICATE KEY UPDATE。

• Bulk inserts(大量插入)
  无法通过语句预先判断插入的行数。包括INSERT ... SELECT、REPLACE ... SELECT >和LOAD DATA语句。InnoDB每处理一行才会为 AUTO_INCREMENT 列分配一个值。

• Mixed-mode inserts(混合模式插入)
  在简单插入语句当中，有的行有为自增列指定值，而有的行没有为自增列指定值。

例如:混合模式插入

# id列作为自增列
 INSERT INTO t1 (id,c1,c2) VALUES (1,1,'a'), (NULL,NULL,'b'), (10,5,'c'), (NULL,NULL,'d') ;

在实际生产环境中业务上是需要当出现唯一键冲突时发生重复要抛出异常而不是吞掉异常，这个前面也有说到。此外，由上面的执行过程可知，我们期望插入的记录因为唯一键冲突而没有插入成功，但AUTO_INCREMENT字段值却递增了。因为插入语句并未执行成功，在binlog中并不会有执行记录，这意味着从库的AUTO_INCREMENT字段值不会递增，即主库和从库的AUTO_INCREMENT值出现了不一致。这带来的问题是，后续如果因为主库发生故障而发生主从切换，从库的AUTO_INCREMENT值落后于主库，就会导致一段时间内在原从库现主库插入的数据在原主库现从库上因为AUTO_INCREMENT主键冲突而导致插入失败，这个肯定是用INSERT IGNORE之前没有想到的，所以建议在实际开发中不要使用此种骚操作! 😄😄😄

• [1] 参考《MySQL是怎样运行的》