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

开心一刻

  今天,她给我打来电话

  她:你明天陪我去趟医院吧

  我:怎么了

  她:我怀孕了,陪我去打胎

  我:他的吗

  她:嗯

  我心一沉,犹豫了片刻:生下来吧,我养!

  她:他的孩子,你不配养!

  我:我随孩子姓

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

需求背景

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

  为了降低迁移的影响,数据进行分批迁移,也就是说新旧系统会并行一段时间

  数据分批不是根据 id 范围来分的,也就说每批数据的 id 都是无规律的

  另外,为了保证新旧系统数据的对应,新系统的 id 尽可能的沿用旧系统的 id

  因为表 id 在新旧系统都是自增的,所以迁移的时候,旧系统的 id 可能在新系统已经被占用了,类似如下

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

  需求描述

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

  如何调整这批冲突的 id,正是我当下要实现的需求

  我的实现是根据业务数据的增长情况,结合目前新系统的最大 id 来预设一个起始的 id

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

  这个 SQL 该如何写?

  需求实现

  有小伙伴可能觉得,这还不简单?

  不就 5 条数据嘛,这么写不就搞定了

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

  多简单的事,还铺垫那么多,楼主你到底会不会?

  楼主此刻幡然醒悟:小伙伴,你好厉害哇哦

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

  但是如果冲突的数据很多了(几百上千),你也这样一条一条改?

  如果你真这样做,我是真心佩服你

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

  很显然,理智的小伙伴更多

  那该如何实现了?

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

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

  我们来看实际案例

  表 tbl_batch_update 

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

  数据如下

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

  执行效果如下

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

  更新之后

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

  更严谨点

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

 

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

  还真支持,如下所示

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

  楼主平时使用 UPDATE 的时候,基本没结合 ORDER BY ,也没尝试过结合 LIMIT 

  这次尝试让楼主对 UPDATE 产生了陌生的感觉,它的完整语法应该是怎样的?我们慢慢往下看

UPDATE

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

  单表语法

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

 

   是不是有很多疑问:

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

  多表语法

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

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

  LOW_PRIORITY

   UPDATE 的修饰符之一,用来降低 SQL 的优先级

  当使用 LOW_PRIORITY 之后, UPDATE 的执行将会被延迟,直到没有其他客户端从表中读取数据为止

  但是,只有表级锁的存储引擎才支持 LOW_PRIORITY ,表级锁的存储引擎包括: MyISAM 、 MEMORY 和 MERGE ,所以最常用的 InnoDB 是不支持的

  使用场景很少,混个眼熟就好

  IGNORE

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

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

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

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

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

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

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

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

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

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

  ORDER BY

  如果大家对 UDPATE 的执行流程了解的话,那就更好理解了

   UPDATE 其实有两个阶段: 查阶段 、 更新阶段 

  一行一行的处理,查到一行满足 WHERE 子句,就更新一行

  所以,这里的 ORDER BY 就和 SELECT 中的 ORDER BY 是一样的效果

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

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

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

  LIMIT

   LIMIT row_count 子句是行匹配限制。一旦找到满足 WHERE 子句的 row_count 行,无论这些行是否实际更改,该语句都会立即停止

  也是就说 LIMIT 限制的是 查阶段 ,与 更新阶段 没有关系

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

  注意:与 SELECT 语法中的 LIMIT 

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

  还是有区别的

  value DEFAULT

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

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

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

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

  会发生什么

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

 

 

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

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

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

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

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

   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 

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

  想必大家都很清楚

  然而,以下 SQL 中的 name 列的值会是多少

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

  我们来看下结果

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

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

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

总结

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

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

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

参考

  UPDATE Statement

发表评论

相关文章