記一次批量更新整型類型的列 → 探究 UPDATE 的使用細節

来源:https://www.cnblogs.com/youzhibing/archive/2022/09/27/16719474.html
-Advertisement-
Play Games

開心一刻 今天,她給我打來電話 她:你明天陪我去趟醫院吧 我:怎麼了 她:我懷孕了,陪我去打胎 我:他的嗎 她:嗯 我心一沉,猶豫了片刻:生下來吧,我養! 她:他的孩子,你不配養! 我:我隨孩子姓 需求背景 最近接到一個數據遷移的需求,舊系統的數據遷移到新系統;舊系統不會再新增業務數據,業務操作都在 ...


開心一刻

  今天,她給我打來電話

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

  我:怎麼了

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

  我:他的嗎

  她:嗯

  我心一沉,猶豫了片刻:生下來吧,我養!

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

  我:我隨孩子姓

需求背景

  最近接到一個數據遷移的需求,舊系統的數據遷移到新系統;舊系統不會再新增業務數據,業務操作都在新系統上進行

  為了降低遷移的影響,數據進行分批遷移,也就是說新舊系統會並行一段時間

  數據分批不是根據 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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • haproxy 部署haproxy haproxy源碼包下載網站地址 | 主機名稱 | IP地址 | 需要安裝的應用 | 系統版本 | | | | | | | LB | 192.168.111.141 | haproxy | centos 8 | | RS1 | 192.168.111.142 | ...
  • Redis Desktop Manager for Mac是Mac平臺上一款非常實用的Redis可視化工具。RDM支持SSL / TLS加密,SSH隧道,基於SSH隧道的TLS,為您提供了一個易於使用的GUI,可以訪問您的Redis資料庫並執行一些基本操作:將鍵視為樹,CRUD鍵,通過shell執行 ...
  • 如何能打開閱讀chm格式文件?使用CHM Reader for Mac即可直接狀態欄打開即可使用,非常方便。 詳情:iCHM Reader for Mac(chm格式文件閱讀器) iCHM Reader中文版是最終CHM(編譯的HTML幫助)文件閱讀器。可以讓你閱讀較大的CHM文檔,在mac系統中使 ...
  • 晶體結構軟體CrystalMaker for mac創建、顯示和操作各種晶體和分子結構 ,CrystalMaker Mac版便捷、靈活,能夠容易的載入結構數據並產生壯觀的,相片型的圖形,戴上紅/藍眼鏡,還可以感受立體三維畫面,親臨分子結構當中。 詳情:CrystalMaker for Mac(晶體結 ...
  • 電阻種類很多,常用的有貼片電阻、插件電阻、熱敏電阻、壓敏電阻、光敏電阻、水泥電阻、可調電阻。 可調電阻在成品的PCBA中很少見,也大多用於電路調試中試用,等電路調試完成後再換成固定阻值的電阻,起到電路參數調節的作用。水泥電阻則在調試的時候會用到更多,當做假負載來使用。 這裡說下假負載,假負載並不是電 ...
  • 準備工作 一臺Linux(Centos7為例)伺服器。 安裝Docker服務。 安裝並啟動SqlServer容器服務。 編寫Shell文件 給出一個備份的範例 #!/bin/bash #設置mssql備份目錄 folder=/var/opt/mssql/data/databack/ day=`dat ...
  • 風裡雨里,我在深圳機場等你,口說無憑,上圖! 這是一段很長的故事!以前倒也不曾提過~ 銀河證券和騰訊雲資料庫長久以來並肩作戰,情比金堅,我們的故事日前在深圳寶安機場上映 他說:做好國產化分散式改造,就用騰訊雲資料庫。 我說:做國產資料庫,我是認真的。 誕生於2007年的騰訊雲資料庫現已歷經十四年的錘 ...
  • 1 導讀 數據的一致性是數據準確的重要指標,那如何實現數據的一致性呢?本文從事務特性和事務級別的角度和大家一起學習如何實現數據的讀寫一致性。 2 一致性 1.數據的一致性:通常指關聯數據之間的邏輯關係是否正確和完整。 舉個例子:某系統實現讀寫分離,讀資料庫是寫資料庫的備份庫,小李在系統中之前錄入的學 ...
一周排行
    -Advertisement-
    Play Games
  • 經常看到有群友調侃“為什麼搞Java的總在學習JVM調優?那是因為Java爛!我們.NET就不需要搞這些!”真的是這樣嗎?今天我就用一個案例來分析一下。 昨天,一位學生問了我一個問題:他建了一個預設的ASP.NET Core Web API的項目,也就是那個WeatherForecast的預設項目模 ...
  • 很多軟體工程師都認為MD5是一種加密演算法,然而這種觀點是不對的。作為一個 1992 年第一次被公開的演算法,到今天為止已經被髮現了一些致命的漏洞。本文討論MD5在密碼保存方面的一些問題。 ...
  • Maven可以使我們在構建項目時需要用到很多第三方類jar包,如下一些常用jar包 而maven的出現可以讓我們避免手動導入jar包出現的某些問題,它可以自動下載那須所需要的jar包 我們只需要在創建的maven項目自動生成的pom.xml中輸入如下代碼 <dependencies> <!--ser ...
  • 來源:https://developer.aliyun.com/article/694020 非同步調用幾乎是處理高併發Web應用性能問題的萬金油,那麼什麼是“非同步調用”? “非同步調用”對應的是“同步調用”,同步調用指程式按照定義順序依次執行,每一行程式都必須等待上一行程式執行完成之後才能執行;非同步調 ...
  • 1.面向對象 面向對象編程是在面向過程編程的基礎上發展來的,它比面向過程編程具有更強的靈活性和擴展性,所以可以先瞭解下什麼是面向過程編程: 面向過程編程的核心是過程,就是分析出實現需求所需要的步驟,通過函數一步一步實現這些步驟,接著依次調用即可,再簡單理解就是程式 從上到下一步步執行,從頭到尾的解決 ...
  • 10瓶毒藥其中只有一瓶有毒至少需要幾隻老鼠可以找到有毒的那瓶 身似浮雲,心如飛絮,氣若游絲。 用二分查找和二進位位運算的思想都可以把死亡的老鼠降到最低。 其中,二進位位運算就是每一隻老鼠代表一個二進位0或1,0就代表老鼠存活,1代表老鼠死亡;根據數學運算 23 = 8、24 = 16,那麼至少需要四 ...
  • 一、Kafka存在哪些方面的優勢 1. 多生產者 可以無縫地支持多個生產者,不管客戶端在使用單個主題還是多個主題。 2. 多消費者 支持多個消費者從一個單獨的消息流上讀取數據,而且消費者之間互不影響。 3. 基於磁碟的數據存儲 支持消費者非實時地讀取消息,由於消息被提交到磁碟,根據設置的規則進行保存 ...
  • 大家好,我是陶朱公Boy。 前言 上一篇文章《關於狀態機的技術選型,最後一個真心好》我跟大家聊了一下關於”狀態機“的話題。從眾多技術選型中我也推薦了一款阿裡開源的狀態機—“cola-statemachine”。 於是就有小伙伴私信我,自己項目也考慮引入這款狀態機,但網上資料實在太少,能不能系統的介紹 ...
  • 使用腳本自動跑實驗(Ubuntu),將實驗結果記錄在文件中,併在實驗結束之後將結果通過郵件發送到郵箱,最後在windows端自動解析成excel表格。 ...
  • 話說在前面,我不是小黑子~ 我是超級大黑子😏 表弟大周末的跑來我家,沒事幹天天騷擾我,搞得我都不能跟小姐姐好好聊天了,於是為了打發表弟,我決定用Python做一個小游戲來消耗一下他的精力,我思來想去,決定把他變成小黑子,於是做了一個坤坤打籃球的游戲,沒想到他還挺愛玩的~ 終於解放了,於是我把游戲寫 ...