空间占用的坑
空’'在存储过程中是不会占用空间的，但是NULL会。就像一个杯子，空表示杯子是真空的，NULL表示装的空气。
mysql> SELECT length('1'),length(NULL),length('');
+-------------+--------------+------------+
| length('1') | length(NULL) | length('') |
+-------------+--------------+------------+
| 1 | NULL | 0 |
+-------------+--------------+------------+
1 row in set
mysql>查询的坑
如果要查询表的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>
