偶尔会遇到有人问,表里某个列存在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编写简单明了,字典表方便修改替换对应关系。