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，会带来不可预料的麻烦
