MySQL中UPDATE的使用細(xì)節(jié)是什么

本篇內(nèi)容介紹了“MySQL中UPDATE的使用細(xì)節(jié)是什么”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

需求背景

　　最近接到一個數(shù)據(jù)遷移的需求，舊系統(tǒng)的數(shù)據(jù)遷移到新系統(tǒng)；舊系統(tǒng)不會再新增業(yè)務(wù)數(shù)據(jù)，業(yè)務(wù)操作都在新系統(tǒng)上進行

　　為了降低遷移的影響，數(shù)據(jù)進行分批遷移，也就是說新舊系統(tǒng)會并行一段時間

　　數(shù)據(jù)分批不是根據(jù) id 范圍來分的，也就說每批數(shù)據(jù)的 id 都是無規(guī)律的

　　另外，為了保證新舊系統(tǒng)數(shù)據(jù)的對應(yīng)，新系統(tǒng)的 id 盡可能的沿用舊系統(tǒng)的 id

　　因為表 id 在新舊系統(tǒng)都是自增的，所以遷移的時候，舊系統(tǒng)的 id 可能在新系統(tǒng)已經(jīng)被占用了，類似如下

需求描述

　　數(shù)據(jù)遷移的時候，盡可能沿用舊系統(tǒng)的 id，而沖突的 id 需要進行批量調(diào)整

　　如何調(diào)整這批沖突的 id，正是我當(dāng)下要實現(xiàn)的需求

　　我的實現(xiàn)是根據(jù)業(yè)務(wù)數(shù)據(jù)的增長情況，結(jié)合目前新系統(tǒng)的最大 id 來預(yù)設(shè)一個起始的 id

　　這個 SQL 該如何寫？

需求實現(xiàn)

　　有小伙伴可能覺得，這還不簡單？

　　不就 5 條數(shù)據(jù)嘛，這么寫不就搞定了

　　多簡單的事，還鋪墊那么多，樓主你到底會不會？

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

　　但是如果沖突的數(shù)據(jù)很多了（幾百上千），你也這樣一條一條改？

　　如果你真這樣做，我是真心佩服你

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

　　那該如何實現(xiàn)了？

　　樓主就不賣關(guān)子了，可以用局部變量 +  UPDATE 來實現(xiàn)，直接上 SQL

　　我們來看實際案例

　　表 tbl_batch_update

　　數(shù)據(jù)如下

　　執(zhí)行效果如下

　　更新之后

　　更嚴(yán)謹(jǐn)點

　　該如何實現(xiàn)？  UPDATE 是不是也支持 ORDER BY ？

　　還真支持，如下所示

　　樓主平時使用 UPDATE 的時候，基本沒結(jié)合 ORDER BY ，也沒嘗試過結(jié)合 LIMIT

　　這次嘗試讓樓主對 UPDATE 產(chǎn)生了陌生的感覺，它的完整語法應(yīng)該是怎樣的？我們慢慢往下看

UPDATE

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

　　單表語法

　　是不是有很多疑問：

　　多表語法

　　相比于單表，貌似更簡單一些，不支持 ORDER BY 和  LIMIT

　　LOW_PRIORITY

　　 UPDATE 的修飾符之一，用來降低 SQL 的優(yōu)先級

　　當(dāng)使用 LOW_PRIORITY 之后， UPDATE 的執(zhí)行將會被延遲，直到?jīng)]有其他客戶端從表中讀取數(shù)據(jù)為止

　　但是，只有表級鎖的存儲引擎才支持 LOW_PRIORITY ，表級鎖的存儲引擎包括： MyISAM 、 MEMORY 和 MERGE ，所以最常用的 InnoDB 是不支持的

　　使用場景很少，混個眼熟就好

　　IGNORE

　　 UPDATE 的修飾符之一，用來聲明 SQL 執(zhí)行時發(fā)生錯誤的處理方式

