關於 MySQL 你可能不知道的 SQL 使用技巧

02-04

近來處理了比較多的資料庫維護工作，對 SQL 的語法也算有了更深層次的認識，也學到了很多以前沒有用過的 SQL 語法技巧，這裡統一整理一下，希望對讀者也有所啟發。

本文將主要介紹一些我認為有用的 SQL 語法和技巧，並通過適當案例說明，但案例本身做了簡化處理，只希望通過案例讓讀者更好的理解。

使用 UNION | UNION ALL 語法

UNION 用於合併多個查詢的結果集，我目前遇到的主要有如下兩個場景用起來比較有效：

1. 同表的複雜查詢，很難通過一個 SELECT 語句搞定

2. 多表查詢，但返回的數據一致，常見一些聚合數據統計需求

UNION 也可以加 limit、order by 子句，用於對 UNION 後的結果集進行排序和過濾。

通過 UNION 的這些特性，我們可以把原本需要編寫代碼才能處理的一些工作交給資料庫，同時還減少 SQL 數，提高性能。

比如說，下面這個例子：

select product as type, count(*) as count from `products`
union
select comment as type, count(*) as count from `comments`
order by count;

我們通過 UNION 語法同時查詢了 products 表和 comments 表的總記錄數，並且按照 count 排序。

結合 UPDATE | DELETE 與 JOIN

一直以來，我們得益於聯合查詢（SELECT + JOIN）給我們帶來的便利，但無形之中確形成了思維定勢（至少對於我而言是這樣的），殊不知 UPDATE DELETE 也能與 JOIN 聯合使用，從而簡化 SQL 編寫。

在使用 UPDATE | DELETE + JOIN 之前，我們可能做法要麼是先查詢出待刪除記錄的 ID 然後再根據 ID 進行刪除，要麼是使用 IN 子查詢。前者需要寫兩個 SQL 語句，在程序中處理邏輯，後者有時並不能正常工作。

就後者而言，應該有人遇到過這樣的錯誤：

ERROR 1093 (HY000): You cant specify target table xxx for update in FROM clause

這樣的錯誤產生的原因是：MySQL 不支持同一個 SQL 語句嘗試對同一個表進行查詢和修改兩個操作。

比如，刪除沒有評論的文章這條語句

delete from articles
where id in (
select a.id from articles as a left join comments as c on a.id=c.article_id
where c.is is NULL
)

articles 表既被查詢，也被更新，將出現上面的錯誤。

但是，如果 DELETE 結合 JOIN，則可以直接寫出這樣的 SQL 語句，簡潔許多：

delete s from articles as a

left join comments as c on a.id=c.article_id
where c.is is NULL

當然，UPDATE 也是同理：

update articles as a
left join comments as c on a.id=c.article_id
set a.deleted=1
where c.is is NULL

CASE 語法

CASE 語法可以在 SQL 內做簡單的分支判斷，根據不同的條件返回不同的值。比如考慮這樣的需求：

一個商品有多個訂單，訂單有已付款和未付款兩個狀態，現在給定一個商品列表，返回每個商品已付款和未付款訂單的數量。

這個時候我們可以通過 CASE 語句和 GROUP BY 通過一條 SQL 實現：

select
product_id,

count(
case is_paid
when 1 then 1
else null
end
) as total_paid,
count(
case is_paid
when 0 then 1
else null

end
) as total_not_paid
from orders
where product_id in (1, 2, 3, 4)
group by product_id;

配合 ORM 庫，這樣的寫法可以幫助我們實現 eager loading，避免 n + 1 查詢。

因為這個場景比較簡單，我們也可以使用 MySQL 提供的流程式控制制函數(Control Flow Functions) 使得該 SQL 更簡潔：

select
product_id,
count(if(is_paid = 1, 1, null)) as total_paid,
count(if(is_paid = 0, 1, null)) as total_not_paid

from orders
where product_id in (1, 2, 3, 4)
group by product_id;

使用 INSERT INTO ... SELECT 語法

通過 INSERT INTO ... SELECT 語法，我們可以把 SELECT 的結果集直接寫入另一張表中，而不需要程序處理。通過這個語法，外加一些變通，我們可以很方便的實現更多的需求場景。

比如說，我們要給所有購買了某一商品的用戶發放一張元價值10元的優惠券，我們可以這樣寫：

insert into tickets (user_id, price, expires_in)
select
user_id, 10 as price, 2017-09-09 as expires_in
from orders
where product_id=123 and is_paid=1;

又比如說，在選課的場景中，我們要給一批人分配一批課，假設要給1班的人分配體育課和美術課，我們可以通過該語法加 CROSS JOIN 實現：

insert into class_members (class_id, user_id, status)

select
c.id as class_id,
u.id as user_id,
1 as status
from classes as c cross join users as u
where c.name in (體育課, 美術課) and u.class_name=1班;

本文就這些了，你還知道有哪些技巧呢？

--------------------------------

本文首發於「代碼寫詩」微信公眾號: https://mp.weixin.qq.com/s?__biz=MzI4MjAwMTk0OA==&amp;mid=2247483676&amp;idx=1&amp;sn=bc5c47f541f884e5686d2923430e5078