mysql中null与“空值”的坑

共计 1598 个字符，预计需要花费 4 分钟才能阅读完成。

相信很多用了 MySQL 很久的人，对这两个字段属性的概念还不是很清楚，一般会有以下疑问：

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

2、为毛 not null 的效率比 null 高

3、判断字段不为空的时候，到底要  select * from table where column <> '' 还是要用 select * from table where column is not null 呢。

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

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

1、空值是不占用空间的

2、mysql 中的 NULL 其实是占用空间的，下面是来自于 MYSQL 官方的解释

阿里云 2 核 2G 服务器 3M 带宽 61 元 1 年，有高配

腾讯云新客低至 82 元 / 年，老客户 99 元 / 年

代金券：在阿里云专用满减优惠券

PS：打个比方来说，你有一个杯子，空值代表杯子是真空的，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 ;

插入数据：

INSERT INTO `test` VALUES (null,1);

mysql 发生错误：

#1048 - Column 'col1' cannot be null

再来一条

INSERT INTO `test` VALUES ('',1);

成功插入数据，可见，NOT NULL 的字段是不能插入“NULL”的，只能插入“空值”，上面的问题 1 也就有答案了。

对于问题 2，上面我们已经说过了，NULL 其实并不是空值，而是要占用空间，所以 mysql 在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响，而且 B 树索引时不会存储 NULL 值的，所以如果索引的字段可以为 NULL，索引的效率会下降很多。
我们再向 test 的表中插入几条数据：

INSERT INTO `test` VALUES ('', NULL);  
INSERT INTO `test` VALUES ('1', '2');

现在表中数据：

现在根据需求，我要统计 test 表中 col1 不为空的所有数据，我是该用“<> ””还是“IS NOT NULL”呢，让我们来看一下结果的区别。

SELECT * FROM `test` WHERE col1 IS NOT NULL

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

在筛选非空的字段经常会用到 ”is not null” 和 ”!=null”，但是这两种方法是不一样的！！！！
Why？？？？？而 ”null” 表示什么也不是，用“=、>、< …”所有的判断，结果都是 false，所有只能用 is null 进行判断。默认情况下，推荐使用 IS NOT NULL 去判断，因为 SQL 默认情况下对。”！= Null” 的判断会永远返回 0 行，但没有语法错误。如果你一定想要使用 ”！= Null” 来判断，需要加上这个语句：“set ANSI_NULLS off”这时你会发现“IS NOT NULL”和“!= null”是等效的。

阿里云 2 核 2G 服务器 3M 带宽 61 元 1 年，有高配

腾讯云新客低至 82 元 / 年，老客户 99 元 / 年

代金券：在阿里云专用满减优惠券