空间占用的坑

空’'在存储过程中是不会占用空间的，但是NULL会。就像一个杯子，空表示杯子是真空的，NULL表示装的空气。

mysql> SELECT length('1'),length(NULL),length('');

+-------------+--------------+------------+
| length('1') | length(NULL) | length('') |
+-------------+--------------+------------+
|           1 | NULL         |          0 |
+-------------+--------------+------------+
1 row in set

查询的坑

如果要查询表的NULL需要使用 is null或is not null，如果直接使用=/!=/in/not inl将查询不到值

mysql> SELECT * FROM `user`;
+----+------+-----+---------+
| id | name | age | address |
+----+------+-----+---------+
|  1 | wyf  |  32 | 合肥    |
|  2 | xx   |  31 | 北京    |
|  3 | yy   |  30 | 上海    |
|  4 | zz   |  11 |         |
|  5 | aa   |  21 | NULL    |
+----+------+-----+---------+
5 rows in set

mysql> SELECT * FROM `user` WHERE address IS NULL;
+----+------+-----+---------+
| id | name | age | address |
+----+------+-----+---------+
|  5 | aa   |  21 | NULL    |
+----+------+-----+---------+
1 row in set

mysql> SELECT * FROM `user` WHERE address = NULL;
Empty set

mysql> 

NULL统计的坑

如果使用count()等统计函数，将不会统计NULL。如下，一共有5条数据，统计address就只返回4。

mysql> SELECT * FROM `user`;
+----+------+-----+---------+
| id | name | age | address |
+----+------+-----+---------+
|  1 | wyf  |  32 | 合肥    |
|  2 | xx   |  31 | 北京    |
|  3 | yy   |  30 | 上海    |
|  4 | zz   |  11 |         |
|  5 | aa   |  21 | NULL    |
+----+------+-----+---------+
5 rows in set

mysql> SELECT COUNT(address) FROM `user`;
+----------------+
| COUNT(address) |
+----------------+
|              4 |
+----------------+
1 row in set

mysql> 

索引的坑

Mysql的索引会为NULL值做特殊处理，导致整个索引的查询效率下降。如果是语句中有 is null会使用索引，如果语句中有is not null则会导致索引失效，如下：

查看索引：

mysql> show index from user;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY     |            1 | id          | A         |           5 | NULL     | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_name    |            1 | name        | A         |           5 | NULL     | NULL   | YES  | BTREE      |         |               |
| user  |          1 | idx_address |            1 | address     | A         |           5 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

is null 会使用索引

mysql> explain select * from user where address is null;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_address   | idx_address | 1023    | const |    1 |      100 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set

is not null 不会使用索引

mysql> explain select * from user where address is not null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | idx_address   | NULL | NULL    | NULL |    5 |       80 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set

mysql> 

原文链接：https://xiaolyuh.blog.csdn.net/article/details/105427529