mysql外键(mysql插数据)

内容广告上

00-1010

需求背景

MySQL版本:5 . 7 . 20-日志

环境

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

更新数据库表中的数据时,不允许先删除后批量插入。

需要对比参与表中的数据,找出哪些是新插入的,哪些需要更新,哪些删除,然后做相应的数据操作。

开发规范

我们有下表:

货物交付时,需要记录最新的交货价格。如果货物的最新交货价格已经存在,将会更新;如果它不存在,它将被插入。

我们如何实现这一需求?

需求

根据开发规范表示处理。

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

因为这是一个非常常规的插入和更新操作,所以这种处理方法适用于所有关系数据库。

00-1010当数据库是MySQL的时候,遇到不存在就插入,存在就更新的需求时,最先想到的就是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。

00-1010因为替换成的工作原理,难免有一些需要注意的地方。

00-1010如果主键被指定为其他表的外键,那么当replace into被更新(未插入)时,其他表的外键约束会受到影响,然后执行失败,提示类似信息:

很多小伙伴可能会说:在开发过程中,我们会遵循阿里开发手册中的规定,其中一条如下:

我们不需要外键,不会出现之前的[Err] 1451错误。

其实阿里开发手册中的这个规定,并不是说不允许我们使用外键,而是在应用代码层面解决外键逻辑,在数据库层面没有外键约束。

使用数据库级别的外键,问题很明显,不会生成脏数据。

但是应用层解决了外键,使得外键约束的数据一致性问题更加模糊,导致数据变脏,如下

tiaoimg.com/origin/pgc-image/128c9a5827224afab1a8817e794aae13?from=pc">

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

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

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

内容广告下