今天来分享一道美团高频面试题，5 分钟搞懂“为什么 MySQL 不建议使用 NULL 作为列默认值？”。
对于这个问题，通常能听到的答案是使用了NULL值的列将会使索引失效,但是如果实际测试过一下,你就知道IS NULL会使用索引，所以上述说法有漏洞。
着急的人拉到最下边看结论
前言
NULL值是一种对列的特殊约束,我们创建一个新列时,如果没有明确的使用关键字not null声明该数据列,MySQL会默认的为我们添加上NULL约束。
有些开发人员在创建数据表时,由于懒惰直接使用Mysql的默认推荐设置.(即允许字段使用NULL值).而这一陋习很容易在使用NULL的场景中得出不确定的查询结果以及引起数据库性能的下降。
介绍
NULL并不意味着什么都没有,我们要注意 NULL 跟 ''(空值)是两个完全不一样的值，MySQL中可以操作NULL值操作符主要有三个。
- IS NULL
- IS NOT NULL
- <=> 太空船操作符,这个操作符很像=,select NULL<=>NULL可以返回true,但是select NULL=NULL返回false.
- IFNULL 一个函数.怎么使用自己查吧…反正我会了
NULL通过任一操作符与其它值比较都会得到NULL,除了<=>.
代码语言：javascript复制(root@localhost mysql3306.sock)[zlm]>create table test_null(
-> id int not null,
-> name varchar(10)
-> );
Query OK, 0 rows affected (0.02 sec)
(root@localhost mysql3306.sock)[zlm]>insert into test_null values(1,'zlm');
Query OK, 1 row affected (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>insert into test_null values(2,null);
Query OK, 1 row affected (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null;
---- ------
| id | name |
---- ------
| 1 | zlm |
| 2 | NULL |
---- ------
2 rows in set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where name=null;
Empty set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where name is null;
---- ------
| id | name |
---- ------
| 2 | NULL |
---- ------
1 row in set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where name is not null;
---- ------
| id | name |
---- ------
| 1 | zlm |
---- ------
1 row in set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where null=null;
Empty set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where null<>null;
Empty set (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select * from test_null where null<=>null;
---- ------
| id | name |
---- ------
| 1 | zlm |
| 2 | NULL |
---- ------
2 rows in set (0.00 sec)
//null<=>null always return true,it's equal to "where 1=1".
NULL代表一个不确定的值,就算是两个NULL,它俩也不一定相等.(像不像C中未初始化的局部变量)
代码语言：javascript复制(root@localhost mysql3306.sock)[zlm]>SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
----------- --------------- ------------ ----------------
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
----------- --------------- ------------ ----------------
| 0 | 1 | 0 | 1 |
----------- --------------- ------------ ----------------
1 row in set (0.00 sec)
//It's not equal to zero number or vacant string.
//In MySQL,0 means fasle,1 means true.
(root@localhost mysql3306.sock)[zlm]>SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
---------- ----------- ---------- ----------
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
---------- ----------- ---------- ----------
| NULL | NULL | NULL | NULL |
---------- ----------- ---------- ----------
1 row in set (0.00 sec)
//It cannot be compared with number.
//In MySQL,null means false,too.
任何有返回值的表达式中有NULL参与时,都会得到另外一个NULL值.
代码语言：javascript复制(root@localhost mysql3306.sock)[zlm]>select ifnull(null,'First is null'),ifnull(null 10,'First is null'),ifnull(concat('abc',null),'First is null');
------------------------------ --------------------------------- --------------------------------------------
| ifnull(null,'First is null') | ifnull(null 10,'First is null') | ifnull(concat('abc',null),'First is null') |
------------------------------ --------------------------------- --------------------------------------------
| First is null | First is null | First is null |
------------------------------ --------------------------------- --------------------------------------------
1 row in set (0.00 sec)
//null value needs to be disposed with ifnull() function,what usually causes sql statement more complex.
//As we all know,MySQL does not support funcion index.Therefore,indexes on the column may not be used.That's really worse.
使用count() 或者 count(null column)结果不同,count(null column)<=count().
代码语言：javascript复制(root@localhost mysql3306.sock)[zlm]>select count(*),count(name) from test_null;
---------- -------------
| count(*) | count(name) |
---------- -------------
| 2 | 1 |
---------- -------------
1 row in set (0.00 sec)
//count(*) returns all rows ignore the null while count(name) returns the non-null rows in column "name".
//This will also leads to uncertainty if someone is unaware of the details above.
虽然select NULL=NULL的结果为false,但是在我们使用distinct,group by,order by时,NULL又被认为是相同值.
代码语言：javascript复制(root@localhost mysql3306.sock)[zlm]>insert into test_null values(3,null);
Query OK, 1 row affected (0.00 sec)
(root@localhost mysql3306.sock)[zlm]>select distinct name from test_null;
------
| name |
------
| zlm |
| NULL |
------
2 rows in set (0.00 sec)
//Two rows of null value returned one and the result became two.
(root@localhost mysql3306.sock)[zlm]>select name from test_null group by name;
------
| name |
------
| NULL |
| zlm |
------
2 rows in set (0.00 sec)
//Two rows of null value were put into the same group.
//By default,group by will also sort the result(null row showed first).
(root@localhost mysql3306.sock)[zlm]>select id,name from test_null order by name;
---- ------
| id | name |
---- ------
| 2 | NULL |
| 3 | NULL |
| 1 | zlm |
---- ------
3 rows in set (0.00 sec)
//Three rows were sorted(two null rows showed first).
MySQL中支持在含有NULL值的列上使用索引,但是Oracle不支持.这就是我们平时所说的如果列上含有NULL那么将会使索引失效。
严格来说，这句话对与MySQL来说是不准确的。
代码语言：javascript复制(root@localhost mysql3306.sock)[sysbench]>show tables;
--------------------
| Tables_in_sysbench |
--------------------
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
--------------------
10 rows in set (0.00 sec)
(root@localhost mysql3306.sock)[sysbench]>show create table sbtest1G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
(root@localhost mysql3306.sock)[sysbench]>alter table sbtest1 modify k int null,modify c char(120) null,modify pad char(60) null;
Query OK, 0 rows affected (4.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost mysql3306.sock)[sysbench]>insert into sbtest1 values(100001,null,null,null);
Query OK, 1 row affected (0.00 sec)
(root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where id=100001;
---- ------------- --------- ------------ ------- --------------- --------- --------- ------- ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------- ------------ ------- --------------- --------- --------- ------- ------ ---------- -------
| 1 | SIMPLE | sbtest1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
---- ------------- --------- ------------ ------- --------------- --------- --------- ------- ------ ---------- -------
1 row in set, 1 warning (0.00 sec)
(root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where k is null;
---- ------------- --------- ------------ ------ --------------- ------ --------- ------- ------ ---------- --------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- --------- ------------ ------ --------------- ------ --------- ------- ------ ---------- --------------------------
| 1 | SIMPLE | sbtest1 | NULL | ref | k_1 | k_1 | 5 | const | 1 | 100.00 | Using where; Using index |
---- ------------- --------- ------------ ------ --------------- ------ --------- ------- ------ ---------- --------------------------
1 row in set, 1 warning (0.00 sec)
//In the first query,the newly added row is retrieved by primary key.
//In the second query,the newly added row is retrieved by secondary key "k_1"
//It has been proved that indexes can be used on the columns which contain null value.
//column "k" is int datatype which occupies 4 bytes,but the value of "key_len" turn out to be 5.what's happed?Because null value needs 1 byte to store the null flag in the rows.
这个是我自己测试的例子：
代码语言：javascript复制mysql> select * from test_1;
----------- ------ ------
| name | code | id |
----------- ------ ------
| gaoyi | wo | 1 |
| gaoyi | w | 2 |
| chuzhong | wo | 3 |
| chuzhong | w | 4 |
| xiaoxue | dd | 5 |
| xiaoxue | dfdf | 6 |
| sujianhui | su | 99 |
| sujianhui | NULL | 99 |
----------- ------ ------
8 rows in set (0.00 sec)
mysql> explain select * from test_1 where code is NULL;
---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- -----------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- -----------------------
| 1 | SIMPLE | test_1 | NULL | ref | index_code | index_code | 161 | const | 1 | 100.00 | Using index condition |
---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- -----------------------
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_1 where code is not NULL;
---- ------------- -------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- -----------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- -------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- -----------------------
| 1 | SIMPLE | test_1 | NULL | range | index_code | index_code | 161 | NULL | 7 | 100.00 | Using index condition |
---- ------------- -------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- -----------------------
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_1 where code='dd';
---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- -----------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- -----------------------
| 1 | SIMPLE | test_1 | NULL | ref | index_code | index_code | 161 | const | 1 | 100.00 | Using index condition |
---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- -----------------------
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_1 where code like "dd%";
---- ------------- -------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- -----------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- -------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- -----------------------
| 1 | SIMPLE | test_1 | NULL | range | index_code | index_code | 161 | NULL | 1 | 100.00 | Using index condition |
---- ------------- -------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- -----------------------
1 row in set, 1 warning (0.00 sec)
总结
列中使用NULL值容易引发不受控制的事情发生,有时候还会严重托慢系统的性能.
例如:
- 对含有NULL值的列进行统计计算,eg. count(),max(),min(),结果并不符合我们的期望值.
- 干扰排序，分组,去重结果.
- 有的时候为了消除NULL带来的技术债务,我们需要在SQL中使用IFNULL()来确保结果可控,但是这使程序变得复杂.
- NULL值并是占用原有的字段空间存储,而是额外申请一个字节去标注,这个字段添加了NULL约束.(就像额外的标志位一样)
根据以上缺点,我们并不推荐在列中设置NULL作为列的默认值,你可以使用NOT NULL消除默认设置,使用0或者''空字符串来代替NULL。
