我以为她要把她的鸡腿给我吃，倍感欣慰地说道：我不吃，宝贝
女儿一把抓起我的鸡腿放进了她爷爷的碗里，说道：不吃给爷爷吃
我没想到她会来这一出，我从我爸碗里夹回我的鸡腿，对女儿说道：不是，你这样问问你爷爷
女儿向她爷爷问道：爷爷，你吃鸡腿吗
我爸一脸溺爱的说道：吃
女儿又一把抓起我的鸡腿放进了她爷爷的碗里，说道：爷爷吃，给爷爷
我一脸不可思议的看着我女儿，竟然套路我，那我就陪你玩到底，我又从我爸碗里夹回鸡腿，对女儿说道：不对不对，让你问懵了，你再问我一次
女儿问道：爸爸，你吃公鸡腿吗
我信誓旦旦地说道：我吃
女儿又一把抓起我的鸡腿放进了她爷爷的碗里，说道：这是母鸡腿，你去找公鸡腿吃
我彻底懵了
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超大问题查询
