MySQL SELECT 查詢中使用 IFNULL/COALESCE 為 NULL 列設置默認顯示值

場景

左連接查詢時，右表無匹配行導致目標列為 NULL，前端顯示「null」或報錯，需在 SQL 層統一轉為默認值（如 0、空串、占位符）。

原始查詢（無默認值）

SELECT 
  ui.id, ui.user_name, ui.role, ui.adm_regionid,
  r.region_name,
  ui.create_time
FROM userinfo ui
LEFT JOIN region r ON ui.adm_regionid = r.id;

結果片段：

id | user_name | role | adm_regionid | region_name | create_time
---|-----------|------|--------------|-------------|------------
1  | alice     | 1    | 100          | 北京市       | 2023-01-01
2  | bob       | 2    | 999          | NULL        | 2023-01-02

方案一：IFNULL(expr, default) —— MySQL 專用，兩參數

SELECT 
  ui.id, ui.user_name, ui.role, ui.adm_regionid,
  IFNULL(r.region_name, '未知區域') AS region_name,
  ui.create_time
FROM userinfo ui
LEFT JOIN region r ON ui.adm_regionid = r.id;

結果：

... | region_name
... | 北京市
... | 未知區域

方案二：COALESCE(val1, val2, ...) —— 標準 SQL，多參數，返回首個非 NULL

SELECT 
  ui.id,
  COALESCE(ui.nickname, ui.user_name, '匿名用戶') AS display_name,
  COALESCE(r.region_name, r.region_code, 'N/A') AS region
FROM userinfo ui
LEFT JOIN region r ON ui.adm_regionid = r.id;

優勢：可鏈式備選，如「暱稱 → 用戶名 → 匿名用戶」。

方案三：CASE WHEN —— 複雜邏輯默認值

SELECT 
  ui.id,
  CASE 
    WHEN r.region_name IS NOT NULL THEN r.region_name
    WHEN ui.adm_regionid IS NOT NULL THEN CONCAT('區域ID:', ui.adm_regionid)
    ELSE '無區域信息'
  END AS region_name
FROM userinfo ui
LEFT JOIN region r ON ui.adm_regionid = r.id;

性能與類型注意點

1. 類型一致性：IFNULL(col, 0) 若 col 是 VARCHAR，會隱式轉換導致索引失效，建議 IFNULL(col, '') 或 CAST(0 AS CHAR)。
2. 索引影響：WHERE IFNULL(col, '') = '' 不走索引，改用 WHERE col IS NULL OR col = ''。
3. 執行計劃：EXPLAIN 確認 ref/range 未退化為 ALL。

擴展：聚合函數中的 NULL 處理

-- COUNT(*) 計算行數（含 NULL）
-- COUNT(col) 計算非 NULL 個數
-- SUM/AVG/MIN/MAX 自動忽略 NULL
SELECT 
  COUNT(*) AS total_rows,
  COUNT(region_name) AS not_null_regions,
  COALESCE(SUM(sales), 0) AS total_sales
FROM orders;

總結選型表