MySQL中的case when中对于NULL值判断的小坑
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，所以出现查询结果和实际不匹配的情况；