背景

最近在開發(fā)小伙伴的需求，遇到了一個(gè)數(shù)據(jù)庫統(tǒng)計(jì)的問題，
is not null 結(jié)果正確
！=null 結(jié)果就不對(duì)，然后就激發(fā)了獲取真理的想法，那必須的查查
咋回事嘞？

開整

在用MySQL的過程中，你是否存在過如下的幾個(gè)疑問？

• 我的字段類型明明指定的是NOT NULL，但是為什么還是可以插入空值呢？
• 為什么NOT NULL的效率比NULL更高？
• 在查詢空字段的記錄時(shí)是用"select * from table where col <> ‘’ “還是用"select * from table where col is not null”？

帶著疑問，我們來看看NOT NULL和NULL有什么不一樣呢？要搞清楚這兩個(gè)的區(qū)別。首先，我們先要理解"空值"和"NULL"的含義：

• 空值是不占用空間的。
• NULL是會(huì)占用空間的，我們來看看官方對(duì)這個(gè)NULL的描述。MySQL的官方描述如下：

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

怎么理解呢？簡單舉個(gè)栗子：

假設(shè)有一個(gè)瓶子，空值表示的是瓶子里什么都沒有，NULL表示的是瓶子里面狀態(tài)的是空氣?？梢岳斫鉃槭裁碞ULL也會(huì)占用空間了吧。

下面來通過一個(gè)實(shí)例例子來測(cè)試一下。首先建一個(gè)表，表引擎使用InnoDB，建表語句如下：

create table test(
  c1 varchar(10) not null, 
  c2 varchar(10) default null
) engine = InnoDB;

驗(yàn)證插入數(shù)據(jù)和查詢：

mysql> insert into test(c1, c2) values(null, 0);
ERROR 1048 (23000): Column 'c1' cannot be null

mysql> insert into test(c1, c2) values('', 0);
Query OK, 1 row affected (0.00 sec)
# null字符串
mysql> insert into test(c1, c2) values('null', 0);
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from test;
+------+------+
| c1   | c2   |
+------+------+
|      | 0    |
| null | 0    |
|      | NULL |
+------+------+
3 rows in set (0.00 sec)

從上述結(jié)果中可以看到，NOT NULL的字段是沒辦法插入NULL值的，只能插入空值’'。上面第三個(gè)SQL插入的NULL是個(gè)字符串格式的NULL，對(duì)于MyISAM的存儲(chǔ)引擎，測(cè)試的結(jié)果和上述結(jié)果是一樣的。所以第1個(gè)疑問也就了解了吧。

對(duì)于第2個(gè)問題，因?yàn)镹ULL值是占了一定空間的，所以在MySQL進(jìn)行字段比較的時(shí)候，值為NULL的字段也是會(huì)參與比較的，所以是會(huì)對(duì)性能有一定的影響。

當(dāng)字段上包含有索引時(shí)，由于B樹索引是不會(huì)存儲(chǔ)NULL值的，所以在使用這個(gè)字段做為查詢條件時(shí)，對(duì)性能的影響還是比較大的，在平時(shí)創(chuàng)建索引的時(shí)候，應(yīng)該盡量保證列的值不為NULL。

針對(duì)上述的結(jié)論，有幾個(gè)針對(duì)NULL和NOT NULL的常見優(yōu)化建議：

• MySQL如果不指定列的約束，默認(rèn)就是允許NULL，TIMESTAMP類型的字段除外。所以在非必要情況下，盡量設(shè)置列的約束為NOT NULL。
• 如果列的值為NULL，通過這個(gè)為NULL的列進(jìn)行條件查詢時(shí)，MySQL更難做優(yōu)化，因?yàn)闉镹ULL的列會(huì)讓索引的統(tǒng)計(jì)和值的比較更加復(fù)雜。
• 如果計(jì)劃在某個(gè)列上創(chuàng)建索引，那么需要盡量避免這個(gè)列中的字段值為NULL。在優(yōu)化的過程中，把NULL改為NOT NULL對(duì)性能的提升并不是很明顯。所以如果在使用過程中沒有問題的話，沒有必要首先去做NULL到NOT NULL的優(yōu)化。

來通過實(shí)際例子看看最后一個(gè)問題。假如需要查詢上述test表中c1不為空的所有數(shù)據(jù)，應(yīng)該使用"<> ‘’"呢？還是使用"IS NOT NULL"呢？測(cè)試結(jié)果如下：

mysql> select * from test where c1 is not null;
+------+------+
| c1   | c2   |
+------+------+
|      | 1    |
| null | 1    |
|      | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test where c1 <> '';
+------+------+
| c1   | c2   |
+------+------+
| null | 1    |
+------+------+
1 row in set (0.00 sec)

可以看到，不同的查詢條件，對(duì)于查詢的結(jié)果區(qū)別還是特別大的。所以在使用過程中需要根據(jù)業(yè)務(wù)場(chǎng)景，選擇不同的查詢條件。

附錄

附錄1：MySQL索引失效的常見情況

1. 最左前綴原則。例如：存在聯(lián)合索引idx_a_b(a, b)，查詢條件使用where b = 1則無法使用索引
2. LIKE 前置模糊查詢。例如：col_name like '%test' 或 col_name like '%test%'
3. 索引列使用函數(shù)或存在計(jì)算。例如：存在索引idx_col(col)，查詢條件使用where left(col, 2) = 'te'
4. 查詢條件使用 is not null。設(shè)計(jì)表結(jié)構(gòu)時(shí)盡量設(shè)置 not null約束
5. 字段類型出現(xiàn)隱式轉(zhuǎn)換。例如：存在字段test_col為varchar類型，查詢時(shí)使用了where test_col = 1，隱式轉(zhuǎn)為了int類型導(dǎo)致索引失效
6. 條件中有 or 存在可能不會(huì)使用索引。例如：查詢條件為where a = 'testa' or b = 'testb'，存在索引idx_a(a)，此時(shí)也不會(huì)使用索引，除非為b字段也添加索引
7. 查詢結(jié)果超過整體結(jié)果的25%或三分之一，或者表數(shù)據(jù)量比較少時(shí)，MySQL認(rèn)為全表掃描代價(jià)更小，會(huì)導(dǎo)致索引失效

附錄2：MySQL關(guān)鍵字列表

R 表示為 MySQL 預(yù)留關(guān)鍵字文章來源地址http://www.zghlxwxcb.cn/news/detail-480488.html

到了這里，關(guān)于【MySQL】 IS NOT NULL 和 != NULL 的區(qū)別？的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！