【MySQL】探究之null与not null

插入测试

mysql> insert into test values(null,1);
ERROR 1048 (23000): Column ‘col1‘ cannot be null

mysql> insert into test values(‘‘,null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(‘‘,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(‘NULL‘,1);
Query OK, 1 row affected (0.00 sec)


mysql> select * from test;
+------+------+
| col1 | col2 |
+------+------+
|      | NULL |
|      | 1    |
| NULL | 1    |
+------+------+
3 rows in set (0.00 sec)

可见，NOT NULL 的字段是不能插入NULL的，只能插入“空值”，上面的问题1也就有答案了。读者需要注意的是，最后插入的NULL并非NULL,而是字符串"NULL"。并且对于InnoDB引擎的表具有相同的测试效果。

对于问题2，上面我们已经说过了，NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。 而且B树索引（MyISAM 表）时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。 值得注意的是：尽量避免NULL

1. 很多表都包含可为NULL的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性（TIMESTAMP除外），然而通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。
2. 如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引统计和值比较更加复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊的处理。当可为NULL的字段被索引时，每个索引记录需要一个额外的字节，在MyASIM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
3. 通常把可为NULL的列改为NOT NULL 带来的性能提升比较小，所以（调忧时）没有必要首先在现有schema中查找并修改这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计为NULL的列。当然也有一些例外，例如值得一提的是，InnoDB使用单独的位（Bit）存储NULL值，所以对于稀疏数据（很多值为NULL，只有少数行是非NULL）有很好的空间效率。但这一点不适用于MyISAM。

                                                                                                                                       ---引用自《高性能MySQL－第三版》第四章 Schema与数据类型优化

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

mysql> SELECT * FROM `test` WHERE col1 IS NOT NULL;
+------+------+
| col1 | col2 |
+------+------+
| NULL | 1    |
|      | 1    |
|      | NULL |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `test` WHERE col1 <> ‘‘; 
+------+------+
| col1 | col2 |
+------+------+
| NULL | 1    |
+------+------+
1 row in set (0.00 sec)

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

【MySQL】探究之null与not null

标签：