今天我要分享乙個高頻面試問題，你可以在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