前言
公司有个sql脚本审核工具,在创建新表或者老表加字段的时候,建议所有的字段都设置为not null而不是mysql默认的default null。设置not null 究竟有啥好处呢?带着这个疑问我们一起看一下之前遇到的一个问题。之前需要统计订单表中非WebHyjy类型的订单数量,当时写了这样的一个sql
SELECT count(t.Id) FROM `OrderInfo` t where t.orderType!='WebHyjy'
-- 结果 769
然后我们来查询下这个表的总数据量
SELECT count(t.Id) FROM `OrderInfo` t
-- 结果 17359
接着我们再来统计下orderType='WebHyjy'的数量
SELECT count(t.Id) FROM `OrderInfo` t where t.orderType='WebHyjy'
-- 结果346
想必大家已经看出问题了, orderType等于WebHyjy和不等于WebHyjy的数量之和不是表的数据量,这不是很奇怪么?后来distinct之后发现orderType的默认值居然是null,而在orderType!='WebHyjy'的条件筛选中是不包含null值的。为了验证这个点我们修改下sql语种再查询一下
SELECT count(t.Id) FROM `OrderInfo` t where t.orderType!='WebHyjy' or t.DownFrom is null
-- 结果 17013
很显然,这样数据就对的上了,所以说default null往往能给你带来不必要的坑。
mysql的默认值
对于MySql而言,如果不主动设置为NOT NULL的话,那么插入数据的时候默认值就是NULL。NULL和NOT NULL使用的空值代表的含义是不一样,NULL可以认为这一列的值是未知的,空值则可以认为我们知道这个值,只不过他是空的而已。举个例子,一张表中的某一条name
字段是NULL,我们可以认为不知道名字是什么,反之如果是空字符串则可以认为我们知道没有名字,他就是一个空值。而对于大多数程序的情况而言,没有什么特殊需要非要字段要NULL的吧,NULL值反而会对程序造成比如空指针的问题。
默认值为NULL带来的问题
聚合函数不准确
对于NULL值的列,使用聚合函数的时候会忽略NULL值。比如说我们有一张表,name
字段默认是NULL,此时对name
进行count
得出的结果是1,这个是错误的。count(*)
是对表中的行数进行统计,count(name)
则是对表中非NULL的列进行统计。
=失效
对于NULL值的列,是不能使用=
表达式进行判断的,下面对name
的查询是不成立的,必须使用is NULL
。
与其他值运算
NULL和其他任何值进行运算都是NULL,包括表达式的值也是NULL。user
表第二条记录age
是NULL,所以+1
之后还是NULL,name
是NULL,进行concat
运算之后结果还是NULL。
distinct、group by、order by
对于distinct
和group by
来说,所有的NULL值都会被视为相等,对于order by
来说升序NULL会排在最前。
索引问题
关于网上很多说如果NULL那么不能使用索引的说法,这个描述其实并不准确,根据引用官方文档里描述,使用is NULL和范围查询都是可以和正常一样使用索引的,只不过在某些场景下,由于mysql的执行策略导致索引失效。我们知道,一个查询SQL执行大概是这样的流程:
首先连接器负责连接到指定的数据库上,接着看看查询缓存中是否有这条语句,如果有就直接返回结果。如果缓存没有命中的话,就需要分析器来对SQL语句进行语法和词法分析,判断SQL语句是否合法。接着来到优化器,就会选择使用什么索引比较合理,SQL语句具体怎么执行的方案就确定下来了。最后执行器负责执行语句、有无权限进行查询,返回执行结果。索引列存在NULL就会导致优化器在做索引选择的时候更复杂,更加难以优化。