浅析mysql中查询使用 != 不等于会过滤掉null的情况及其原因分析和解决、IFNULL 函数用法及其使用需要注意的事项
一、问题背景及介绍
在写 SQL 条件语句时经常用到 不等于 !=
的筛选条件。此时要注意此条件会将字段为 Null
的数据也当做满足不等于的条件而将数据筛选掉。(也就是说会忽略过滤掉为 null 的数据,导致数据不准确)。
比如:表A
A1 | B1 |
---|---|
1 | 0 |
2 | 1 |
3 | Null |
执行如下查询:select * from A where B1 != 1,得到的结果如下:
A1 | B1 |
---|---|
1 | 0 |
第三列 B1为空的也是会筛选掉的。
二、解决方案
要查出第三列只需将 SQL 改为如下语句 即可。
SELECT * FROM A WHERE B1 != 1 OR B1 is Null
上面这种方法最通俗,网上也最多,但是我总是感觉效率太低。目前我使用的方法是:
SELECT * FROM A WHERE IFNULL(B1,'') != 1
开发中遇到的问题,在此做下记录,谨防下次入坑。
三、原因分析
为什么会这样呢?这还得从 mysql 的底层开始说起,因为 NULL 不是一个「值」,而是「没有值」。
「没有值」不满足「值不等于1」这个条件,怎么解决可以使用 ifnull() 方法,将 null 转为空字符串,或者这个字段做出判断 加上OR a is null。当然使用 ifnull 相率会更好,现在就是先让大家理解一下。
四、关于 IFNULL 方法 - mysql 中 ifnull() 方法的用法
一般我们在使用 ifnull() 方法的时候,都是类似下面的语句:
IFNULL(expr1,expr2)
如果 expr1 不是 NULL,IFNULL() 返回expr1,否则它返回expr2。IFNULL()返回一个数字或字符串值。
1、将查询到的结果中的null转化为指定的字符串
select ifnull(name,'no name') from person;
-- name为null时显示 no name
2、其实在where查询部分也可以使用 ifnull()
select * from person where ifnull(name,'no name')='no name';
-- name 为 no name 时,及 name 为 null 时的数据均返回
这种使用看起来有点笨,但是在一些应用中可以很好的减少代码量
3、IFNULL使用的注意事项
数据如下:
现在开始正题:
(1)IFNULL的作用是什么?下面一个简单的sql和结果说明,如果IFNULL(a,b),a接收的值为null,则返回b,否则返回a;
SELECT IFNULL(NULL,0); -- 0
(2)以下sql语句,大家可以预测下结果,按照IFNULL函数的作用,应该返回0才对,可是结果并不是这样。
SELECT IFNULL(score,0) FROM student WHERE ID = 4;
返回结果,居然是null,与预期的结果0不一致。
(3)以下语句返回正确结果0;
SELECT IFNULL((SELECT score FROM student WHERE ID = 4),0);
总结:使用 2 方式使用 IFNULL、SUM等函数时,需要确保有查询记录,否则也将返回 null 值,当然也可使用 3 方式避免返回 null 值,避免程序中出现NPE异常。