為什麼表設計時必須把字段定義為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統計的坑

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> 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>