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

MySQL 流程控制函數完全指南：CASE、IF、IFNULL、NULLIF 實戰用法與類型推導規則

編輯：MySQL 綜合教學

四大流程控制函數對比

函數	用途	語法形式	返回類型規則
`CASE`	多條件分支，類似 switch/if-else	簡單/搜索兩種形式	所有結果類型的兼容聚合類型
`IF()`	單條件二元選擇	IF(cond, true_val, false_val)	true_val/false_val 的兼容類型
`IFNULL()`	NULL 替換為指定值	IFNULL(expr1, expr2)	expr1/expr2 兼容類型，優先 STRING > REAL > INTEGER
`NULLIF()`	相等返回 NULL，否則返回第一參數	NULLIF(expr1, expr2)	同 expr1 類型

CASE 表達式詳解

形式 1：簡單 CASE（值比較）

CASE case_value
    WHEN when_value1 THEN result1
    WHEN when_value2 THEN result2
    ...
    [ELSE else_result]
END

SELECT CASE 2 
    WHEN 1 THEN 'one'
    WHEN 2 THEN 'two'
    ELSE 'other'
END; -- 'two'

-- 字符串比較受排序規則影響
SELECT CASE BINARY 'B'
    WHEN 'a' THEN 1
    WHEN 'b' THEN 2
    ELSE 0
END; -- 0 (BINARY 區分大小寫，'B' != 'b')

形式 2：搜索 CASE（條件表達式）

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE else_result]
END

SELECT CASE 
    WHEN 1 > 0 THEN 'positive'
    WHEN 1 < 0 THEN 'negative'
    ELSE 'zero'
END; -- 'positive'

-- 在查詢中使用
SELECT 
    name,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 60 THEN 'C'
        ELSE 'D'
    END AS grade
FROM students;

返回類型推導規則

上下文決定：字符串上下文 → 字符串；數值上下文 → 十進制/實數/整數
所有 THEN/ELSE 結果類型的「兼容聚合類型」：

結果類型集合	聚合返回類型
全數值	最高精度數值類型 (BIGINT > DOUBLE > DECIMAL > INT...)
含字符串	VARCHAR / TEXT / BLOB
含時間/日期	DATETIME / TIMESTAMP / DATE / TIME
混合數值+字符串	DOUBLE (隱式轉換)

IF() 函數深度解析

語法與語義

IF(expr1, expr2, expr3)
-- expr1 為 TRUE (非 0 且非 NULL) 返回 expr2，否則返回 expr3

關鍵陷阱：數值語境下的隱式轉換

-- 陷阱：浮點數作為條件
SELECT IF(0.1, 'true', 'false');  -- 'false' !!!
-- 原因：0.1 轉為整數 0 => FALSE

-- 正確：顯式比較
SELECT IF(0.1 <> 0, 'true', 'false'); -- 'true'

-- 字符串條件
SELECT IF('non-empty', 1, 0); -- 1 (非空字符串為 TRUE)
SELECT IF('', 1, 0);          -- 0 (空字符串為 FALSE)

返回類型推導（存儲到表/臨時表時關鍵）

expr2 類型	expr3 類型	IF() 返回類型
字符串	任意	字符串
浮點數	任意	浮點數
整數	整數	整數
整數	浮點數	浮點數
日期/時間	日期/時間	日期/時間

若 expr2/expr3 任一為區分大小寫字符串，結果也區分大小寫。

IFNULL() 與 NULLIF()

IFNULL(expr1, expr2)

SELECT IFNULL(NULL, 'default');    -- 'default'
SELECT IFNULL(1/0, 'error');       -- 'error' (1/0 產生 NULL)
SELECT IFNULL(col, 0) FROM t;      -- NULL 替換為 0

-- 類型推導：取兩者「更通用」類型，順序 STRING > REAL > INTEGER
CREATE TABLE tmp SELECT IFNULL(1, 'test') AS c; -- c 類型為 CHAR(4)

NULLIF(expr1, expr2)

SELECT NULLIF(1, 1);  -- NULL
SELECT NULLIF(1, 2);  -- 1
SELECT NULLIF('abc', 'abc'); -- NULL

-- 等價於
CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

-- 注意：參數不相等時，MySQL 會計算兩次 expr1（可能觸發副作用函數）

實戰組合模式

1. COALESCE 替代嵌套 IFNULL

-- 返回第一個非 NULL 值
SELECT COALESCE(phone, mobile, email, 'no-contact') FROM users;

-- 等價於
SELECT IFNULL(phone, IFNULL(mobile, IFNULL(email, 'no-contact')));

2. NULLIF 防止除零錯誤

SELECT 100 / NULLIF(total, 0) AS avg_price; -- total=0 時返回 NULL 而非報錯

3. CASE 實現複雜業務規則

SELECT 
    order_id,
    CASE 
        WHEN amount >= 1000 AND member_level = 'VIP' THEN amount * 0.8
        WHEN amount >= 500 THEN amount * 0.9
        WHEN coupon_code IS NOT NULL THEN amount - 50
        ELSE amount
    END AS final_amount
FROM orders;

4. 動態排序

SET @sort_col = 'price', @sort_dir = 'DESC';
SELECT * FROM products
ORDER BY 
    CASE WHEN @sort_col = 'price' AND @sort_dir = 'ASC' THEN price END ASC,
    CASE WHEN @sort_col = 'price' AND @sort_dir = 'DESC' THEN price END DESC,
    CASE WHEN @sort_col = 'name' AND @sort_dir = 'ASC' THEN name END ASC,
    CASE WHEN @sort_col = 'name' AND @sort_dir = 'DESC' THEN name END DESC;

5. 條件聚合（單掃描多統計）

SELECT 
    COUNT(*) AS total,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_cnt,
    SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refund_amt,
    AVG(CASE WHEN channel = 'app' THEN score END) AS app_avg_score
FROM orders;