MySQL 替换查询结果中的null或特定字符

我就是我 2022-04-13 14:45 612阅读 0赞

偶尔会遇到有人问，表里某个列存在null值，查询结果集里想要把null替换成特定的字符，怎么做？  
看到有人写了各种各样比较复杂的SQL，其实在MySQL里只要一个函数就能搞定，那就是ifnull，长话短说，看例子：

--将结果集中的null替换成'空'
    mysql> select id from test;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    | NULL |
    +------+
    3 rows in set (0.00 sec)
    
    mysql> select ifnull(id,'空') from test;
    +------------------+
    | ifnull(id,'空')  |
    +------------------+
    | 1                |
    | 2                |
    | 空               |
    +------------------+
    3 rows in set (0.00 sec)

那么，如果想要替换并不是null，而是特定的字符呢，例如1替换成’man’，这时，可以用case when语法实现，看例子：

--将结果集中的1替换成‘男’
    mysql> select * from test;
    +------+------+
    | id   | id2  |
    +------+------+
    |    1 |    1 |
    |    2 |    2 |
    | NULL |    3 |
    +------+------+
    3 rows in set (0.00 sec)
    mysql> select case id
        -> when 1 then 'man'
        -> else id
        -> end id
        -> from test;
    +------+
    | id   |
    +------+
    | man   |
    | 2    |
    | NULL |
    +------+
    3 rows in set (0.00 sec)

那么，如果我要替换的并不单单是一个特定的字符，而是多个呢？例如1替换’man’，2替换成’women’。这时有两个办法，一个是仍然利用case when实现，另一个是建立一个字典表，通过关联查询来实现替换。看例子：  
1.case when

--将结果中的1替换成'man'，2替换成'women'
    mysql> select * from test;
    +------+------+
    | id   | id2  |
    +------+------+
    |    1 |    1 |
    |    2 |    2 |
    | NULL |    3 |
    +------+------+
    3 rows in set (0.00 sec)
    mysql> select case id
        -> when 1 then 'man'
        -> when 2 then 'women'
        -> else id
        -> end id
        -> from test;
    +-------+
    | id    |
    +-------+
    | man   |
    | women |
    | NULL  |
    +-------+
    3 rows in set (0.00 sec)

2.字典表

--建立字典表dict，指定字符对应关系
    mysql> select * from dict;
    +------+-------+
    | id   | sex   |
    +------+-------+
    |    1 | man   |
    |    2 | women |
    +------+-------+
    2 rows in set (0.00 sec)
    
    mysql> select id from test;
    +------+
    | id   |
    +------+
    |    1 |
    |    2 |
    | NULL |
    +------+
    3 rows in set (0.00 sec)
    
    --源表与字典表关联，查出对应关系，实现替换效果
    mysql> select a.id,b.sex from test a left join dict b on a.id=b.id;
    +------+-------+
    | id   | sex   |
    +------+-------+
    |    1 | man   |
    |    2 | women |
    | NULL | NULL  |
    +------+-------+
    3 rows in set (0.00 sec)

case when和字典表各有优点，case when编写简单明了，字典表方便修改替换对应关系。