在上一篇文章中我们谈到了数据库批量插入，如果你已经亲自实践过了的话你会发现有个问题，就是我们在第一次执行完测试之后，再次执行的话会报错，除非你删掉数据库中的数据再执行。
那是因为 activity_stats 表中 activity_id 是表的主键，第二次执行的话就会因为主键重复而报错了，当然你也可以选择去掉这个主键的限制来进行测试。
但真正在项目中可就不能这么做了，项目中我们可能就是想要 activity_id 字段作为主键，不允许插入重复数据。
虽然说一般情况下不会有重复的，但毕竟是一般情况下，对于特殊情况我们也是需要考虑的。比如说 excel 中就是因为运营人员粗心弄了多条相同的数据，或者再次将同样的 excel 文件拿来上传，这时候就会有问题了。
所以对于这种情况我们需要做到兼容，尽量给用户好的体验，不然说因为几条重复的数据就让用户去修改再次上传。对于这种普通的数据，可能就需要系统自动去重了，当然如果是涉及到金钱方面的数据那还是需要人工再次确认过的。
下面我们就讨论下 key 重复的几种不同处理方式:
1.忽略重复 key 的数据，由于主键或者唯一键重复的记录，数据库自动忽略，在 insert 后面加上 ignore 关键字。
1 | insert ignore into activity_stats (activity_id, times_viewed, works_count, user_count) |
上面的 SQL 执行时，如果主键重复或者唯一键重复，会忽略掉新插入的数据，受影响行数为0，表数据不变，这是比较简单暴力的方式，但对于大部分情况下都是适用的，具体看业务情况而定。
2.使用最新数据，
对于 key 重复的数据，我们希望插入最新的数据到数据库中。要做到这个有两种方式: 一种是对重复数据进行更新，另一种是先删掉旧数据，然后再插入新数据。
先看第一种: ON DUPLICATE KEY UPDATE
1 | insert into activity_stats (activity_id, times_viewed, works_count, user_count) |
这种方式就是对于有重复的数据采取更新操作，要更新的字段跟在 UPDATE 关键字后面。
这里要注意一个受影响的行数的问题:
- 没有重复 key 数据，直接插入新数据，受影响的行数为 1。
- 有重复 key 数据并且要更新的新数据和已经存在的旧数据不一样，受影响的行数为 2(???)，这里可能有点不好理解为什么是 2，这里我们先暂时记下是 2，待会我会详细说明。
- 有重复 key 数据并且要更新的新数据和已经存在的旧数据一致，受影响的行数为 1(???)，这个可能也不是很好理解，同样暂时先记下，马上为你娓娓道来。
好了，现在看上面有两个地方打上了问号，这两个地方都是和我们正常所想的有点不一样。先看第一个打了问号的地方，也就是上面的第二点，按我们所想，key 重复，然后进行数据更新，这里只是进行了更新操作，受影响的行数应该是 1 才对，刚开始我对这个也是不太理解，后来通过查资料发现在 MySQL 的文档中关于 ON DUPLICATE KEY UPDATE 的使用中有下面这样一句话:
文档链接:
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
1 | With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values. |
上面这句话前半句就能为我解答为什么我们在第二点中提到的疑惑，前半句的大概意思就是:
对于 ON DUPLICATE KEY UPDATE 的使用，如果没有 key 重复的新数据插入，每行受影响的行数是 1，如果有 key 重复的新数据更新，返回受影响的行数是 2，如果有 key 重复的数据并且存在的旧数据和要更新的数据一致，受影响的行数是 0。
看到这里我们应该就能明白为什么受影响的行数是 2 了，我们可以理解为是 MySQL 为了在 ON DUPLICATE KEY UPDATE 的使用中区分插入和更新，所以对于插入返回 1，更新返回 2。
但是不是说 “如果是有 key 重复的数据并且存在的旧数据和要更新的数据一致，受影响的行数是 0” 吗？可是我们在上面第三点中 MySQL 返回的受影响的行数是 1，而这也正是我们第三点所疑惑的地方，我们再去看上面那句话的后半句，大概意思是:
在连接 mysqld 的时候如果你为 mysql_real_connect() API 函数指定了 CLIENT_FOUND_ROWS 标志，那么对于已经存在的旧数据和要更新的数据一致时，受影响的数值为 1 而不是 0。
对于第二个疑问，我们也大概明白为什么受影响的行数是 1 了，是因为我们使用的 MySQL 指定了 CLIENT_FOUND_ROWS 标志，而 mysql_real_connect 函数指定 CLIENT_FOUND_ROWS 标志的意思就是:
1 | CLIENT_FOUND_ROWS: Return the number of found (matched) rows, not the number of changed rows. |
这段文字在上面文档链接中可以点击 mysql_real_connect 跳转到对 mysql_real_connect 函数的描述页面，里面有 CLIENT_FOUND_ROWS 相应介绍。感兴趣的可以去研究研究。
再回到使用最新数据方式上，上面已经描述了对 key 重复的数据进行更新的方式，接下来我们看第二种:
对于 key 重复，先删掉旧数据，然后再插入新数据: REPLACE INTO
1 | REPLACE INTO activity_stats (activity_id, times_viewed, works_count, user_count) |
这种方式就是如果没有重复 key，直接插入，效果和 insert into 一样，但如果有重复 key，它会先删除旧数据，再插入新数据。同样这种方式受影响的行数也有以下几种情况:
- 没有重复 key 数据，直接插入新数据，受影响的行数为 1。
- 有重复 key 数据并且要更新的新数据和已经存在的旧数据不一样，受影响的行数为 2。这个很好理解，先删除旧数据，然后再插入新数据，所以受影响的行数为 2。
- 有重复 key 数据并且要更新的新数据和已经存在的旧数据一致，受影响的行数为 1。这里应该是数据没有发生变化，至于返回 1，我的理解是和上面一样的，返回匹配到的行数，并不是发生改变的行数(暂时没找到有关文档描述)。
关于重复 key 数据的处理方式主要有上面提到的三种，忽略重复数据的 INSERT IGNORE，使用更新的方式插入最新数据的 ON DUPLICATE KEY UPDATE，还有先删除再插入最新数据的 REPLACE INTO。
在我们使用这三种方式来避免插入重复数据的过程中，我们还需要注意下面几个问题:
首先，使用这三种方式的前提都是表中需要存在主键冲突或唯一键冲突，不然这三种都跟直接 INSERT INTO 没什么区别。
第二，要注意存在多个键冲突(插入的数据既可能存在主键冲突，也可能存在其他唯一键冲突或者存在多个唯一键冲突)的情况，在这种情况下使用可能会出现其他结果(可以自己测试看看)，所以这种情况下不太建议使用。
第三，这点比较重要，上面我的 activity_stats 表是有一个 activity_id 字段作为主键，这是存在主键冲突的情况。但如果我把 activity_id 字段改成唯一键并且新增一个字段 id 作为主键自增长，插入语句不变，这里因为 id 是自增长的，不存在冲突，所以这里是满足单一键冲突的情况，但是这里有个坑要注意，这种情况下使用上面三种方式都会产生主键 id 自增长不连续的问题。
目前的 SQL 语句是这样的:
1 | insert ignore into activity_stat (id, activity_id, times_viewed, works_count, user_count) |
对于 INSERT IGNORE 和 ON DUPLICATE KEY UPDATE，在存在 unique key 重复时，前者虽然重复的记录被忽略了没有执行插入操作，但是 id 还是会 +1。而后者是在 key 重复时执行更新操作，但 id 同样也会 +1，也就是说在存在 unique key 重复的情况下这两种方式不管有没有插入数据，自增长 id 都会增加。
这样的话在主从库的场景下就会出现主从的 auto_increment 不一致，因为 slave 并不会同步 master 的 auto_increment，一旦当前 master 挂了，任意一个 slave 被选举为 master，再次执行 insert 语句就可能导致主键冲突。
至于这里为什么会出现没有插入记录 auto_increment 却依然 +1 的情况，和 innodb_autoinc_lock_mode 的值有关。可以通过下面的 SQL 语句查看:
1 | select @@innodb_autoinc_lock_mode; |
它的值可能是 0，1，2 其中一种，默认是 1，具体各个值的特征可以参看官方文档中的描述，下面是文档地址:
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes
而对于 REPLACE INTO，那是因为在存在 unique key 重复时，会先删除再插入，执行了插入操作，id 肯定会增加。问题就在于重新插入了一条数据 id 发生了变化。如果别的表关联了这个表的主键 id 的话就会出现数据查找不到的情况。
总之就是对于这三种避免插入重复数据的方式，如果是由于主键冲突的情况，基本上没什么问题，但如果是唯一键冲突的情况，都会导致 master 和 slave 的 auto_increment 不一致的问题，一旦主从的 auto_increment 不一致(这里是 master 的 auto_increment 要大于 slave)，只要 master 出现问题，任意一个 slave 升级成 master，由于当前新的 master 的 auto_increment 是小于数据中的 id 的值，所以再进行插入时就可能发生主键冲突。
