MySQL中case when對NULL值判斷的踩坑記錄

前言

今天在開發程序中,從MySQL中提取數據的時候,使用到瞭case when的語法用來做判斷,在使用過程中在判斷NULL值的時候遇到個小問題

sql中的case when 有點類似於Java中的switch語句,比較靈活,但是在Mysql中對於Null的處理有點特殊

Mysql中case when語法:

語法1:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

語法2:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

註意:  這兩種語法是有區別的,區別如下:

1:第一種語法:case_value必須是一個表達式,例如 userid%2=1或者username is null等。該種語法不能用於測試NULL。

2:第二種語法CASE後面不需要變量或者表達式,直接執行時候評估每一個WHEN後面的條件,如果滿足則執行。

案例實戰:

表結構如下:a 值為null, b值為1

mysql> SELECT NULL AS a, 1 AS b;
+------+---+
| a    | b |
+------+---+
| NULL | 1 |
+------+---+

現在實現,如果a值為null 則取b值,否則取a值

方法1: ifnull 用法

SELECT
    IFNULL(a, b) AS new,
    a,
    b
FROM
    -- 創建臨時表: a 的值為null ,b為1
    (SELECT NULL AS a, 1 AS b) tmp;

方法2: case when 用法

SELECT
    (
        CASE a
        WHEN a IS NULL THEN
            b
        ELSE
            a
        END
    ) AS new,
    a,
    b
FROM
    (SELECT NULL AS a, 1 AS b) tmp;

發現得到的結果不對,new 的值居然為null ,而不是我們想要的1.

為什麼會出現這個錯誤呢?是將第一種語法與第二種語法混用導致的,case 後面commission_pct 的值有兩種:真實值或者為null,而 when 後面的commission_pct is null 也有兩個值:true或者false,所以case 後面為null時候永遠無法跟true或false匹配,因此輸出不為null。

對於該種情況如果必須要用語法1的話可以如下改寫:

SELECT
    (
        CASE a IS NULL
        WHEN TRUE THEN b
        ELSE a         
        END
    ) AS new,
    a,
    b
FROM
    (SELECT NULL AS a, 1 AS b) tmp;

也可以使用語法2寫:

SELECT
    (
        CASE
        WHEN a is NULL  THEN b 
        ELSE a 
        END
    ) AS new,
    a,
    b
FROM
    (SELECT NULL AS a, 1 AS b) tmp;

註意另一種可能存在錯誤卻不容易發現錯誤的情況:

SELECT
    (
        CASE a
        WHEN NULL THEN b
        ELSE a         
        END
    ) AS new,
    a,
    b
FROM
    (SELECT NULL AS a, 1 AS b) tmp;

看似沒有問題,實際有問題,問題原因就是null的判斷不能用=進行判斷。簡單說就是:語法1中的case表達式的值與後面的when的值使用的=進行判等,但是mysql中必須使用is 或者is not。

總結:

1:語法1是將case後面的表達式值計算好之後跟後面的when條件的值使用“=”進行判斷相等,相等就進入該分支。

2:語法2是不需要case後面有表達式,直接評估when後面的條件值即可,如果為true則進入。

到此這篇關於MySQL中case when對NULL值判斷的文章就介紹到這瞭,更多相關MySQL case when對NULL值判斷內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: