今天在开发程序中,从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,所以出现查询结果和实际不匹配的情况;