今天我要分享一个高频面试问题，你可以在5分钟内理解。为什么MySQL不建议使用null作为列默认值？
这个问题的答案通常是“”。具有 null 值的列将使索引失效但是如果你实际测试它，你就知道 null 会使用索引，所以上面的语句有一个漏洞。
那些匆忙的人拉到底部看看结论。
前言
null is a special constraint of columns. the columns in table will be added null constrain if you do not define the column with “not null” key words explicitly when creating the table.many programmers like to define columns by default because of the conveniences(reducing the judgement code of nullibility) what consequently cause some uncertainty of query and poor performance of database.
null 值是对一个列的特殊约束，当我们创建一个新列时，如果数据列没有用关键字 not null 显式声明，MySQL 会默认为我们添加一个 null 约束。 一些开发人员在创建数据表时懒得使用 MySQL 的默认推荐设置（即允许字段使用 null 值）。 这种坏习惯很容易导致查询结果不确定，在使用 null 的情况下降低数据库性能。
介绍
null is null means it is not anything at all,we cannot think of null is equal to ‘’and they are totally different. mysql provides three operators to handle null value:“is null”,“is not null”,"<=>" and a function ifnull().is null: it returns true,if the column value is null. is not null: it returns true,if the columns value is not null. <=>: it’s a compare operator similar with “=” but not the same.it returns true even for the two null values. (eg. null <=> null is legal) ifnull():specify two input parameters,if the first is null value then returns the second one. it’s similar with oracle’s nvl() function.
null 并不意味着什么都没有，我们需要注意 null 并遵循''（Null 值）是两个完全不同的值。 在MySQL中可以操作的空值运算符主要有三种类型。
is null
is not null
飞船运算符，这个运算符很像 =，select null<=>null 可以返回 true，但 select null=null 返回 false。
ifnull 函数。 如何使用它自己检查......无论如何，我会的。
example
null never returns true when comparing with any other values except null with “<=>”.
null 将通过将任何运算符与除 <=> 之外的任何其他值进行比较来获得。
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 means “a missing and unknown value”.let’s see details below.
null 表示一个不确定的值，即使它是两个 null，它们也不一定相等。 （就像 c 中未初始化的局部变量一样）。
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 |
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.
it truns null as a result if any expression contains null value.
任何返回值包含 null 的表达式都将获得另一个 null 值。
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.
it’s diffrent when using count(*)count(null column).
使用 count（*） 或 count（null column） 的结果不同，count（null column）<=count（*）。
root@localhost mysql3306.sock)[zlm]>select count(*)count(name) from test_null;
count(*)count(name) |
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.
when using distinct,group by,order by,all null values are considered as the same value.
虽然 select null=null 的结果是 false，但当我们使用 distinct、group by 和 order by 时，null 被认为是相同的值。
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 supports to use index on column which contains null value(what’s different from oracle).
MySQL 支持对具有 null 值的列进行索引，但 Oracle 不支持。 这就是我们通常所说的，如果一个列包含 null，它将使索引失效。
严格来说，这种说法对于MySQL来说是不准确的。
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 sbtest1\g
*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 | 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 | 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.
这是我自己测试的一个例子。
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 | 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 | 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 | 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 | 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 value always leads to many uncertainties when disposing sql statement.it may cause bad performance accidentally.
在列中使用 null 值很容易导致不受控制的事情发生，有时还会严重降低系统的性能。
例如：不会在聚合函数 （） 中估计 null 值，这可能会导致结果不准确
对具有 null 值的列执行统计计算，例如count（）、max（）、min（），结果不符合我们的预期。
null value will influence the beh**ior of the operations such as “distinct”,“group by”,“order by” which causes wrong sort.
干扰排序、分组、重复数据删除结果。
null value needs ifnull() function to do judgement which makes the program code more complex.
有时我们需要在 SQL 中使用 ifnull（） 来确保结果是可控的，以消除 null 带来的技术债务，但这会使程序复杂化。
null value needs a extra 1 byte to store the null information in the rows.
null 值不存储在原始字段空间中，但请求一个额外的字节来注释该字段，并使用 null 约束添加该字段（就像额外的标志位一样）。
as these above drawbacks,it’s not recommended to define columns with default null. we recommand to define “not null” on all columns and use zero number & vacant string to substitute relevant data type of null.
基于以上缺点，我们不建议设置 null 作为列的默认值，可以使用 not null 来消除默认设置，使用 0 或''空字符串而不是 null。
译者丨guangsu
*丨***J**aguide（ID：J**aguide）。
DBAPLUS 社区欢迎 editor@dbaplus 技术人员的贡献cn