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

我就是我 2023-09-28 06:37 48阅读 0赞

## 需求背景 ##

## 环境 ##

MySQL 版本： **5.7**.**20**\-log

## 开发规范 ##

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

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

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

![76046003856b2f8b63e54ce4e0581fe3.png][]

## 需求 ##

我们有 表 如下：

![0d46117cc46a790891dba79cdbda82c9.png][]

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

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

## 代码处理 ##

按开发规范中说的处理

![e984f7d1a5e199047173f372f99fa10a.png][]

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

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

## REPLACE INTO ##

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

## 工作原理 ##

replace into 跟 insert 功能类似

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

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

我们来看个示例

![b8b05218673b8b122629125d6ffe0de3.png][]

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

需要强调的是：根据唯一索引 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 更新（非插入）时影响到了其他表的外键约束，那么会执行失败，提示类似信息：

![75811dab17495af7b74815c516be80cf.png][]

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

![c13fb721e8c8df3aacf1ed938d91e531.png][]

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

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

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

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

![07588c5a635b5d7cb9a4ca520985d26e.png][]

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

## 2、主键加速自增 ##

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

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

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

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

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

## 3、主从切换问题 ##

master：master-local ，slave：slave-**192.168**.**0.112** ，同步库：my\_project

![9e379770e5ac34e33d0f1d2ce4c916fc.png][]

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

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

![d745564aa3b31ee565f7f563a83ae574.png][]

此时 master 与 slave 上的  
  
t\_ware\_last\_delivery\_price 的下一个非手工指定的主键都是 11（ AUTO\_INCREMENT=**11** ），两者是一致的

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

![6ab4ace2a55cbf96ef28a12394404ee4.png][]

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，则以下两条语句具有类似的效果

![1bda657ca3f1faa3856c8eea657947ba.png][]

但是这两条 SQL 的效果并不完全相同，我们以  
  
t\_ware\_last\_delivery\_price 为例，来看看它们的区别

我们先来看看 UPDATE

![8cc0d6545d417320317e010d284dd3e8.png][]

只是对 id = **11** 的 last\_delivery\_price 就行了修改，受影响的行只有 1，不会影响 AUTO\_INCREMENT 的值

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

![66aea0e786608b689d19377d3d85dcd1.png][]

对 id = **11** 的 last\_delivery\_price 进行了修改，受影响的行是 2，并且 AUTO\_INCREMENT=**13**

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

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

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

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

![a3aeb2e1acd9a56a4be70534258e237f.png][]

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

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

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

主键明明没有变化，为什么 AUTO\_INCREMENT=**13** 自增了 1 ？

这和 MySQL 的主键自增的参数有关 innodb\_autoinc\_lock\_mode ，它有 3 个值 **0**,**1**,**2**

![b058bb6bf75fbaecb92f8e983bd2f13d.png][]

mysql5.**1** 之后其默认值是 1

![8c8989cec176610dd7da5efd26652fcb.png][]

因为 innodb\_autoinc\_lock\_mode = **1**

![843aa0124b13aaef6d79fd8b53c77088.png][]

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

## 批量操作 ##

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

![9e3f256aa5f32bfbceb2112714807767.png][]

和批量插入类似

## 有坑 ##

因为 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、道路千万条，合适第一条

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

![c43738bd51d3400e823dc037d7de5c92.jpeg][]

[76046003856b2f8b63e54ce4e0581fe3.png]: https://img-blog.csdnimg.cn/img_convert/76046003856b2f8b63e54ce4e0581fe3.png
[0d46117cc46a790891dba79cdbda82c9.png]: https://img-blog.csdnimg.cn/img_convert/0d46117cc46a790891dba79cdbda82c9.png
[e984f7d1a5e199047173f372f99fa10a.png]: https://img-blog.csdnimg.cn/img_convert/e984f7d1a5e199047173f372f99fa10a.png
[b8b05218673b8b122629125d6ffe0de3.png]: https://img-blog.csdnimg.cn/img_convert/b8b05218673b8b122629125d6ffe0de3.png
[75811dab17495af7b74815c516be80cf.png]: https://img-blog.csdnimg.cn/img_convert/75811dab17495af7b74815c516be80cf.png
[c13fb721e8c8df3aacf1ed938d91e531.png]: https://img-blog.csdnimg.cn/img_convert/c13fb721e8c8df3aacf1ed938d91e531.png
[07588c5a635b5d7cb9a4ca520985d26e.png]: https://img-blog.csdnimg.cn/img_convert/07588c5a635b5d7cb9a4ca520985d26e.png
[9e379770e5ac34e33d0f1d2ce4c916fc.png]: https://img-blog.csdnimg.cn/img_convert/9e379770e5ac34e33d0f1d2ce4c916fc.png
[d745564aa3b31ee565f7f563a83ae574.png]: https://img-blog.csdnimg.cn/img_convert/d745564aa3b31ee565f7f563a83ae574.png
[6ab4ace2a55cbf96ef28a12394404ee4.png]: https://img-blog.csdnimg.cn/img_convert/6ab4ace2a55cbf96ef28a12394404ee4.png
[1bda657ca3f1faa3856c8eea657947ba.png]: https://img-blog.csdnimg.cn/img_convert/1bda657ca3f1faa3856c8eea657947ba.png
[8cc0d6545d417320317e010d284dd3e8.png]: https://img-blog.csdnimg.cn/img_convert/8cc0d6545d417320317e010d284dd3e8.png
[66aea0e786608b689d19377d3d85dcd1.png]: https://img-blog.csdnimg.cn/img_convert/66aea0e786608b689d19377d3d85dcd1.png
[a3aeb2e1acd9a56a4be70534258e237f.png]: https://img-blog.csdnimg.cn/img_convert/a3aeb2e1acd9a56a4be70534258e237f.png
[b058bb6bf75fbaecb92f8e983bd2f13d.png]: https://img-blog.csdnimg.cn/img_convert/b058bb6bf75fbaecb92f8e983bd2f13d.png
[8c8989cec176610dd7da5efd26652fcb.png]: https://img-blog.csdnimg.cn/img_convert/8c8989cec176610dd7da5efd26652fcb.png
[843aa0124b13aaef6d79fd8b53c77088.png]: https://img-blog.csdnimg.cn/img_convert/843aa0124b13aaef6d79fd8b53c77088.png
[9e3f256aa5f32bfbceb2112714807767.png]: https://img-blog.csdnimg.cn/img_convert/9e3f256aa5f32bfbceb2112714807767.png
[c43738bd51d3400e823dc037d7de5c92.jpeg]: https://img-blog.csdnimg.cn/c43738bd51d3400e823dc037d7de5c92.jpeg