MySQL有哪些实现方式？何为插入，何为更新？

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！

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

我们不用外键了，也就不会出现前面的 [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、道路千万条，合适第一条
针对某个需求，实现方式往往有很多，我们要做的就是从中找到最适合的那一条

总结

上述知识点，囊括了目前互联网企业的主流应用技术以及能让你成为“香饽饽”的高级架构知识，每个笔记里面几乎都带有实战内容。

很多人担心学了容易忘，这里教你一个方法，那就是重复学习。

打个比方，假如你正在学习 spring 注解，突然发现了一个注解@Aspect，不知道干什么用的，你可能会去查看源码或者通过博客学习，花了半小时终于弄懂了，下次又看到@Aspect 了，你有点郁闷了，上次好像在哪哪哪学习，你快速打开网页花了五分钟又学会了。

从半小时和五分钟的对比中可以发现多学一次就离真正掌握知识又近了一步。

人的本性就是容易遗忘，只有不断加深印象、重复学习才能真正掌握，所以很多书我都是推荐大家多看几遍。哪有那么多天才，他只是比你多看了几遍书。

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！
ct，不知道干什么用的，你可能会去查看源码或者通过博客学习，花了半小时终于弄懂了，下次又看到@Aspect 了，你有点郁闷了，上次好像在哪哪哪学习，你快速打开网页花了五分钟又学会了。

从半小时和五分钟的对比中可以发现多学一次就离真正掌握知识又近了一步。

[外链图片转存中…(img-gTkEcryr-1714713540861)]

人的本性就是容易遗忘，只有不断加深印象、重复学习才能真正掌握，所以很多书我都是推荐大家多看几遍。哪有那么多天才，他只是比你多看了几遍书。

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！