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)
实在不太建议使用NULL字段

山花浪漫

[赞同][赞同][赞同]