暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

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

DBA的辛酸事儿 2021-10-14
1140

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

            文章转载自DBA的辛酸事儿,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

            评论