• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL中的case when中对于NULL值判断的坑

MySQL SEian.G 3个月前 (10-13) 131次浏览 已收录 0个评论

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

MySQL中的case when中对于NULL值判断的坑


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL中的case when中对于NULL值判断的坑
喜欢 (2)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址