MySQL中的case when中对于NULL值判断的坑学习心得分享

今天在开发程序中，从MySQL中提取数据的时候，使用到了case when的语法用来做判断，在使用过程中在判断NULL值得时候遇到个小问题；

具体的现象测试如下：

表结构如下：

CREATE TABLE `wjqtab1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入三条数据：
mysql>insert into wjqtab1 values(null,'wjq'),(null,''),(null,null);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

表中数据：

mysql>select * from wjqtab1;
+----+------+
| id | name |
+----+------+
|  1 | wjq  |
|  2 |      |
|  3 | NULL |
+----+------+
3 rows in set (0.00 sec)

说明：ID=2，name为空字符，ID=3,name为NULL

查询需求：如果name为空字符或NULL，输出不同的值，用TEST替换空字符，用PROD替换NULL

SQL语句如下：

mysql>>SELECT 
    ->     id,
    ->     CASE name
    ->         WHEN '' THEN 'TEST'
    ->         WHEN NULL THEN 'PROD'
    ->         ELSE name
    ->     END AS name
    -> FROM
    ->     wjqtab1;
+----+------+
| id | name |
+----+------+
|  1 | wjq  |
|  2 | TEST |
|  3 | NULL |
+----+------+
3 rows in set (0.00 sec)

发现这个结果是有问题的，理想的结果第3条记录为3 PROD ,但是却为空，说明这个判断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必须是一个表达式或字段名，例如 name或name is null等。

2：第二种语法CASE后面不需要变量或者表达式，直接执行时候评估每一个WHEN后面的条件，如果满足则执行。

那么针对上面的查询需求，我们就可以调整成语法2的语法格式：

mysql>SELECT 
    ->     id,
    ->     CASE 
    ->         WHEN name = '' THEN 'TEST'
    ->         WHEN name IS NULL THEN 'PROD'
    ->         ELSE name
    ->     END AS name
    -> FROM
    ->     wjqtab1;
+----+------+
| id | name |
+----+------+
|  1 | wjq  |
|  2 | TEST |
|  3 | PROD |
+----+------+
3 rows in set (0.00 sec)

除了最开始的SQL语句无法满足需求，下面我们再来看下面一个SQL语句，同样也存在问题，无法满足我的查询需求：

mysql>SELECT 
    ->     id,
    ->     CASE name
    ->         WHEN name = '' THEN 'TEST'
    ->         WHEN name IS NULL THEN 'PROD'
    ->         ELSE name
    ->     END AS name
    -> FROM
    ->     wjqtab1;
+----+------+
| id | name |
+----+------+
|  1 | TEST |
|  2 | PROD |
|  3 | NULL |
+----+------+
3 rows in set, 1 warning (0.00 sec)

mysql>show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'wjq' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

发现得到的结果完全都不对了；

为什么会出现这个错误呢？

主要是将第一种语法与第二种语法混用导致的，case 后面的case_value 的值有两种：真实值或者为null，而 when 后面的条件也有两个值：true或者false，所以出现查询结果和实际不匹配的情况；