MySQL中NULL和NOT NULL详解

这篇小短文讲的是啥

之前看到有人问到 PHPHub 迁移数据库文件中 nullable 和索引的问题，相信很多用了 MySQL 很久的人（特别是平时过多关注业务开发的人），对这两个字段属性的概念还不是很清楚，一般会有以下疑问：

我字段类型是 not null，为什么我可以插入 空值；

为毛 not null 的效率比 null 高；

判断字段不为空的时候，到底要 column <> '' 还是要用 column is not null 呢。

带着上面几个疑问，我们来深入研究一下 null 和 not null 到底有什么不一样。

null 和 空值 一样么

首先，我们要搞清楚 空值 和 null 的概念：

空值 是不占用空间的；

MySQL 中的 null 其实是占用空间的，下面是来自于 MYSQL 官方的解释：

打个比方来说，你有一个杯子，空值代表杯子是真空的，NULL 代表杯子中装满了空气，虽然杯子看起来都是空的，但是区别是很大的。

一个小栗子

搞清楚 “空值” 和 “NULL” 的概念之后，问题基本就明了了，我们搞个例子测试一下：CREATE TABLE`test` (`col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL ) ENGINE = MYISAM ;问题 1：我字段类型是 not null，为什么我可以插入 空值？

执行下面的 SQL，发生错误，提示 Column 'col1' cannot be null。INSERT INTO `test` VALUES ( null, 1);再来一条，执行成功。INSERT INTO `test` VALUES ('',1);可见，NOT NULL 的字段是不能插入 NULL 的（这不是废话么），只能插入 空值，上面的问题 1 也就有答案了。

问题 2：为毛 not null 的效率比 null 高？

对于问题 2，上面我们已经说过了，NULL 其实并不是空值，而是要占用空间，所以 mysql 在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。

而且 B 树索引时不会存储 NULL 值的，所以如果索引的字段可以为 NULL，索引的效率会下降很多。

问题 3：判断字段不为空的时候，到底要 column<>'' 还是要用 column is not null 呢。

我们再向 test 的表中插入几条数据：INSERT INTO `test` VALUES ('', NULL);INSERT INTO `test` VALUES ('1', '2');现在根据需求，我要统计 test 表中 col1 不为空的所有数据，我是该用 <>'' 还是 IS NOT NULL 呢，让我们来看一下结果的区别。

现在表中的数据如下：

分别对照以下两句 SQL 的执行效果SELECT * FROM `test` WHERE col1 IS NOT NULLSELECT * FROM `test` WHERE col1 <> ''

可以看到，结果迥然不同，所以我们一定要根据业务需求，搞清楚到底是要用那种搜索条件，以及要不要为 null。

自己遇到的一个小坑

在许久以前刚入职做第一个需求上线的时候，只一味注意到说是 not null 的效率比 null 的效率高。

好嘛～自己在现有的表上增加字段时全设置为了 not null，感觉自己LL的。

因为许多 Service 都有操作这个表的插入动作，结果可想而知，刚上线，错误 Column 'col1' cannot be null 弥漫了整个开发组每个人的邮箱。

所以，当业务量不是很大的情况下，很多技术的使用其实都需要根据实际状况综合考虑。以上就是MySQL中NULL和NOT NULL详解的详细内容，更多请关注小潘博客其它相关文章！