比CRUD多一點兒（三）：UPDATE、DELETE語句

碼農肥波發表於2019-03-02
delete

這是MySQL系列筆記的第一部分，本系列筆記希望能按照筆者自己學習MySQL技術的經歷來記錄，避免純粹按照內容一塊一塊總結，也就是不同於一般按內容分配章節的書籍的結構，會有一個平滑的閱讀曲線。內容比較豐富的技術點會按照專題在多個學習筆記中逐漸深入。

這部分的標題叫比CRUD多一丁點兒，比起最基礎的w3c的SQL教程之外，只多一點的擴充套件，滿足應付從純粹閱讀入門資料到可以上手完成一個簡單的工作的需求。

第三篇的主要內容繼續是基礎SQL語句UPDATE和DELETE，這兩個相對比較簡單，在運維和處理線上資料問題時候可能比較常用，在開發過程中基本上都只會使用最基礎的操作。

DELETE語句

最基礎的用法不再贅述。說一下當使用DELETE刪除多張表的資料而不止是簡單的where條件時候，寫法是這樣的：

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;
複製程式碼

注意是DELETE後接著兩個表名。另外如果用了alias，刪除語句也必須用alias不能再用表名：

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;   -- 正確
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;   -- 錯誤
複製程式碼

一定要提醒的是，DELETE和UPDATE一定記得加限定條件，否則很容易就可能釀成一樁慘案，因為資料庫需要保證MVCC（Multiversion concurrency control，是事務的基礎），這類更新操作的開銷很大甚至需要鎖表。筆者自己線上上資料庫做這些操作包括SELECT時候手已經都已經形成了下意識，一定會先寫下where條件，Ctrl-A再開始寫update,select。

TRUNCATE TABLE

相對剛才說的DELETE操作很慢，如果寫單元測試和自己用的開發庫需要快速的清空一個表時候要怎麼做呢？答案是使用TRUNCATE。

注意使用TRUNCATE時候如果表有設定自增列，比如自增的主鍵，是會被清空到初始值的，也就是TRUNCATE之後整張表的資料沒了，自增的序列也回到了原始的值。

而DELETE FROM t;這樣刪掉整張表資料的DELETE語句是不會清空自增值的。另外如果有外來鍵關聯，那麼就需要使用set foreign_key_checks來去掉外來鍵檢查：

set foreign_key_checks = 0;
truncate Account;
set foreign_key_checks = 1;
複製程式碼

這個特性還引起一個筆者開發時候碰到的很有意思的異常：就是當這樣清空了某張表t的主鍵後，與其有關聯關係的表a沒有清空，隨後在t中新插入資料，自增ID重新從1開始增加。結果a中的一些舊資料結果就跟t中的新資料關聯上了。哈哈哈，當時著實是覺得是不是鬧鬼了，查了挺久才發現。

UPDATE語句

最基礎的用法不再贅述。看下面例子：

UPDATE t1 SET yourname = realname + ".avi";
複製程式碼

設定的值其實是一個表示式，其實可以這樣用別的列表，也可以同時更新兩個表，也就是用上JOIN：

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
複製程式碼

其實和在select中直接select兩個表不用寫join一樣，這其實也是join，只是是inner join的一種簡寫。那麼更新還可以更新自身列：

UPDATE t1 SET counting = counting + 1 where id = 2;
複製程式碼

列值這樣自增是實際開發中一個很常用的技巧，用在計數統計時候非常方便，而且也免得需要自己去面對先讀後寫在併發衝突時候引起的值覆蓋問題。然後還可以加order by和limit來做只更新符合條件的前幾個：

UPDATE t SET counting = counting + 1 ORDER BY id DESC LIMIT 10;
複製程式碼

同樣上面這個自增自身的例子，有些特殊的情況很有意思，即當要更新的主鍵自增id。那麼如果不加order by直接更新的話：

UPDATE t SET id = id + 1;   -- 這是錯誤的
複製程式碼

會報id衝突的錯誤，無法執行。增加了order by就可以順利執行了：

UPDATE t SET id = id + 1 ORDER BY id DESC;
複製程式碼

一定要提醒的是，DELETE和UPDATE一定記得加限定條件，否則很容易就可能釀成一樁慘案，因為資料庫需要保證MVCC（Multiversion concurrency control，是事務的基礎），這類更新操作的開銷很大，而且會鎖表，當鎖表的時候你的其他請求就被卡住了。筆者自己線上上資料庫做這些操作包括SELECT時候手已經都已經形成了下意識，一定會先寫下where條件，Ctrl-A跳到命令列開頭再開始寫update,select。

相關文章