MySQL NULL值分析
通常情况下，MySQL基本在InnoDB引擎下使用， 故相关描述均以InnoDB引擎为背景。使用的表结构和数据
CREATE TABLE `demo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`abc` int(11) DEFAULT NULL,
`xyz` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `IDX_ABC` (`abc`) USING BTREE,
KEY `IDX_XYZ` (`xyz`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `demo`(`id`, `abc`, `xyz`) VALUES (1, 1, 'hello');
INSERT INTO `demo`(`id`, `abc`, `xyz`) VALUES (2, 2, NULL);
INSERT INTO `demo`(`id`, `abc`, `xyz`) VALUES (3, NULL, 'world');1、NULL是什么
The NULL value means “no data.” NULL can be written in any lettercase（大小写不敏感）. -- 引用自MySQL手册
NULL的长度问题
mysql> select length(''), length(null), length(0), length('0');
+------------+--------------+-----------+-------------+
| length('') | length(null) | length(0) | length('0') |
+------------+--------------+-----------+-------------+
| 0 | NULL | 1 | 1 |
+------------+--------------+-----------+-------------+
1 row in set (0.00 sec)NULL的长度是NULL
Compact Row Format前提下，每个行记录都会有一个Bit vector来记录行中出现NULL的字段，长度为 N / 8 向上取整，其中 N为值NULL的字段数。
MySQL针对NULL进行的特殊处理逻辑有很多。
2、NULL与查询
如果要查询某个字段为NULL，不能使用 = NULL，必须使用 IS NULL。
如果要查询某个字段不为NULL，不能使用 != NULL，必须使用 IS NOT NULL。
mysql> select null = null, null is null, null != null, null is not null;
+-------------+--------------+--------------+------------------+
| null = null | null is null | null != null | null is not null |
+-------------+--------------+--------------+------------------+
| NULL | 1 | NULL | 0 |
+-------------+--------------+--------------+------------------+
1 row in set (0.00 sec)
mysql> select * from demo where abc is null;
+----+------+-------+
| id | abc | xyz |
+----+------+-------+
| 3 | NULL | world |
+----+------+-------+
1 row in set (0.00 sec)如果查询一个存在NULL可能的字段不为某个特定值时， != 运算将不包含该字段为NULL的记录！比如想查询demo表中 id不为1的记录，可以看到并不包含abc is null的行记录，等价于select * from demo where abc != 1 and abc is not null;
mysql> select * from demo where abc != 1;
+----+------+------+
| id | abc | xyz |
+----+------+------+
| 2 | 2 | NULL |
+----+------+------+
1 row in set (0.00 sec)NULL也不能被用于范围查询，即NULL与非NULL值无法比较大小
mysql> select 1 = null, 1 > null, 1 < null, 1<>null;
+----------+----------+----------+---------+
| 1 = null | 1 > null | 1 < null | 1<>null |
+----------+----------+----------+---------+
| NULL | NULL | NULL | NULL |
+----------+----------+----------+---------+
1 row in set (0.00 sec)
mysql> select * from demo where abc > 1;
+----+------+------+
| id | abc | xyz |
+----+------+------+
| 2 | 2 | NULL |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from demo where abc < 2;
+----+------+-------+
| id | abc | xyz |
+----+------+-------+
| 1 | 1 | hello |
+----+------+-------+
1 row in set (0.00 sec)3、NULL与索引
MySQL会对NULL字段也进行索引，但是只有IS NULL的方式会使用上索引。
mysql> explain select * from demo where abc = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: demo
type: ref
possible_keys: IDX_ABC
key: IDX_ABC
key_len: 5
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
mysql> explain select * from demo where abc is null\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: demo
type: ref
possible_keys: IDX_ABC
key: IDX_ABC
key_len: 5
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)唯一索引字段允许插入多条NULL的记录，如果把abc字段修改为UNIQUE之后
mysql> insert into demo(`abc`, `xyz`) values(null, 'hello');
Query OK, 1 row affected (0.00 sec)
mysql> insert into demo(`abc`, `xyz`) values(null, 'hello');
Query OK, 1 row affected (0.01 sec)
mysql> select * from demo;
+----+------+-------+
| id | abc | xyz |
+----+------+-------+
| 1 | 1 | hello |
| 2 | 2 | NULL |
| 3 | NULL | world |
| 4 | NULL | hello |
| 5 | NULL | hello |
+----+------+-------+
6 rows in set (0.00 sec)
索引长度：复制demo表到demo_copy，将abc, xyz设置为非NULL，查看索引长度key_len
mysql> show create table demo_copy\G
*************************** 1. row ***************************
Table: demo_copy
Create Table: CREATE TABLE `demo_copy` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`abc` int(11) NOT NULL,
`xyz` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `IDX_ABC` (`abc`) USING BTREE,
KEY `IDX_XYZ` (`xyz`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> explain select * from demo where xyz = 'hello'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: demo
type: ref
possible_keys: IDX_XYZ
key: IDX_XYZ
key_len: 99
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)
mysql> explain select * from demo_copy where xyz = 'hello'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: demo_copy
type: ref
possible_keys: IDX_XYZ
key: IDX_XYZ
key_len: 98
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)4、NULL与数据类型、数据运算
如果允许NULL，那么该字段的数据类型，从程序的角度上来说，是不统一的，你很难把NULL跟整数，或者NULL跟字符串当作同一个类型处理。NULL值与其他值进行运算，结果总是为NULL。
mysql> select 1 + null;
+----------+
| 1 + null |
+----------+
| NULL |
+----------+
1 row in set (0.01 sec)
mysql> select concat('abc', null);
+---------------------+
| concat('abc', null) |
+---------------------+
| NULL |
+---------------------+
1 row in set (0.00 sec)5、NULL与COUNT
COUNT(*)：不管字段为什么，计算存在的行
COUNT(column_name)：结果不包含字段为NULL的记录
mysql> select count(*) from demo;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> select count(abc) from demo;
+------------+
| count(abc) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
mysql> select count(xyz) from demo;
+------------+
| count(xyz) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)6、NULL与排序
For sorting with ORDER BY, NULL values sort before other values for ascending sorts, after other values for descending sorts.
ASC时，NULL值在所有其他值之前
DESC时，NULL值在所有其他值之后
请注意与查询中的范围匹配区分，这只是一种约定，并不是因为NULL更小
mysql> select * from demo order by abc asc;
+----+------+-------+
| id | abc | xyz |
+----+------+-------+
| 3 | NULL | world |
| 1 | 1 | hello |
| 2 | 2 | NULL |
+----+------+-------+
3 rows in set (0.00 sec)
mysql> select * from demo order by abc desc;
+----+------+-------+
| id | abc | xyz |
+----+------+-------+
| 2 | 2 | NULL |
| 1 | 1 | hello |
| 3 | NULL | world |
+----+------+-------+
3 rows in set (0.00 sec)
mysql> select * from demo order by xyz asc;
+----+------+-------+
| id | abc | xyz |
+----+------+-------+
| 2 | 2 | NULL |
| 1 | 1 | hello |
| 3 | NULL | world |
+----+------+-------+
3 rows in set (0.00 sec)
mysql> select * from demo order by xyz desc;
+----+------+-------+
| id | abc | xyz |
+----+------+-------+
| 3 | NULL | world |
| 1 | 1 | hello |
| 2 | 2 | NULL |
+----+------+-------+
3 rows in set (0.00 sec)7、NULL与分区
该逻辑与排序一致，NULL总是判断为排序值较小的那些
mysql> CREATE TABLE t1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (0),
-> PARTITION p1 VALUES LESS THAN (10),
-> PARTITION p2 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE t2 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (-5),
-> PARTITION p1 VALUES LESS THAN (0),
-> PARTITION p2 VALUES LESS THAN (10),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.09 sec)如果c2字段为NULL的记录，其分区总是在p0分区，查看上述命令创建的分区
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 0 | 0 | 0 |
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
| t2 | p0 | 0 | 0 | 0 |
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)执行数据插入
mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2;
+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)查看更新后的分区信息
mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
> FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 1 | 20 | 20 |
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
| t2 | p0 | 1 | 20 | 20 |
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec) 8、总结
- NULL只支持IS NULL、IS NOT NULL、IFNULL()操作
- NULL对数学比较运算符（>, =, <=, <>）运算出的结果都是FALSE
- 索引列是允许存在NULL的
- DISTINCT、GROUP BY、ORDER BY中认为所有的NULL值都是相等的
- ORDER BY认为NULL是最小的值
- MIN()、SUM()、COUNT()在运算时会忽略NULL值，但是COUNT(*)不会忽略
- TIMESTAMP类型的字段被插入NULL时，实际写入到表中的是当前时间
- AUTO_INCREMENT属性的字段被插入NULL时，实际写入到表中的是顺序的下一个自增值
- 想要禁止某个字段被设置为NULL，则对此字段设置NOT NULL属性
- 如非必要，不要使用NULL，会带来不可预料的麻烦
