记录不存在则插入，存在则更新 → MySQL 的实现方式有哪些？[亲测有效]

开心一刻

　　今天我爸、我、我女儿一起吃饭，我们每人一个鸡腿

　　女儿问道：爸爸，你吃鸡腿吗

　　我以为她要把她的鸡腿给我吃，倍感欣慰地说道：我不吃，宝贝

　　女儿一把抓起我的鸡腿放进了她爷爷的碗里，说道：不吃给爷爷吃

　　我没想到她会来这一出，我从我爸碗里夹回我的鸡腿，对女儿说道：不是，你这样问问你爷爷

　　女儿向她爷爷问道：爷爷，你吃鸡腿吗

　　我爸一脸溺爱的说道：吃

　　女儿又一把抓起我的鸡腿放进了她爷爷的碗里，说道：爷爷吃，给爷爷

　　我一脸不可思议的看着我女儿，竟然套路我，那我就陪你玩到底，我又从我爸碗里夹回鸡腿，对女儿说道：不对不对，让你问懵了，你再问我一次

　　女儿问道：爸爸，你吃公鸡腿吗

　　我信誓旦旦地说道：我吃

　　女儿又一把抓起我的鸡腿放进了她爷爷的碗里，说道：这是母鸡腿，你去找公鸡腿吃

　　我彻底懵了

需求背景

　　环境

　　MySQL 版本： 5.7.20–log 

　　开发规范

　　公司后端开发规范有这么一点：

　　　　更新数据库表中数据的时候，不允许先删，然后批量插入

　　　　需要将入参与表中数据比判断，找出哪些是新插入，哪些需要更新，哪些是删除的，然后再做对应的数据操作

　　需求

　　我们有表如下：

　　当商品配送完后之后，需要记录它的最新配送价，若商品最新配送价已经存在则进行更新，不存在则执行插入

　　针对这个需求，我们有哪些实现方式？

代码处理

　　按开发规范中说的处理

　　通过代码在内存中进行数据处理，找出插入列表与更新列表，然后执行数据库操作

　　因为是很常规的插入与更新操作，所以这种处理方式适用于所有的关系型数据库

REPLACE INTO

　　当数据库是 MySQL ，碰到 不存在则插入，存在则更新 的需求时，第一时间往往想到的是 REPLACE INTO 

　　工作原理

　　 replace into 跟 insert 功能类似

　　不同点在于： replace into 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据，否则直接插入新数据

　　 replace 语句会返回一个数，表示受影响的行的数目，该数是被删除和被插入的行数的和

　　我们来看个示例

　　对于示例结果，相信大家都能理解

　　需要强调的是：根据唯一索引 uk_comany_ware 判定 (1001,10001,19.8,1,1) 已经存在，那么先删除此记录，然后插入 (1001,10001,20.5,1,1) 

　　而 (1001,10002,5.45,1,1) 判定为不存在，那么直接插入

　　这就导致我们看到的输出结果是： 受影响的行：3 ，同时自增主键由 1 变成了 2 3 ，而不是 1 2 

　　有坑

　　正是因为 replace into 的工作原理，不可避免就产生了一些需要注意的地方

　　1、破坏外键约束

　　　　如果主键被指定成了其他表的外键，那么 replace into 更新（非插入）时影响到了其他表的外键约束，那么会执行失败，提示类似信息：

　　　　可能很多小伙伴会说：我们开发过程中，会遵循阿里开发手册中的规约，其中有一条规约如下：

　　　　我们不用外键了，也就不会出现前面的 [Err] 1451 错误了

　　　　其实阿里开发手册中的这条规约，不是说不让我们用外键，而是说不用数据库层面的外键约束，在应用代码层面解决外键逻辑

　　　　用数据库层面的外键，问题提示的很明显，也不会产生脏数据

　　　　而应用层解决外键，反而使外键约束的数据一致性问题更隐晦，产生脏数据，如下

　　　　从此我们踏上了修数据的不归路

　　2、主键加速自增

　　　　很多情况下，我们的主键是 int 或者 bigint 类型，并且设置成了自增

　　　　不管是 int 还是 bigint ，都有一个最大值，如果一直自增下去，总有一天会达到最大值（可能到地老天荒也达不到这个值） 

　　　　replace into 的更新是先删除再插入，会导致主键自增 1（照理来说，更新是不应该导致主键自增 1）

　　　　如果更新频率远远大于插入频率，本不用考虑的自增主键用完的问题，可能就需要考虑了

　　　　另外也会导致主键不连续，主键值跳跃式的出现在表中

　　3、主从切换问题

　　　　master：master–local ，slave：slave–192.168.0.112 ，同步库：my_project 

　　　　从上图可以看出，主从复制是正常的

　　　　接下来我们看看 replace into 对主从复制有什么影响

