数据库索引字段请不要为NULL

一、实验

create table tbNotNull (

`id` INT ( 11 ) NOT NULL,

  name varchar( 20 ) NOT NULL

)




create table tbHaveNull (

`id` INT ( 11 ) NOT NULL,

 name varchar( 20 ) NULL

)




insert into tbNotNull values( 4 , "zhaoliu" ), values( 2 , "lisi" ),values( 3 , "wangmazi" ),values( 1 , "zhangsan" ),values( 2 , "lisi2" ),values( 4 , "zhaoliu2" ),values( 3 , "wangmazi2" )




insert into tbHaveNull values ( 1 , "zhaoliu_2_1" ),( 2 , null )

二、说明

1、NOT IN子查询在有NULL值的情况下返回永远为空结果。

select name from tbNotNull where name not in (select name from tbHaveNull where id!= 1 )




2、如果在两个字段进行拼接：首先要各字段进行非null判断，否则只要任意一个字段为空都会造成拼接的结果为null。

select CONCAT( "1" , null ) from xxxx; -- 执行结果为 null 。




3、如果有 Null column 存在的情况下，count(Null column)需要格外注意，null 值不会参与统计。

注意 Null 字段的判断方式， = null 将会得到错误的结果。

4、注意 Null 字段的判断方式， = null 将会得到错误的结果




5、Null 列需要更多的存储空间：需要一个额外字节作为判断是否为 NULL 的标志位

  explain select * from tbNotNull where name='zhaoliu_2_1';


6、单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集（null的列如果是索引，则为null的列不进入索引里）

-- 如果name允许为 null ，索引不存储 null 值，结果集中不会包含这些记录。所以，请使用not null 约束以及默认值。

key_len 的计算规则和三个因素有关：数据类型、字符编码、是否为 NULL

key_len 62 == 20*3（utf8 3字节） + 2 （存储 varchar 变长字符长度 2字节，定长字段无需额外的字节）

key_len 83 == 20*4（utf8mb4 4字节） + 1 (是否为 Null 的标识) + 2 （存储 varchar 变长字符长度 2字节，定长字段无需额外的字节）

所以说索引字段最好不要为NULL，因为NULL会使索引、索引统计和值更加复杂，并且需要额外一个字节的存储空间。