　　如果沒有使用 IGNORE ， UPDATE 執(zhí)行時如果發(fā)生錯誤會中止，如下所示

　　 9002 更新成 9003 的時候，主鍵沖突，整個 UPDATE 中止， 9000 更新成的 9001 會回滾， 9003 ~ 9005 還未執(zhí)行更新

　　如果使用 IGNORE ，會是什么情況了？

　　 UPDATE 執(zhí)行期間即使發(fā)生錯誤了，也會執(zhí)行完成，最終返回受影響的行數(shù)

　　上述返回受影響的行是 2 ，你們說說是哪兩行修改了？

　　更多關(guān)于 IGNORE 的信息，請查看：The Effect of IGNORE on Statement Execution

　　關(guān)于使用場景，在新舊系統(tǒng)并行，做數(shù)據(jù)遷移的時候可能會用到，主鍵或者唯一鍵沖突的時候直接忽略

　　ORDER BY

　　如果大家對 UDPATE 的執(zhí)行流程了解的話，那就更好理解了

　　 UPDATE 其實有兩個階段： 查階段 、 更新階段

　　一行一行的處理，查到一行滿足 WHERE 子句，就更新一行

　　所以，這里的 ORDER BY 就和 SELECT 中的 ORDER BY 是一樣的效果

　　關(guān)于使用場景，大家可以回過頭去看看前面講到的的需求背景，

　　IGNORE 的案例 1 中的報錯，其實也可以用 ORDER BY

　　LIMIT

　　 LIMIT row_count 子句是行匹配限制。一旦找到滿足 WHERE 子句的 row_count 行，無論這些行是否實際更改，該語句都會立即停止

　　也是就說 LIMIT 限制的是 查階段 ，與 更新階段 沒有關(guān)系

　　注意：與 SELECT 語法中的 LIMIT

　　還是有區(qū)別的

　　value DEFAULT

　　 UPDATE 中 SET 子句的 value 是表達(dá)式，我們可以理解，這個 DEFAULT 是什么意思？

　　我們先來看這么一個問題，假設(shè)某列被聲明了 NOT NULL ，然而我們更新這列成 NULL

　　會發(fā)生什么

　　我們看下 SQL_MODE ，執(zhí)行 SELECT @@sql_mode; 得到結(jié)果

　　 STRICT_TRANS_TABLES 表明啟動了嚴(yán)格模式，對 INSERT 和 UPDATE 語句的 value 管控會更嚴(yán)格

　　如果我們關(guān)閉嚴(yán)格模式，再看看執(zhí)行結(jié)果

　　 name 字段聲明成了 NOT NULL ，非嚴(yán)格 SQL 模式下，將 name 設(shè)置成 NULL 是成功的，但更改的值并非 NULL ，而是 VARCHAR 類型的默認(rèn)值： 空字符串('')

小結(jié)下

　　　　1、嚴(yán)格 SQL 模式下，對 NOT NULL 的字段設(shè)置 NULL ，會直接報錯，更新失敗

　　　　2、非嚴(yán)格 SQL 模式下，對 NOT NULL 的字段設(shè)置 NULL ，會將字段值設(shè)置字段類型對應(yīng)的默認(rèn)值

　　關(guān)于字段類型的默認(rèn)值，可查看：Data Type Default Values

　　關(guān)于 sql_mode ，可查看：Server SQL Modes

　　通常情況下，生成環(huán)境的 MySQL 一般都是嚴(yán)格模式，所以大家知道有 value DEFAULT 這回事就夠了

　　SET 字段順序

　　針對如下 SQL

　　想必大家都很清楚

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

　　我們來看下結(jié)果

　　 name 的值是不是和預(yù)想的有點不一樣？

　　單表 UPDATE 的 SET 是從左往右進行的，然而多表 UPDATE 卻不是，多表 UPDATE 不能保證按任何特定順序進行

“MySQL中UPDATE的使用細(xì)節(jié)是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！