深入探討MySQL中 UPDATE 的使用細節

在MySQL中，可以使用 UPDATE 語句來修改、更新一個或多個表格的資料。以下這篇文章帶大家探究下MySQL中 UPDATE 的使用細節，希望對大家有幫助。

需求背景

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

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

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

##n SET

 子句的 value

 是表達式，我們可以理解，這個 ######DEFAULT###### 是什麼意思？ ######　　我們先來看這麼一個問題，假設某列被宣告了 ######NOT### ###NULL###### ，然而我們更新這列成 ### ###NULL###### ###############　　會發生什麼#############

 　　我們看下 SQL_MODE ，執行 SELECT @@sql_mode; 得到結果

#; 得到結果#　　 STRICT_TRANS_TABLES 顯示啟動了嚴格模式，對 INSERT 和 

UPDATE

## 語句的問題」# 

##C #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 偶爾用， #ORDERIGNORE 偶爾用， #ORDER

BY

 和 LIMIT

 相對會使用的多一點，都混合眼熟

　　3、 

sql_mode

 是比較重要的知識點，推薦大家掌握；生產環境，強烈建議開啟嚴格模式

原文網址：https://www.cnblogs.com/youzhibing/p/16719474.html作者：青石路

######【相關推薦：###mysql影片教學###】###

以上是深入探討MySQL中 UPDATE 的使用細節的詳細內容。更多資訊請關注PHP中文網其他相關文章！