MySQL中的case when then end中对于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后面的条件，如果满足则执行。

 

使用场景1：

对每一个用户ID进行奇偶数显示：

SELECT  employee_id , case employee_id when employee_id%2=1 then "奇数编号" else "偶数编号" end  "奇偶编号"   FROM employees;

 

使用场景2：

判断某一字段是否为空，如果为空的话则输出汉字为空，不为空输出不为空。

SELECT
	 (case commission_pct WHEN commission_pct is null THEN "commission_pct为null" else "commission_pct不为null" end)  ,commission_pct,
	department_id
FROM
	employees;

为什么会出现这个错误呢？是将第一种语法与第二种语法混用导致的，case 后面commission_pct 的值有两种：真实值或者为null，而 when 后面的commission_pct is null 也有两个值：true或者false，所以case 后面为null时候永远无法跟true或false匹配，因此输出不为null。

对于该种情况如果必须要用语法1的话可以如下改写：

SELECT  (case  commission_pct IS NULL  WHEN true THEN "lala" else commission_pct end) AS "new_col" ,   commission_pct 
from  employees;

也可以使用语法2写：

SELECT  (case  WHEN commission_pct IS NULL THEN "commission_pct为null" else "commission_pct不为null" end) AS "是否为空" ,   commission_pct 
from  employees;

 

注意另一种可能存在错误却不容易发现错误的情况：

SELECT
	CONCAT((case commission_pct  WHEN   null then "lala" else commission_pct end), '88' ) ,commission_pct,
	department_id
FROM
	employees;

看似没有问题，实际有问题，问题原因就是null的判断不能用=进行判断。简单说就是：语法1中的case表达式的值与后面的when的值使用的=进行判等，但是mysql中必须使用is 或者is not。

 

总结：

1：语法1是将case后面的表达式值计算好之后跟后面的when条件的值使用“=”进行判断相等，相等就进入该分支。

2：语法2是不需要case后面有表达式，直接评估when后面的条件值即可，如果为true则进入。

 

参考文档：https://dev.mysql.com/doc/refman/5.7/en/case.html

补充：官方文档永远是解决问题的王道！！！

拓展：如果只针对null值进行替换的话可以使用ifnull函数：

SELECT 
	IFNULL(commission_pct,"commission_pct为null") ,commission_pct,department_id
FROM
	employees;