比CRUD多一點兒（三）：UPDATE、DELETE
這是MySQL系列筆記的第一部分，本系列筆記希望能按照筆者自己學習MySQL技術的經歷來記錄，避免純粹按照內容一塊一塊總結，也就是不同於一般按內容分配章節的書籍的結構，會有一個平滑的閱讀曲線。內容比較豐富的技術點會按照專題在多個學習筆記中逐漸深入。
這部分的標題叫比CRUD多一丁點兒，比起最基礎的w3c的SQL教程之外，只多一點的擴展，滿足應付從純粹閱讀入門資料到可以上手完成一個簡單的工作的需求。
第三篇的主要內容繼續是基礎SQL語句UPDATE和DELETE，這兩個相對比較簡單，在運維和處理線上數據問題時候可能比較常用，在開發過程中基本上都只會使用最基礎的操作。
DELETE語句
最基礎的用法不再贅述。說一下當使用DELETE刪除的數據需要用一個條件關聯來查找而不止是簡單的where條件時候，寫法是這樣的：
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3WHERE t1.id=t2.id AND t2.id=t3.id;
注意如果用了alias，刪除語句也必須用alias不能再用表名：
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2WHERE a1.id=a2.id; -- 正確DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2WHERE 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.priceWHERE 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。
推薦閱讀：