性能文章>记一次批量更新整型类型的列 → 探究 UPDATE 的使用细节>

记一次批量更新整型类型的列 → 探究 UPDATE 的使用细节原创

2月前
176513

需求背景

最近接到一个数据迁移的需求,旧系统的数据迁移到新系统;旧系统不会再新增业务数据,业务操作都在新系统上进行

为了降低迁移的影响,数据进行分批迁移,也就是说新旧系统会并行一段时间,数据分批不是根据 id 范围来分的,也就说每批数据的 id 都是无规律的。

另外,为了保证新旧系统数据的对应,新系统的 id 尽可能的沿用旧系统的 id,因为表 id 在新旧系统都是自增的,所以迁移的时候,旧系统的 id 可能在新系统已经被占用了,类似如下:

image1.png

需求描述

数据迁移的时候,尽可能沿用旧系统的 id,而冲突的 id 需要进行批量调整

如何调整这批冲突的 id,正是我当下要实现的需求,我的实现是根据业务数据的增长情况,结合目前新系统的最大 id 来预设一个起始的 id。

image2.png

这个 SQL 该如何写?

需求实现

有小伙伴可能觉得,这还不简单?不就 5 条数据嘛,这么写不就搞定了

多简单的事,还铺垫那么多,楼主你到底会不会?楼主此刻幡然醒悟:小伙伴,你好厉害哇哦

image3.png

但是如果冲突的数据很多了(几百上千),你也这样一条一条改?如果你真这样做,我是真心佩服你很显然,理智的小伙伴更多,那该如何实现了?

image5.GIF

楼主就不卖关子了,可以用局部变量 + UPDATE 来实现,直接上 SQL

image6.png
我们来看实际案例,表 tbl_batch_update:

image7.png

数据如下
image8.png

执行效果如下

9

更新之后

image10.png

更严谨点

image11.png

该如何实现? UPDATE 是不是也支持 ORDER BY ?

还真支持,如下所示

楼主平时使用 UPDATE 的时候,基本没结合 ORDER BY ,也没尝试过结合 LIMIT ,这次尝试让楼主对 UPDATE 产生了陌生的感觉,它的完整语法应该是怎样的?我们慢慢往下看

UPDATE

下文都是基于 MySQL 8.0 的官方文档 UPDATE Statement 整理而来,推荐大家直接去看官方文档

单表语法

image13.png

是不是有很多疑问:

image14.png

多表语法
image15.png

相比于单表,貌似更简单一些,不支持 ORDER BY 和 LIMIT

LOW_PRIORITY

UPDATE 的修饰符之一,用来降低 SQL 的优先级,当使用 LOW_PRIORITY 之后, UPDATE 的执行将会被延迟,直到没有其他客户端从表中读取数据为止。

但是,只有表级锁的存储引擎才支持 LOW_PRIORITY ,表级锁的存储引擎包括: MyISAM 、 MEMORY 和 MERGE ,所以最常用的 InnoDB 是不支持的,使用场景很少,混个眼熟就好。

IGNORE

UPDATE 的修饰符之一,用来声明 SQL 执行时发生错误的处理方式

如果没有使用 IGNORE , UPDATE 执行时如果发生错误会中止,如下所示

9002 更新成 9003 的时候,主键冲突,整个 UPDATE 中止, 9000 更新成的 9001 会回滚, 9003 ~ 9005 还未执行更新

如果使用 IGNORE ,会是什么情况了?

UPDATE 执行期间即使发生错误了,也会执行完成,最终返回受影响的行数。

上述返回受影响的行是 2 ,你们说说是哪两行修改了?

更多关于 IGNORE 的信息,请查看:The Effect of IGNORE on Statement Execution

关于使用场景,在新旧系统并行,做数据迁移的时候可能会用到,主键或者唯一键冲突的时候直接忽略

ORDER BY

如果大家对 UDPATE 的执行流程了解的话,那就更好理解了,UPDATE 其实有两个阶段: 查阶段 、 更新阶段 。一行一行的处理,查到一行满足 WHERE 子句,就更新一行,所以,这里的 ORDER BY 就和 SELECT 中的 ORDER BY 是一样的效果。

关于使用场景,大家可以回过头去看看前面讲到的的需求背景,

IGNORE 的案例 1 中的报错,其实也可以用 ORDER BY

image18.png

LIMIT

LIMIT row_count 子句是行匹配限制。一旦找到满足 WHERE 子句的 row_count 行,无论这些行是否实际更改,该语句都会立即停止,也是就说 LIMIT 限制的是 查阶段 ,与更新阶段没有关系。

注意:与 SELECT 语法中的 LIMIT 还是有区别的

image20.png

value DEFAULT

image21.png
   UPDATE 中 SET 子句的 value 是表达式,我们可以理解,这个 DEFAULT 是什么意思?

我们先来看这么一个问题,假设某列被声明了 NOT NULL ,然而我们更新这列成 NULL

image22.png

会发生什么

image23.png

我们看下 SQL_MODE ,执行 SELECT @@sql_mode; 得到结果

image24.png

STRICT_TRANS_TABLES 表明启动了严格模式,对 INSERT 和 UPDATE 语句的 value 管控会更严格

如果我们关闭严格模式,再看看执行结果

name 字段声明成了 NOT NULL ,非严格 SQL 模式下,将 name 设置成 NULL 是成功的,但更改的值并非 NULL ,而是 VARCHAR 类型的默认值: 空字符串(’’)

小结

1、严格 SQL 模式下,对 NOT NULL 的字段设置 NULL ,会直接报错,更新失败

2、非严格 SQL 模式下,对 NOT NULL 的字段设置 NULL ,会将字段值设置字段类型对应的默认值

关于字段类型的默认值,可查看:Data Type Default Values

关于 sql_mode ,可查看:Server SQL Modes

通常情况下,生成环境的 MySQL 一般都是严格模式,所以大家知道有 value DEFAULT 这回事就够了

SET 字段顺序

针对如下 SQL

image26.png

想必大家都很清楚,然而,以下 SQL 中的 name 列的值会是多少

image27.png

我们来看下结果


name 的值是不是和预想的有点不一样?

单表 UPDATE 的 SET 是从左往右进行的,然而多表 UPDATE 却不是,多表 UPDATE 不能保证按任何特定顺序进行

总结

1、不管是 UPDATE ,还是 DELETE ,都有一个先查的过程,查到一行处理一行

2、 UPDATE 语法中的 LOW_PRIORITY 很少用, IGNORE 偶尔用, ORDER BY 和 LIMIT 相对会用的多一点,都混个眼熟

3、 sql_mode 是比较重要的知识点,推荐大家掌握;生产环境,强烈推荐开启严格模式

点赞收藏
分类:标签:
青石路
请先登录,查看1条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

【全网首发】总结 mysql 的所有 buffer,一网打尽就这篇了!

【全网首发】总结 mysql 的所有 buffer,一网打尽就这篇了!

大家都在用MySQL count(*)统计总数,到底有什么问题?

大家都在用MySQL count(*)统计总数,到底有什么问题?

MySQL 崩溃恢复过程分析

MySQL 崩溃恢复过程分析

两个事务并发写,能保证数据唯一吗?

两个事务并发写,能保证数据唯一吗?

我说HashMap初始容量是16,面试官让我回去等通知

我说HashMap初始容量是16,面试官让我回去等通知

【译】如何使用MySQL来设计分布式锁?

【译】如何使用MySQL来设计分布式锁?

3
1