为什么表设计时必须把字段定义为NOT NULL并设默认值

为什么表设计时必须把字段定义为NOT NULL并设默认值

空间占用的坑

• 空’'在存储过程中是不会占用空间的，但是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>