MySQL中NULL和NOT NULL詳解

這篇小短文講的是啥

之前看到有人問到PHPHub 遷移資料庫檔案中nullable 和索引的問題，相信很多用了MySQL 很久的人（特別是平時過度關注業務開發的人），對這兩個字段屬性的概念還不是很清楚，一般會有以下疑問：

我字段類型是not null，為什麼我可以插入空值；

為毛not null 的效率比null 高；

判斷字段不為空的時候，到底要column '' 還是要用column is not null 呢。

帶著上面幾個疑問，我們來深入研究 null 和 not null 到底有什麼不一樣。

null 和空值一樣麼

首先，我們要搞清楚空值和null 的概念：

空值是不佔用空間的;

MySQL 中的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.

打個比方來說，你有一個杯子，空值代表杯子是真空的，NULL 代表杯子中裝滿了空氣，雖然杯子看起來都是空的，但是差異是很大的。

一個小栗子

搞清楚「空值」 和「NULL」 的概念之後，問題基本上就明了，我們搞個例子測試一下：

CREATE TABLE  `test` (
     `col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
     `col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
 ) ENGINE = MYISAM ;

問題1：我欄位類型是not null，為什麼我可以插入空值？

執行下面的 SQL，發生錯誤，提示 Column 'col1' cannot be null。

INSERT INTO `test` VALUES ( null, 1);

再來一條，執行成功。

INSERT INTO `test` VALUES ('',1);

可見，NOT NULL 的欄位是不能插入 NULL 的（這不是廢話麼  ），只能插入 空值，上面的問題 1 也就有答案了。

問題 2：為毛 not null 的效率比 null 高？

對於問題2，上面我們已經說過了，NULL 其實不是空值，而是要佔用空間，所以mysql 在進行比較的時候，NULL 會參與字段比較，所以對效率有一部分影響。

而且 B 樹索引時不會儲存 NULL 值的，所以如果索引的欄位可以是 NULL，索引的效率會下降很多。

問題 3：判斷字段不為空的時候，到底要 column<>'' 還是要用 column is not null 呢。

我們再向test 的表中插入幾條數據：

INSERT INTO `test` VALUES ('', NULL);
INSERT INTO `test` VALUES ('1', '2');

現在根據需求，我要統計test 表中col1 不為空的所有數據，我是該用<> ;'' 還是IS NOT NULL 呢，讓我們來看結果的差別。

現在表中的資料如下：

分別對照以下兩句SQL 的執行效果

SELECT * FROM `test` WHERE col1 IS NOT NULL

SELECT * FROM `test` WHERE col1 <> &#39;&#39;

可以看到，結果迥然不同，所以我們一定要根據業務需求，搞清楚到底是要用那種搜尋條件，以及要不要為null。

自己遇到的一個小坑

在許久以前剛入職做第一個需求上線的時候，只一味注意到說是not null 的效率比null 的效率高。

好嘛～自己在現有的表上增加字段時全設定為了 not null，感覺自己屌屌的。

因為許多 Service 都有操作這個表的插入動作，結果可想而知，剛上線，錯誤 Column 'col1' cannot be null 瀰漫了整個開發組每個人的郵箱。

所以，當業務量不是很大的情況下，很多技術的使用其實都需要根據實際狀況綜合考慮。

以上是MySQL中NULL和NOT NULL詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！