您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 流程控制函數實戰：CASE WHEN 與 IFNULL/COALESCE 深度對比

編輯：MySQL 綜合教學

MySQL 流程控制函數實戰：CASE WHEN 與 IFNULL/COALESCE 深度對比

SQL 中的條件判斷是業務邏輯下推數據庫層的關鍵。MySQL 提供 CASE WHEN、IF()、IFNULL()、COALESCE()、NULLIF() 多種函數，本文通過實戰 UPDATE/SELECT 場景深度對比語法、語義、性能與適用邊界。

一、 CASE WHEN：通用條件分支

1. 簡單 CASE（值比較）

CASE col
    WHEN val1 THEN result1
    WHEN val2 THEN result2
    ELSE default_result
END

2. 搜索 CASE（布爾表達式）— 最常用

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

3. 實戰 UPDATE 場景：動態計算利潤

UPDATE t_tradefee 
SET margin_profit = CASE 
    WHEN market_price IS NULL THEN (2.3 - basic_fee) * remain_amount
    ELSE (2.3 - market_price) * remain_amount + margin_profit
END,
market_price = 2.3
WHERE SUBSTRING(cardno, 1, 3) = '001' 
  AND trade_type = 1;

語義：若無市場價（NULL），按基礎費率計算；否則按市場價調整並累加原利潤。

優勢：支持多條件、複雜表達式、嵌套、返回任意類型。

二、 IFNULL / COALESCE：空值處理專家

1. IFNULL(expr1, expr2) — 兩參數，MySQL 專有

expr1 NOT NULL → 返回 expr1
expr1 IS NULL → 返回 expr2

SELECT 
    DAY(op_time) AS day,
    SUM(price) AS price,
    IFNULL((
        SELECT SUM(price) 
        FROM t_margin_profit 
        WHERE op_time < '2016-01-01 00:00:00'
    ), 0) AS old_total
FROM t_margin_profit
WHERE YEAR(op_time) = 2016 AND MONTH(op_time) = 1
GROUP BY DAY(op_time);

場景：子查詢可能返回 NULL（無歷史數據），展示時轉為 0。

2. COALESCE(expr1, expr2, ..., exprN) — 多參數，標準 SQL

返回第一個非 NULL 參數
所有參數 NULL → 返回 NULL

-- 取第一個非空聯系方式
SELECT COALESCE(mobile, email, wechat, '未填寫') AS contact FROM users;

-- 多表關聯取優先非空字段
SELECT COALESCE(t1.name, t2.name, t3.name, '匿名') AS display_name
FROM main_table m
LEFT JOIN table1 t1 ON m.id = t1.mid
LEFT JOIN table2 t2 ON m.id = t2.mid
LEFT JOIN table3 t3 ON m.id = t3.mid;

3. IFNULL vs COALESCE 核心差異

維度	IFNULL	COALESCE
參數個數	固定 2 個	1-N 個
標準兼容	MySQL 專有	ANSI SQL 標準
類型推導	按 expr1/expr2 推導	按所有參數最高優先級推導
短路求值	支持	支持
性能	極微弱優勢（固定參數）	通用

💡 選型建議：兩參數用 IFNULL（簡潔）；多參數/跨庫遷移用 COALESCE。

三、進階函數：IF / NULLIF

1. IF(condition, true_val, false_val) — 三元運算符

SELECT IF(score >= 60, '及格', '不及格') FROM exams;
-- 等價於 CASE WHEN score >= 60 THEN '及格' ELSE '不及格' END

⚠️ 注意：IF() 僅支持三參數，條件結果必須為標量。

2. NULLIF(expr1, expr2) — 相等則 NULL

-- 避免除零錯誤
SELECT 100 / NULLIF(divisor, 0) FROM calc;

-- 空字符串轉 NULL
SELECT NULLIF(trim(name), '') FROM users;

四、綜合實戰：複雜業務邏輯下推

需求：訂單金額計算 — 會員折扣、滿減、優惠券疊加規則

SELECT 
    o.id,
    o.original_amount,
    -- 會員等級折扣
    CASE u.vip_level
        WHEN 1 THEN 0.95
        WHEN 2 THEN 0.90
        WHEN 3 THEN 0.85
        ELSE 1.00
    END AS vip_discount,
    -- 滿減：每滿 100 減 10
    FLOOR(o.original_amount / 100) * 10 AS full_reduction,
    -- 優惠券（取最大面額）
    COALESCE((
        SELECT MAX(coupon_value) 
        FROM user_coupons uc 
        WHERE uc.user_id = o.user_id 
          AND uc.status = 1 
          AND uc.expire_time > NOW()
    ), 0) AS coupon_value,
    -- 實付金額
    ROUND(
        o.original_amount * 
        CASE u.vip_level WHEN 1 THEN 0.95 WHEN 2 THEN 0.90 WHEN 3 THEN 0.85 ELSE 1.00 END
        - FLOOR(o.original_amount / 100) * 10
        - COALESCE((SELECT MAX(coupon_value) FROM user_coupons uc WHERE uc.user_id = o.user_id AND uc.status = 1 AND uc.expire_time > NOW()), 0)
    , 2) AS pay_amount
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 1;