開心一刻
今天，她給我打來電話
她：你明天陪我去趟醫院吧
我：怎么了
她：我懷孕了，陪我去打胎
我：他的嗎
她：嗯
我心一沉，猶豫了片刻：生下來吧，我養！
她：他的孩子，你不配養！
我：我隨孩子姓
需求背景
最近接到一個資料遷移的需求，舊系統的資料遷移到新系統；舊系統不會再新增業務資料，業務操作都在新系統上進行
為了降低遷移的影響，資料進行分批遷移，也就是說新舊系統會并行一段時間
資料分批不是根據 id 范圍來分的，也就說每批資料的 id 都是無規律的
另外，為了保證新舊系統資料的對應，新系統的 id 盡可能的沿用舊系統的 id
因為表 id 在新舊系統都是自增的，所以遷移的時候，舊系統的 id 可能在新系統已經被占用了，類似如下
需求描述
資料遷移的時候，盡可能沿用舊系統的 id，而沖突的 id 需要進行批量調整
如何調整這批沖突的 id，正是我當下要實作的需求
我的實作是根據業務資料的增長情況，結合目前新系統的最大 id 來預設一個起始的 id
這個 SQL 該如何寫？
需求實作
有小伙伴可能覺得，這還不簡單？
不就 5 條資料嘛，這么寫不就搞定了
多簡單的事，還鋪墊那么多，樓主你到底會不會？
樓主此刻幡然醒悟：小伙伴，你好厲害哇哦
但是如果沖突的資料很多了（幾百上千），你也這樣一條一條改？
如果你真這樣做，我是真心佩服你
很顯然，理智的小伙伴更多
那該如何實作了？
樓主就不賣關子了，可以用區域變數 + 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 中的 ORDER 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 是成功的，但更改的值并非 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
想必大家都很清楚
然而，以下 SQL 中的 name 列的值會是多少
我們來看下結果
name 的值是不是和預想的有點不一樣？
單表 UPDATE 的 SET 是從左往右進行的，然而多表 UPDATE 卻不是，多表 UPDATE 不能保證按任何特定順序進行
總結
1、不管是 UPDATE ，還是 DELETE ，都有一個先查的程序，查到一行處理一行
2、 UPDATE 語法中的 LOW_PRIORITY 很少用， IGNORE 偶爾用， ORDER BY 和 LIMIT 相對會用的多一點，都混個眼熟
3、 sql_mode 是比較重要的知識點，推薦大家掌握；生產環境，強烈推薦開啟嚴格模式
參考
UPDATE Statement
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/509725.html
標籤：其他
上一篇：Linux下定時自動備份Docker中所有SqlServer資料庫
下一篇：如何實作資料庫讀一致性