开心一刻
今天，爸爸、我和女儿一起吃了晚饭，我们每人都吃了一只鸡腿。
[En]
Today, my father, me and my daughter had dinner together, and we each had a drumstick.
女儿问：爸爸，你吃鸡腿吗？
[En]
The daughter asked, Dad, do you eat drumsticks?
我以为她会给我她的鸡腿，我松了一口气说：“我不吃了，宝贝。”
[En]
I thought she was going to give me her drumsticks, and I was relieved to say, “I’m not eating, baby.”
女儿抓起我的鸡腿，把它放进爷爷的碗里，说：“别给爷爷吃。”
[En]
The daughter grabbed my drumstick and put it into her grandfather’s bowl and said, “Don’t give it to Grandpa.”
我没想到她会来这里。我从父亲的碗里抓起鸡腿，对女儿说：“不，这样问你爷爷吧。”
[En]
I didn’t expect her to come here. I grabbed my drumstick from my father’s bowl and said to my daughter, “No, ask your grandfather this way.”
女儿问爷爷，爷爷，你吃鸡腿吗？
[En]
The daughter asked her grandfather, Grandpa, do you eat drumsticks?
我爸一脸溺爱的说道：吃
女儿抓起我的鸡腿，把它放进爷爷的碗里，说：“爷爷，给爷爷。”
[En]
The daughter grabbed my drumstick and put it into her grandfather’s bowl and said, “Grandpa, give it to Grandpa.”
我用一张不可思议的脸看着我的女儿，居然陷害了我，然后我会和你玩到最后。我把鸡腿从父亲的碗里夹了回来，对女儿说：“不，我很困惑。你再问我一次。”
[En]
I looked at my daughter with an incredible face, unexpectedly set me up, then I will play with you to the end. I clipped the drumstick back from my father’s bowl and said to my daughter, “No, I’m confused. You ask me again.”
女儿问：爸爸，你吃公鸡腿吗？
[En]
The daughter asked, Dad, do you eat rooster legs?
我信誓旦旦地说道：我吃
女儿抓住我的鸡腿，把它放进爷爷的碗里，说：这是一条母鸡腿，去找一条公鸡腿吃吧。
[En]
The daughter grabbed my chicken leg and put it into her grandfather’s bowl and said, “this is a hen leg. Go and find a rooster leg to eat.”
我彻底懵了
需求背景
环境
MySQL 版本： 5.7.20 -log
开发规范
该公司的后端开发规范有以下几点：
[En]
The company’s back-end development specification has this point:
更新数据库表中的数据时，不允许先删除后批量插入。
[En]
When updating data in a database table, it is not allowed to delete it first and then insert it in batch.
您需要比较Participation表中的数据，找出哪些是新插入、哪些需要更新、哪些被删除，然后执行相应的数据操作。
[En]
You need to compare the data in the participation table to find out which are new inserts, which need to be updated, and which are deleted, and then do the corresponding data operations.
需求
我们有表如下：
发货后，需要记录最新的配送价。如果最新的分配价格已经存在，则进行更新，如果不存在，则执行插入。
[En]
After the goods are delivered, the latest distribution price needs to be recorded. If the latest distribution price already exists, it will be updated, and if it does not exist, insert will be performed.
我们有什么方法来实现这一要求？
[En]
What ways do we have to implement this requirement?
代码处理
按开发规范中说的处理
通过代码对内存中的数据进行处理，找出插入列表和更新列表，然后执行数据库操作
[En]
Through the code to process the data in memory, find out the insert list and update list, and then perform database operations
因为它是例行的插入和更新操作，所以该方法适用于所有关系数据库。
[En]
Because it is a routine insert and update operation, this method is suitable for all relational databases.
REPLACE INTO
当数据库是 MySQL ，碰到 不存在则插入，存在则更新 的需求时，第一时间往往想到的是 REPLACE INTO
工作原理
replace into 跟 insert 功能类似
不同点在于： replace into 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则 先删除此行数据，然后插入新的数据，否则直接插入新数据
replace 语句会返回一个数，表示受影响的行的数目，该数是被删除和被插入的行数的和
我们来看个示例
我相信每个人都能理解这个例子的结果。
[En]
I believe everyone can understand the result of the example.
需要强调的是：根据唯一索引 uk_comany_ware 判定 (1001,10001,19.8,1,1) 已经存在，那么先删除此记录，然后插入 (1001,10001,20.5,1,1)
而 (1001,10002,5.45,1,1) 判定为不存在，那么直接插入
因此，我们看到的输出是：受影响的行：3，而自我增加的主键已从1变为2，而不是1/2。
[En]
As a result, the output we see is: affected line: 3, while the self-increasing primary key has changed from 1 to 2, instead of 1 / 2.
有坑
正是因为 replace into 的工作原理，不可避免就产生了一些需要注意的地方
1、破坏外键约束
如果主键被指定成了其他表的外键，那么 replace into 更新（非插入）时影响到了其他表的外键约束，那么会执行失败，提示类似信息：
很多合作伙伴可能会说：在开发过程中，我们会遵循阿里开发手册中的规范，其中一条如下：
[En]
Many partners may say: in the process of development, we will follow the specification in Ali’s development manual, one of which is as follows:
我们不用外键了，也就不会出现前面的 [Err ] 1451 错误了
其实，阿里开发手册中的这个规范并不意味着我们不能使用外键，而是我们不需要数据库层面的外键约束，而是在应用代码层面解决外键逻辑。
[En]
In fact, this specification in Ali’s development manual does not mean that we are not allowed to use foreign keys, but that we do not need the foreign key constraints at the database level and solve the foreign key logic at the application code level.
在数据库级别使用外键，问题是显而易见的，不会产生脏数据。
[En]
Using foreign keys at the database level, the problem is obvious and will not produce dirty data.
另一方面，应用层解决外键，这使得外键约束的数据一致性问题更加模糊，导致数据脏，如下所示
[En]
On the other hand, the application layer solves the foreign key, which makes the data consistency problem of the foreign key constraint more obscure, resulting in dirty data, as follows
从此，我们走上了修复数据的不归路。
[En]
Since then, we have embarked on the road of no return in repairing data.
2、主键加速自增
很多情况下，我们的主键是 int 或者 bigint 类型，并且设置成了自增
不管是 int 还是 bigint ，都有一个最大值，如果一直自增下去，总有一天会达到最大值（可能到地老天荒也达不到这个值）
replace into 的更新是先删除再插入，会导致主键自增 1（照理来说，更新是不应该导致主键自增 1）
如果更新频率远远大于插入频率，则可能需要考虑不需要考虑的自增加主键耗尽的问题。
[En]
If the update frequency is much greater than the insertion frequency, the problem of running out of self-increasing primary keys that need not be considered may need to be considered.
此外，它还会导致主键不连续，主键值在表中跳跃。
[En]
In addition, it will also cause the primary key to be discontinuous and the primary key value to jump in the table.
3、主从切换问题
master：master -local ，slave：slave -192.168.0.112 ，同步库：my_project
从上图可以看出，主从复制是正常的。
[En]
As can be seen from the above picture, master-slave replication is normal.
接下来我们看看 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
可能有人会想：只要数据一致，跟下一个自增主键的不一致有什么关系？
[En]
Some people may think: as long as the data are consistent, what does it have to do with the inconsistency of the next self-increasing primary key?
我们来想一下这个问题：如果 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
目前，我相信我们有两个共同的问题。
[En]
At the moment, I believe we have two questions in common.
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 处理了
批量操作
不仅支持单次操作，还支持批量操作
[En]
Supports not only single operation, but also batch operation
和批量插入类似
有坑
因为 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、如何选择哪种方式
以上三种方法各有优势和策略，代码处理不依赖于特定数据库，具有很高的可移植性，不会引入特定数据库在这方面的缺陷。
[En]
The above three methods have their own advantages and strategies, the code processing does not depend on the specific database, has high portability, and will not introduce the defects of the specific database in this aspect.
replace into 的方式不推荐（坑有点多），它完全可以由 INSERT UPDATE 替代
INSERT UPDATE 可以减少我们的代码，但它是 MySQL 的拓展实现，只有 MySQL 支持，可移植性差
2、针对 INSERT UPDATE 的 “坑”，我们可以结合具体的业务来设置 innodb_autoinc_lock_mode ，适当的避免它的 “坑”
3、道路千万条，合适第一条
实现某种需求的方法往往有很多种，我们需要做的就是找到最合适的一种。
[En]
There are often many ways to realize a certain requirement, and what we need to do is to find the most suitable one.
参考
INSERT … ON DUPLICATE KEY UPDATE Statement
Original: https://www.cnblogs.com/youzhibing/p/15248758.html
Author: 青石路
Title: 记录不存在则插入，存在则更新 → MySQL 的实现方式有哪些？
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/508114/
转载文章受原作者版权保护。转载请注明原作者出处！