那么在 MySQL 中,NULL 表示不知道的数据。我们在设计表的时候,经常会有老司机这么告诉我们。
字段尽可能用NOT NULL,而不是NULL,除非特殊情况。
这句话到底有没有错?可以负责任的告诉你这句话没有错,也不是以讹传讹。这句话首次出现在 MySQL 官网。
如果你读过《高性能 MySQL》这本书,你应该会看到这么一段,在 4.1 节提到。
由此看来,把 NULL 改成 NOT NULL 对索引的性能并没有明显的提升。避免使用 NULL 的目的,是便于代码的可读性和可维护性。同时也便于避免下文即将出现的一些稀奇古怪的错误。
好了,下面咱们通过实验来看看,使用 NULL 会出现那些稀奇古怪的错误呢?
跟我一样在本地建两个表 t1,t2;其中一个表 name 字段允许为空,另一个表 name 字段不允许为空,分别对 name 字段建立索引,SQL 语句如下。
1、NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回非空行的结果集。
比如上例中的 t2,我执行如下 SQL 语句。
SELECT * from t2 where name != '张三'
你本打算返回 id 为 2 的那行数据,然而什么都没有。
又比如下面这条 SQL 语句,也返回了空结果集。
select * from t2 where name not in (select name from t2 where id!=1)
2、使用 concat 函数拼接时,首先要对各个字段进行非 NULL 判断,否则只要任何一个字段为空都会造成拼接的结果为 NULL。
比如下面这条 SQL 语句。
SELECT CONCAT("1",NULL)
3、当用count函数进行统计时,NULL 列不会计入统计。
SELECT count(name) from t2
4、查询空行数据,用 is NULL。
SELECT * FROM t2 where name is NULL
5、NULL 列需要更多的存储空间,一般需要一个额外的字节作为判断是否为 NULL 的标志位。
如果你仔细观察 t1 和 t2 表的 key_len,会发现 t2 比 t1 多了一个字节。
explain SELECT * from t2 where name = '张三'
explain SELECT * from t1 where name = '张三'
key_len 的长度一般跟这三个因素有关,分别是数据类型,字符编码,是否为 NULL。
因此,t2 比 t1 多出的这一个字节,用于作为判断是否为 NULL 的标志位了。