MySQL ifnull函数判断字段值为null时使用默认值 |
您所在的位置：网站首页 › 悦然凹凸世界头像高清 › MySQL ifnull函数判断字段值为null时使用默认值 |
|
在使用left join等联表查询时，常遇到某些字段为null，一般都在后台语言使用if a==null 判断做处理，其实MySQL本身也有一个ifnull函数可以处理。
假设有两张表 name表： mysql> select * from name; +------+-----------+ | id | user | +------+-----------+ | 1 | xiaoqiang | | 2 | lisi | | 3 | xiaowu | | 4 | laoliu | +------+-----------+ 4 rows in set (0.00 sec)phone表： mysql> select * from phone; +------+-------+ | id | phone | +------+-------+ | 2 | 110 | | 3 | 119 | +------+-------+ 2 rows in set (0.00 sec)联表查询结果： mysql> select a.*,b.phone from name a left join phone b on a.id=b.id; +------+-----------+-------+ | id | user | phone | +------+-----------+-------+ | 1 | xiaoqiang | NULL | | 2 | lisi | 110 | | 3 | xiaowu | 119 | | 4 | laoliu | NULL | +------+-----------+-------+ 4 rows in set (0.01 sec)其中id=1、id=4两行的phone值为null，我们可以使用ifnull函数判断它为null时给一个默认值： mysql> select a.*,ifnull(b.phone,0) from name a left join phone b on a.id=b.id; +------+-----------+-------------------+ | id | user | ifnull(b.phone,0) | +------+-----------+-------------------+ | 1 | xiaoqiang | 0 | | 2 | lisi | 110 | | 3 | xiaowu | 119 | | 4 | laoliu | 0 | +------+-----------+-------------------+ 4 rows in set (0.00 sec)甚至也可以指定某一个字段值来填补 mysql> select a.*,ifnull(b.phone,a.id) from name a left join phone b on a.id=b.id; +------+-----------+----------------------+ | id | user | ifnull(b.phone,a.id) | +------+-----------+----------------------+ | 1 | xiaoqiang | 1 | | 2 | lisi | 110 | | 3 | xiaowu | 119 | | 4 | laoliu | 4 | +------+-----------+----------------------+ 4 rows in set (0.00 sec)当b.phone的值为null时，使用name表中的id字段来填补。但使用ifnull后列名看起来很不友好，可以用as还原 mysql> select a.*,ifnull(b.phone,a.id) as phone from name a left join phone b on a.id=b.id; +------+-----------+-------+ | id | user | phone | +------+-----------+-------+ | 1 | xiaoqiang | 1 | | 2 | lisi | 110 | | 3 | xiaowu | 119 | | 4 | laoliu | 4 | +------+-----------+-------+ 4 rows in set (0.00 sec) |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |