MySQL NULL 和 NOT NULL 你知道多少

2019独角兽企业重金招聘Python工程师标准>>>

前言：

谈起这个话题，是因为想整理一个开发规范，中间涉及到一条，建表的时候建议字段定义为NOT NULL，但是为什么不可以是NULL呢？怎么让开发相信NOT NULL一定比NULL好呢，往下看把

 

1、NULL 和 ‘’ 值有什么区别？

NULL值会占一个到两个字节的空间，而‘’值是不会占用空间的。

https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html

 

2、为什么字段类型是 NOT NULL 可以插入 ‘’ 值呢？

看看官方给的例子和解释吧，感觉这个解释很完美

 

3、如何判断字段为 NULL 和 ‘’ ？

 

4、为什么索引中 ‘’ 比 NULL 效率高呢？

下面来看一个例子：因为B树索引是不会存储NULL值的，索引字段可以NULL，索引效率会下降很多

root@localhost:mysql.sock  15:03:25 [tom]>show create table a;
+-------+-----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                  |
+-------+-----------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `k` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  15:04:34 [tom]>SELECT * FROM a;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 |    3 |
|    4 |   44 |
|    5 |   55 |
+------+------+
5 rows in set (0.00 sec)

root@localhost:mysql.sock  15:05:47 [tom]>explain select b from a where b is null;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | ref  | k             | k    | 5       | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

root@localhost:mysql.sock  16:42:51 [tom]>explain select b from a where b='';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ref  | k             | k    | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

 

5、NULL值得其他问题

参考文档：

https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html

https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html

 

为了方便大家交流，本人开通了微信公众号，和QQ群291519319。喜欢技术的一起来交流吧

转载于:https://my.oschina.net/u/3023401/blog/1359309