登录/注册
tom
1857
占位
1
占位
0
浏览量
占位
粉丝
占位
关注
记录不存在则插入,存在则更新 → MySQL 的实现方式有哪些?
tom
2021-09-22 17:48:05 2021-09-22
104
0

开心一刻

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

女儿问道:爸爸,你吃鸡腿吗

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

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

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

女儿向她爷爷问道:爷爷,你吃鸡腿吗

我爸一脸溺爱的说道:吃

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

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

女儿问道:爸爸,你吃公鸡腿吗

我信誓旦旦地说道:我吃

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

我彻底懵了

需求背景

环境

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超大问题查询

原文: https://www.cnblogs.com/youzhibing/p/15248758.html

暂无评论