mysql 字段定义不要用null的原因分析

一 NULL 为什么这么经常用

(1) java的null

null是一个让人头疼的问题，比如java中的NullPointerException。为了避免猝不及防的空指针，需要小心翼翼地各种if判断，麻烦又臃肿.

为此有很多的开源包都有诸多处理

common lang3的StringUtils.isBlank(); CollectionUtils.isEmpty();

guava的Optional

甚至java8也引入了Optional来避免这一问题（和guava的大同小异，用法稍有一点点变化)

(2) mysql的null为什么横行滥用

(a) 创建不规范 null是创建数据表时候默认的，一些mysql客户端的自动生成表语句里面可能也没有not null的指定。

(b) 错误认识 会有人觉得not null需要更多的空间

(c) 图省事 null在开发中不用判断插入数据，写sql更方便

二 官方文档

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。 ―― 出自《高性能mysql第二版》

如此看来，不指定not null并没有性能上的优势。

三 mysql不用null的理由

（1）所有使用NULL值的情况，都可以通过一个有意义的值的表示，这样有利于代码的可读性和可维护性，并能从约束上增强业务数据的规范性。

（2）NULL值到非NULL的更新无法做到原地更新，更容易发生索引分裂，从而影响性能。(null -> not null性能提升很小，除非确定它带来了问题，否则不要当成优先的优化措施)

（3）NULL值在timestamp类型下容易出问题，特别是没有启用参数explicit_defaults_for_timestamp

（4）NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果，查询容易出错

四 null引发的bad case

数据初始化:

create table table1 (
    `id` INT (11) NOT NULL,
    `name` varchar(20) NOT NULL
)


create table table2 (
    `id` INT (11) NOT NULL,
    `name`  varchar(20)
)

insert into table1 values (4,"zhaoyun"),(2,"zhangfei"),(3,"liubei")
insert into table2 values (1,"zhaoyun"),(2, null)

(1)NOT IN子查询在有NULL值的情况下返回永远为空结果，查询容易出错

select name from table1 where name not in (select name from table2 where id!=1)

+-------------+
|      name   |
|-------------|
+-------------+

(2) 列值允许为空，索引不存储null值，结果集中不会包含这些记录。

select * from table2 where name != 'zhaoyun'

+------+-------------+
|   id |      name   |
|------+-------------|
|      |             |
+------+-------------+

select * from table2 where name != 'zhaoyun1'

+------+-------------+
|   id |      name   |
|------+-------------|
|   1  |  zhaoyun    |
+------+-------------+

(3) 使用concat拼接时，首先要对各个字段进行非null判断，否则只要任何一个字段为空都会造成拼接的结果为null

select concat("1", null) from dual;

+--------------------+
|   concat("1", null)|
|--------------------|
|               NULL |
+--------------------+

(4) 当计算count时候null column不会计入统计

select count(name) from table2;

+--------------------+
|   count(user_name) |
|--------------------|
|                  1 |
+--------------------+

五 索引长度对比

alter table table1 add index idx_name (name);
alter table table2 add index idx_name (name);
explain select * from table1 where name='zhaoyun';
explain select * from table2 where name='zhaoyun';

table1的key_len = 82

table2的key_len = 83

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

key_len 82 = 20 * 4(utf8mb4 - 4字节, utf8 - 3字节) + 2(存储varchar变长字符长度为2字节，定长字段无需额外的字节)

key_len 83 = 20 * 4(utf8mb4 - 4字节, utf8 - 3字节) + 2(存储varchar变长字符长度为2字节，定长字段无需额外的字节) + 1(是否为null的标志)

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