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

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

开心一刻

　　今天，她给我打来电话

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

　　我：怎么了

　　她：我怀孕了，陪我去打胎

　　我：他的吗

　　她：嗯

　　我心一沉，犹豫了片刻：生下来吧，我养！

　　她：他的孩子，你不配养！

　　我：我随孩子姓

需求背景

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

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

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

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

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

　　需求描述

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

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

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

　　这个SQL该如何写？

　　需求实现

　　有小伙伴可能觉得，这还不简单？

　　不就 5 条数据嘛，这么www.yii666.com写不就搞定了

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

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

　　但是如果冲突的数据很多了（几百上千），你也这样一条一条改？

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

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

　　那该如何实现了？

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

　　我们来看实际案例

　　表tbl_batch_update

　　数据如下

　　执行效果如下

　　更新之后

　　更严谨点

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

　　还真支持，如下所示

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

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

UPDATE

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

　　单表语法

　　是不是有很多疑问：

　　多表语法

　　相比于单表，貌似更简单一些，不支持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中的OR文章来源地址8961.htmlDER BY是一样的效果

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

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

　　LIMIT

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

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

　　注意：与SELECT语法中的LIMIT

　　还是有区别的

　　value DEFAULT

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

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

　　会发生什么

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

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

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

　　name字段声明成了NOT NULL，非严格SQL模式下，将name设置成NULL是成功的，但更改的文章来源站点https://www.yii666.com/值并非NULL，而是VARCHAR类型的默认值：空字符串('')

　　小结下

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

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

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

　　关于sql_mode，可查看：Server SQL Modes

　　通常情况下，生成环境的MySQL一般都是严格模式，所以大家知道有v文章来源地址8961.htmlalue DEFAULT这回事就够了

　　SET 字段顺序

　　针对如下SQL

　　想必大家都很清楚

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

　　我们来看下结果

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

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

总结

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

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

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

参考

　　UPDATE Statement

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