　　　　此时 master 与 slave 上的 t_ware_last_delivery_price 的下一个非手工指定的主键都是 11（ AUTO_INCREMENT=11 ），两者是一致的

　　　　我们在 master 上使用 replace into 更新一条记录

　　　　 master 与 slave 的数据是一致的，但是 master 上的下一个自增主键是 AUTO_INCREMENT=12 ，而 slave 上却是 AUTO_INCREMENT=11 

　　　　可能会有人觉得：数据一致就行，下一个自增主键不一致有什么关系？

　　　　我们来想一下这个问题：如果 master 库崩了，我们会怎么做？会将 slave 提升为 master 

　　　　此时问题就来了： slave 提升成 master 之前，实际数据的 id 已经到了 11 ，但其 AUTO_INCREMENT=11 ，也就说下一个自增主键是 11 

　　　　　　那么下一条不指定 id 值的新纪录是插入时就会发生 duplicate key error ，每次冲突之后 AUTO_INCREMENT += 1，直到增长为 max(id) + 1 之后才能恢复正常

INSERT UPDATE

　　针对 不存在则插入，存在则更新 ， MySQL 还提供了另外一种方言实现： INSERT … ON DUPLICATE KEY UPDATE Statement 

　　工作原理

　　如果指定 ON DUPLICATE KEY UPDATE 子句，并且要插入的行将导致唯一索引或主键中出现重复值，则会更新旧行，否则则是插入

　　例如，如果 列 a 被声明为唯一且包含值 1，则以下两条语句具有类似的效果

　　但是这两条 SQL 的效果并不完全相同，我们以 t_ware_last_delivery_price 为例，来看看它们的区别

　　我们先来看看 UPDATE 

　　只是对 id = 11 的 last_delivery_price 就行了修改，受影响的行只有 1，不会影响 AUTO_INCREMENT 的值

　　我们再来看看 INSERT INTO … ON DUPLICATE KEY UPDATE 

　　对 id = 11 的 last_delivery_price 进行了修改，受影响的行是 2，并且 AUTO_INCREMENT=13 

　　此刻，我相信我们有共同的两个疑问

　　　　1、为什么受影响的行数是 2，而不是 1

　　　　2、自增主键为什么自增了 1（ AUTO_INCREMENT 为什么等于 13，而不是原有的 12）

　　为什么受影响的行数是 2，而不是 1，官方文档有这么一段说明

　　意思就是：1 表示新插入一行，2 表示更新了一行，0 表示更新前后值未变

　　我们换个角度来理解，假设让我们来设计，一条 SQL 既能插入，也能更新，我们如何告知用户到底是插入成功了，还是更新成功了？

　　所以 1，2 仅仅只是用来区分插入和更新，2 并非真正受影响的行数

　　主键明明没有变化，为什么 AUTO_INCREMENT=13 自增了 1 ？

　　这和 MySQL 的主键自增的参数有关 innodb_autoinc_lock_mode ，它有 3 个值 0,1,2 

　　 mysql5.1 之后其默认值是 1

　　因为 innodb_autoinc_lock_mode = 1 

　　所以上述 SQL 被当作简单插入处理，在真正修改数据之前就对 AUTO_INCREMENT 自增 1 处理了

　　批量操作

　　不仅支持单条操作，也支持批量操作

　　和批量插入类似

　　有坑

　　因为 innodb_autoinc_lock_mode = 1 是一个折中的选择，一般不会去改它，所以有些需要注意的点

　　1、主键加速自增

　　与 replace into 类似，即使是更新，也会导致 AUTO_INCREMENT 自增，加速了主键的衰老

　　同时也会导致主键的跳跃

　　2、主从切换问题

　　与 replace into 类似， master 上的更新导致 AUTO_INCREMENT 自增，而 AUTO_INCREMENT 又未同步到 slave 

　　当 slave 升级成 master 后，可能会出现 duplicate key error 

　　与 replace into 不同的是，上述两个问题可以通过设置 innodb_autoinc_lock_mode = 0 来避免，因为很多场景下对性能要求并不高

总结

　　1、如何选择哪种方式

　　　　上述三种方式各有优略，代码处理不依赖于具体的数据库，可移植性高，也不会引入特定数据库的在这方面的缺陷

　　　　 replace into 的方式不推荐（坑有点多），它完全可以由 INSERT UPDATE 替代

　　　　 INSERT UPDATE 可以减少我们的代码，但它是 MySQL 的拓展实现，只有 MySQL 支持，可移植性差

　　2、针对 INSERT UPDATE 的 “坑”，我们可以结合具体的业务来设置 innodb_autoinc_lock_mode ，适当的避免它的 “坑”

　　3、道路千万条，合适第一条

　　　　针对某个需求，实现方式往往有很多，我们要做的就是从中找到最适合的那一条

参考

　　REPLACE Statement

　　INSERT … ON DUPLICATE KEY UPDATE Statement

　　mysql自增id超大问题查询