数据库11mysql之坑null专题

空值” 和 “NULL” 的概念

首先,我们要搞清楚“空值” 和 “NULL” 的概念:
1、空值是不占用空间的
2、mysql中的NULL其实是占用空间的,下面是来自于MYSQL官方的解释

1
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

mysql not in 丢失数据

all_student表是所有的学生信息,fail_student表是不及格的学生信息

1
select * from all_student

1
select * from fail_student


查询及格的学生信息

1
2
3
4
select * from all_student
where name not in (
select name from fail_student
)

假如fail_student有一条name为null的记录

再次查询及格的学生信息,结果集为空

1
2
3
4
select * from all_student
where name not in (
select name from fail_student
)

解决方案
修改表结构,设置name字段为not null,并设置默认值
IFNULL 函数: SELECT IFNULL(a,0) FROM table WHERE 1;

1
select * from user where uid not in (a,b,c,null);

这个sql不回返回任何结果。要避免not in的list中出现null的情况

另外:

1
2
3
4
如果null参与算术运算,则该算术表达式的值为null。(例如:+,-,*,/ 加减乘除)
如果null参与比较运算,则结果可视为false。(例如:>=,<=,<> 大于,小于,不等于)
如果null参与聚集运算,则聚集函数都置为null(使用isnull(字段,0)等方式可以避免这种情况)。除count(*), count(1), count(0)等之外(count(字段) 字段为null的行不参与计数)。
如果在not in子查询中有null值的时候,则不会返回数据。

在MySql中,NOT IN是使用列表中的值做相等比较, “!=”不相等时返回true。如果值为null,!=null始终为false。
NOT(TRUE) 为FALSE, NOT(FALSE) 为TRUE, 而NOT(UNKNOWN) 还是UNKNOWN。null表示UNKNOW
解决方法:
对in返回true取反,非true则是想要的结果
select address from house where heating in (‘cool’) is not TRUE;

null与in,not in,exists,not exists的恩恩怨怨

左右比较符:在比较符左边的我们称为左比较符,在比较符右边的我们称为右比较符,例如1 in (1,2),那么in左边的1是左比较符,in右边的(1,2)是右比较符。
参考:解决mysql使用not in 包含null值的问题:https://www.jb51.net/article/203319.htm




左侧(null)右侧(含null)
in必是null分情况
case01:select null not in (1,2,null);nullcase01:select 3 in (1,2,null);null
case02:select null in (1,2);nullcase02:select 1 in (1,2,null);1
not in必是null分情况
case01:select null not in (1,2,null);nullcase01:select 1 not in (1,2,null);0
case02:select null not in (1,2);nullcase02:select 1 not in (2,3,null); null
existsTRUE
case01:select exists (select null);;1
not existsFALSE
case01:select not exists (select null);0

数字+null = null 被气成傻逼

where 必须使用 is null 使用 = null 直接无效,问题是还不报错
null 各种神奇的设定,只此一条就被气成傻逼

count(column)不会统计所有行

count(column)会忽略掉值为NULL的行,相比于count(*)或count(1),统计出来的数字可能会小一些。从性能角度也不建议使用列。

查询运算符、like、between and、in、not in对NULL值查询不起效。

带有条件的查询,对字段b进行条件查询的,b的值为NULL的都没有出现。
对c字段进行like ‘%’查询、in、not查询,c中为NULL的记录始终没有查询出来。
between and查询,为空的记录也没有查询出来。
结论:查询运算符、like、between and、in、not in对NULL值查询不起效。

那NULL如何查询呢?
IS NULL/IS NOT NULL(NULL值专用查询)
上面介绍的各种运算符对NULL值均不起效,mysql为我们提供了查询空值的语法:IS NULL、IS NOT NULL。

MySQL中IS NULL、IS NOT NULL、!=不能用索引?胡扯!

MySQL中决定使不使用某个索引执行查询的依据很简单:就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件。大家以后也多多辟谣吧,没那么复杂,只是一个成本而已。

总结

1:NULL作为布尔值的时候,不为1也不为0
2:任何值和NULL使用运算符(>、<、>=、<=、!=、<>)或者(in、not in、any/some、all),返回值都为NULL
3:当IN和NULL比较时,无法查询出为NULL的记录
4:当NOT IN 后面有NULL值时,不论什么情况下,整个sql的查询结果都为空
5:判断是否为空只能用IS NULL、IS NOT NULL
6:count(字段)无法统计字段为NULL的值,count(*)可以统计值为null的行
7:当字段为主键的时候,字段会自动设置为not null
8:NULL导致的坑让人防不胜防,强烈建议创建字段的时候字段不允许为NULL,给个默认值

参考

mysql not in 丢失数据:https://www.jianshu.com/p/5898e54cdec9
MySQL中NOT IN填坑之列为null的问题解决:www.111com.net/database/180180.htm
关于mysql的null相关查询的一些坑:https://www.cnblogs.com/mr-wuxiansheng/p/11578881.html
MySQL中避免NULL的坑:https://blog.csdn.net/weixin_43894879/article/details/106306608
MySQL中IS NULL、IS NOT NULL、!=不能用索引?胡扯!:https://blog.csdn.net/lonely_bin/article/details/99715968
MySQL使用NOT IN存在null值导致结果错误的解决方法:https://majing.io/posts/10000048141000

数据库系列
数据库01mysql常用操作速查
数据库02mongodb异常错误
数据库03mongodb占用磁盘空间过大
数据库04sqlite转mysql
数据库05redis常用命令整理
数据库06redis事务
数据库07redis分布式锁
数据库08redis其他
数据库09mysql常用查询实例
数据库10mysql之坑
数据库11mysql之坑null专题
数据库12经验之谈
数据库13mysql执行计划
数据库14mysql的redolog与binlog
数据库15mysql报错2006
数据库16mysql之初始密码
数据库17mysql锁机制
数据库18mysql事务和MVCC

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×