MySQL中case?when對(duì)NULL值判斷的踩坑記錄

更新時(shí)間：2021年12月14日 12:54:01 作者：追風(fēng)的浪子

最近在學(xué)習(xí)Hive基礎(chǔ)知識(shí)時(shí),遇到了遇到了Case?When?Else?End語(yǔ)法,這篇文章主要給大家介紹了關(guān)于MySQL中case?when對(duì)NULL值判斷的踩坑記錄,需要的朋友可以參考下

前言

今天在開(kāi)發(fā)程序中，從MySQL中提取數(shù)據(jù)的時(shí)候，使用到了case when的語(yǔ)法用來(lái)做判斷，在使用過(guò)程中在判斷NULL值的時(shí)候遇到個(gè)小問(wèn)題

sql中的case when 有點(diǎn)類似于Java中的switch語(yǔ)句，比較靈活，但是在Mysql中對(duì)于Null的處理有點(diǎn)特殊

Mysql中case when語(yǔ)法：

語(yǔ)法1：

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

語(yǔ)法2：

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

注意:? 這兩種語(yǔ)法是有區(qū)別的，區(qū)別如下：

1：第一種語(yǔ)法：case_value必須是一個(gè)表達(dá)式，例如 userid%2=1或者username is null等。該種語(yǔ)法不能用于測(cè)試NULL。

2：第二種語(yǔ)法CASE后面不需要變量或者表達(dá)式，直接執(zhí)行時(shí)候評(píng)估每一個(gè)WHEN后面的條件，如果滿足則執(zhí)行。

案例實(shí)戰(zhàn)：

表結(jié)構(gòu)如下：a 值為null, b值為1

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

現(xiàn)在實(shí)現(xiàn)，如果a值為null 則取b值，否則取a值

方法1： ifnull 用法

SELECT
    IFNULL(a, b) AS new,
    a,
    b
FROM
    -- 創(chuàng)建臨時(shí)表: 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;

發(fā)現(xiàn)得到的結(jié)果不對(duì)，new 的值居然為null ,而不是我們想要的1.

為什么會(huì)出現(xiàn)這個(gè)錯(cuò)誤呢？是將第一種語(yǔ)法與第二種語(yǔ)法混用導(dǎo)致的，case 后面commission_pct 的值有兩種：真實(shí)值或者為null，而 when 后面的commission_pct is null 也有兩個(gè)值：true或者false，所以case 后面為null時(shí)候永遠(yuǎn)無(wú)法跟true或false匹配，因此輸出不為null。

對(duì)于該種情況如果必須要用語(yǔ)法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;

也可以使用語(yǔ)法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;

注意另一種可能存在錯(cuò)誤卻不容易發(fā)現(xiàn)錯(cuò)誤的情況：

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

看似沒(méi)有問(wèn)題，實(shí)際有問(wèn)題，問(wèn)題原因就是null的判斷不能用=進(jìn)行判斷。簡(jiǎn)單說(shuō)就是：語(yǔ)法1中的case表達(dá)式的值與后面的when的值使用的=進(jìn)行判等，但是mysql中必須使用is 或者is not。

總結(jié)：

1：語(yǔ)法1是將case后面的表達(dá)式值計(jì)算好之后跟后面的when條件的值使用“=”進(jìn)行判斷相等，相等就進(jìn)入該分支。

2：語(yǔ)法2是不需要case后面有表達(dá)式，直接評(píng)估when后面的條件值即可，如果為true則進(jìn)入。

到此這篇關(guān)于MySQL中case?when對(duì)NULL值判斷的文章就介紹到這了,更多相關(guān)MySQL?case?when對(duì)NULL值判斷內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: