mysql COALESCE 取代null值好方法

2018-07-19 135 words One minute

Contents

以前前公司有用 DB2 的 VALUE()，後來換到 MySQL 時一度很想找類似語法，最後才重新認真記住 COALESCE()。這個函式真的很常用，尤其在報表、查詢結果格式化、欄位預設值處理時很好用。

COALESCE 是做什麼的

COALESCE() 的用途很單純，就是從左到右找第一個不是 NULL 的值回傳。

例如：

1

select coalesce(null, null, 'A', 'B');

結果會是：

1

'A'

1
2


select coalesce(nickname, '未設定') as nickname
from user_profile;

如果 nickname 是 NULL，就會顯示 未設定。

1
2


select price * coalesce(quantity, 0) as total
from order_item;

如果 quantity 為 NULL，至少不會整個運算結果也變 NULL。

1
2


select coalesce(cell_phone, home_phone, office_phone) as contact_phone
from customer;

這種在整理聯絡資訊時很好用。

我當初會想到這個，是因為 DB2 的 VALUE(xxx, ooo) 用法很像：

1

value(xxx, ooo)

如果 xxx 是 NULL，就回傳後面的值。

而 COALESCE(xxx, ooo, zzz, fff, ...) 其實就是更通用的版本，可以一路往右找下去。所以如果你以前習慣 DB2 VALUE()，在 MySQL 記 COALESCE() 就很夠用了。

我通常會把 COALESCE() 用在查詢結果整理，不太會濫用在過度複雜的條件判斷裡。原因很簡單，太多層 COALESCE() 疊起來會讓 SQL 變得難讀。

如果只是單純要補預設值，它非常好用；但如果邏輯開始牽涉多種商業規則，還是建議拆清楚。