Java從零開始學 - 第68篇:細說NULL導致的神坑,讓人防不勝防

2021-03-02 Java研究所

這是Mysql系列第13篇。

環境：mysql5.7.25，cmd命令中進行演示。

當數據的值為NULL的時候，可能出現各種意想不到的效果，讓人防不勝防，我們來看看NULL導致的各種神坑，如何避免？

比較運算符中使用NULL

認真看下面的效果

mysql> select 1>NULL;
+---+
| 1>NULL |
+---+
|   NULL |
+---+
1 row in set (0.00 sec)

mysql> select 1<NULL;
+---+
| 1<NULL |
+---+
|   NULL |
+---+
1 row in set (0.00 sec)

mysql> select 1<>NULL;
+----+
| 1<>NULL |
+----+
|    NULL |
+----+
1 row in set (0.00 sec)

mysql> select 1>NULL;
+---+
| 1>NULL |
+---+
|   NULL |
+---+
1 row in set (0.00 sec)

mysql> select 1<NULL;
+---+
| 1<NULL |
+---+
|   NULL |
+---+
1 row in set (0.00 sec)

mysql> select 1>=NULL;
+----+
| 1>=NULL |
+----+
|    NULL |
+----+
1 row in set (0.00 sec)

mysql> select 1<=NULL;
+----+
| 1<=NULL |
+----+
|    NULL |
+----+
1 row in set (0.00 sec)

mysql> select 1!=NULL;
+----+
| 1!=NULL |
+----+
|    NULL |
+----+
1 row in set (0.00 sec)

mysql> select 1<>NULL;
+----+
| 1<>NULL |
+----+
|    NULL |
+----+
1 row in set (0.00 sec)

mysql> select NULL=NULL,NULL!=NULL;
+-+--+
| NULL=NULL | NULL!=NULL |
+-+--+
|      NULL |       NULL |
+-+--+
1 row in set (0.00 sec)

mysql> select 1 in (null),1 not in (null),null in (null),null not in (null);
+---+--+-++
| 1 in (null) | 1 not in (null) | null in (null) | null not in (null) |
+---+--+-++
|        NULL |            NULL |           NULL |               NULL |
+---+--+-++
1 row in set (0.00 sec)

mysql> select 1=any(select null),null=any(select null);
++---+
| 1=any(select null) | null=any(select null) |
++---+
|               NULL |                  NULL |
++---+
1 row in set (0.00 sec)

mysql> select 1=all(select null),null=all(select null);
++---+
| 1=all(select null) | null=all(select null) |
++---+
|               NULL |                  NULL |
++---+
1 row in set (0.00 sec)

結論：任何值和NULL使用運算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all）比較時，返回值都為NULL，NULL作為布爾值的時候，不為1也不為0。

準備數據

mysql> create table test1(a int,b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values (1,1),(1,null),(null,null);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+-+-+
| a    | b    |
+-+-+
|    1 |    1 |
|    1 | NULL |
| NULL | NULL |
+-+-+
3 rows in set (0.00 sec)

上面3條數據，認真看一下，特別是注意上面NULL的記錄。

IN、NOT IN和NULL比較 IN和NULL比較

mysql> select * from test1;
+-+-+
| a    | b    |
+-+-+
|    1 |    1 |
|    1 | NULL |
| NULL | NULL |
+-+-+
3 rows in set (0.00 sec)

mysql> select * from test1 where a in (null);
Empty set (0.00 sec)

mysql> select * from test1 where a in (null,1);
+-+-+
| a    | b    |
+-+-+
|    1 |    1 |
|    1 | NULL |
+-+-+
2 rows in set (0.00 sec)

結論：當IN和NULL比較時，無法查詢出為NULL的記錄。

NOT IN 和NULL比較

mysql> select * from test1 where a not in (1);
Empty set (0.00 sec)

mysql> select * from test1 where a not in (null);
Empty set (0.00 sec)

mysql> select * from test1 where a not in (null,2);
Empty set (0.00 sec)

mysql> select * from test1 where a not in (2);
+-+-+
| a    | b    |
+-+-+
|    1 |    1 |
|    1 | NULL |
+-+-+
2 rows in set (0.00 sec)

結論：當NOT IN 後面有NULL值時，不論什麼情況下，整個sql的查詢結果都為空。

EXISTS、NOT EXISTS和NULL比較

mysql> select * from test2;
+-+-+
| a    | b    |
+-+-+
|    1 |    1 |
|    1 | NULL |
| NULL | NULL |
+-+-+
3 rows in set (0.00 sec)

mysql> select * from test1 t1 where exists (select * from test2 t2 where t1.a = t2.a);
+-+-+
| a    | b    |
+-+-+
|    1 |    1 |
|    1 | NULL |
+-+-+
2 rows in set (0.00 sec)

mysql> select * from test1 t1 where not exists (select * from test2 t2 where t1.a = t2.a);
+-+-+
| a    | b    |
+-+-+
| NULL | NULL |
+-+-+
1 row in set (0.00 sec)

上面我們複製了表test1創建了表test2。

查詢語句中使用exists、not exists對比test1.a=test2.a，因為=不能比較NULL，結果和預期一致。

判斷NULL只能用IS NULL、IS NOT NULL

mysql> select 1 is not null;
++
| 1 is not null |
++
|             1 |
++
1 row in set (0.00 sec)

mysql> select 1 is null;
+-+
| 1 is null |
+-+
|         0 |
+-+
1 row in set (0.00 sec)

mysql> select null is null;
+----+
| null is null |
+----+
|            1 |
+----+
1 row in set (0.00 sec)

mysql> select null is not null;
+---+
| null is not null |
+---+
|                0 |
+---+
1 row in set (0.00 sec)

看上面的效果，返回的結果為1或者0。

結論：判斷是否為空只能用IS NULL、IS NOT NULL。

聚合函數中NULL的坑 示例

mysql> select count(a),count(b),count(*) from test1;
++++
| count(a) | count(b) | count(*) |
++++
|        2 |        1 |        3 |
++++
1 row in set (0.00 sec)

count(a)返回了2行記錄，a欄位為NULL的沒有統計出來。

count(b)返回了1行記錄，為NULL的2行記錄沒有統計出來。

count(*)可以統計所有數據，不論欄位的數據是否為NULL。

再繼續看

mysql> select * from test1 where a is null;
+-+-+
| a    | b    |
+-+-+
| NULL | NULL |
+-+-+
1 row in set (0.00 sec)

mysql> select count(a) from test1 where a is null;
++
| count(a) |
++
|        0 |
++
1 row in set (0.00 sec)

上面第1個sql使用is null查詢出了結果，第2個sql中count(a)返回的是0行。

結論：count(欄位)無法統計欄位為NULL的值，count(*)可以統計值為null的行。

NULL不能作為主鍵的值

mysql> create table test3(a int primary key,b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test3 values (null,1);
ERROR 1048 (23000): Column 'a' cannot be null

上面我們創建了一個表test3，欄位a未指定不能為空，插入了一條NULL的數據，報錯原因：a 欄位的值不能為NULL，我們看一下表的創建語句：

mysql> show create table test3;
+--+--+
| Table | Create Table      |
+--+--+
| test3 | CREATE TABLE `test3` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
+--+--+
1 row in set (0.00 sec)

從上面的腳本可以看出，當欄位為主鍵的時候，欄位會自動設置為not null。

結論：當欄位為主鍵的時候，欄位會自動設置為not null。

看了上面這些還是比較暈，NULL的情況確實比較難以處理，容易出錯，最有效的方法就是避免使用NULL。所以，強烈建議創建欄位的時候欄位不允許為NULL，設置一個默認值。

總結

NULL作為布爾值的時候，不為1也不為0

任何值和NULL使用運算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all），返回值都為NULL

當IN和NULL比較時，無法查詢出為NULL的記錄

當NOT IN 後面有NULL值時，不論什麼情況下，整個sql的查詢結果都為空

判斷是否為空只能用IS NULL、IS NOT NULL

count(欄位)無法統計欄位為NULL的值，count(*)可以統計值為null的行

當欄位為主鍵的時候，欄位會自動設置為not null

NULL導致的坑讓人防不勝防，強烈建議創建欄位的時候欄位不允許為NULL，給個默認值

Mysql系列目錄

第1篇：mysql基礎知識

第2篇：詳解mysql數據類型（重點）

第3篇：管理員必備技能(必須掌握)

第4篇：DDL常見操作

第5篇：DML操作匯總（insert,update,delete）

第6篇：select查詢基礎篇

第7篇：玩轉select條件查詢，避免採坑

第8篇：詳解排序和分頁(order by & limit)

第9篇：分組查詢詳解（group by & having）

第10篇：常用的幾十個函數詳解

第11篇：深入了解連接查詢及原理

第12篇：子查詢（非常重要，高手必備）

相關焦點

  • Java從零開始學 - 第70篇:詳解視圖
    當對視圖進行更新後，也會對原始表的數據進行更新。為了防止對原始表的數據產生更新，可以為視圖添加只讀權限，只允許讀視圖，不允許對視圖進行更新。一般情況下，極少對視圖進行更新操作。email like 'javacode2018%';/*修改*/UPDATE myv4 SET last_name = '劉德華' WHERE last_name = '路人甲Java';SELECT * from myv4 where email like 'javacode2018%';/*刪除*/DELETE FROM myv4 where
  • Java 開發中如何正確踩坑
    這個手冊目的就是讓我們儘可能少踩坑，杜絕踩重複的坑。我接下來就打算試著寫一些「坑」出來，來看看我們如何一不留神踩坑的，以及如何用正確的姿勢跳出坑。隨隨便便寫出 NPE首先聲明一個 User 對象，接下來所有代碼可能都會用到這個對象做演示，在下面將不在贅述。
  • 零基礎學Java的幾大必知問題!
    我是：小職（z_zhizuobiao）找我：✅ 解鎖高薪工作 ✅ 免費獲取乾貨教程Java是一門永不言敗的開發語言，隨著軟體行業的興盛，現在學習java的人員也是越來越多了，但是想要學好java的話自學是沒有那麼容易的事情。特別是對於零基礎的學員來，所以對於零基礎的學員來說都是要參加java培訓機構才是最合適的學習方式。
  • Java筆試面試總結—try、catch、finally語句中有return 的各類情況
    正文本篇文章主要是通過舉例的方式來闡述各種情況，我這裡根據 try-catch-finally 語法塊分為兩種大情況討論：try-catch 語法塊和 try-catch-finally 語句塊，然後再在每種情況裡再去具體討論。
  • 看完一個在校大學生的 Java 學習歷程,我覺得我還能學得更多
    但是，當我今年開始準備春招準備校招的時候，我發現我錯了，和真正優秀的人比起來，我什麼都不是。以前覺得自己會的東西，出去一看，大家其實都會；以前覺得很多知識等工作了再去學也不遲，出去一看，發現早就有人學會了。但是，現在認識到這些已經有點晚了，因為已經開始找工作了，沒有時間再去讓你從頭開始學了。所以說，永遠不要自滿，要知道人外有人。
  • 從零開始學Excel BI,助你在職場呼風喚雨
    小編今天帶來的《Excel BI之道：從零開始學Power工具應用》書是由零一在總結自身經驗的基礎上寫出來的，主要介紹 Excel2016
  • Spring 事務的那些坑,都在這裡了!
    第三部分通過實際測試代碼介紹關於Spring事務的坑；第四部分是對本文的總結。= null ? AopUtils.getTargetClass(invocation.getThis()) : null);    // Adapt to TransactionAspectSupport's invokeWithinTransaction...
  • 從零開始學習C語言,一起衝衝衝!
    每年都有很多跨考和零基礎的同學考計算機的研究生，而準備計算機考研的第一步就是入門
  • Spring事務的那些坑,這裡都給你總結好了!
    為了更透徹的說明這些坑，本文分四部分展開闡述：第一部分簡單介紹下Spring事務集成的幾種方式；第二部分結合Spring原始碼說明Spring事務的實現原理；第三部分通過實際測試代碼介紹關於Spring事務的坑；第四部分是對本文的總結。
  • 細說 Java Overload 與 Override 差別
    （給ImportNew加星標，提高Java技能）編譯：ImportNew/唐尤華software.rajivprab.com/2019/08/14/nuances-of-overloading-and-overriding-in-java
  • 永遠從零開始(深度好文)
    從零開始這四個字，光是看著就犯怵，畢竟，任何一個人選擇從零開始，都需要莫大的勇氣。前段時間和同學老林聊了個天，感觸頗多。同學老林現在是個3歲孩子的爸。以前的他性格豪爽，做事膽大心細，帶領的團隊業績幾乎都是數一數二。後來，迫於家庭和年齡的壓力，他選擇和老家一個小他5歲的女人相親結婚，做了點小生意。
  • 練習07.09|每日一練Java編程筆試面試題
    1、MonkeyEatPeach.java
  • 藍橋杯軟體學院:學會java、學好java之一,先決條件
    今天藍橋杯軟體學院鄭老師為大家分享學習方法：學會java、學好java之一，先決條件。希望大家能夠喜歡：這篇文章是談學習方法，只是以java為例罷了，有心人可以將我談到的觀點應用得更為廣泛。首先，你適合學習這門技術嗎？很多人不知道。那是因為你不了解目標對象，也不了解自己。
  • JAVA面試之WEB、框架及網絡篇
    寫著寫著家明又開始思考人生了。
  • 打工人防加班辦公技巧合集(第2期)
    本公眾號開通了流量主, 點擊文章底部的小卡片, 就可以請作者喝一杯 速溶咖啡~往期推薦打工人防加班辦公技巧合集（第1期）推薦一個擁有5T電子書純資源網頁（可IDM下載）IDM最新版本6.38安裝包（附安裝教程）點擊文末  閱讀原文
  • 人防救援志願者必備技能(證書)之聯合國安保部BSAFE證書
    聯合國安保部BSAFE證書獲得指南    南京人防應急救援志願大隊所有應急志願者及隊員都需要獲取BSAFE
  • Java枚舉:小小enum,優雅而乾淨
    就是本篇的主角——枚舉（Enum）——大家鼓掌了。在之前很長時間一段時間裡，我都不怎麼用枚舉，因為總感覺它沒什麼用處——這其實就是「自我認知」的短見。當一個人一直蹲在自己的深井裡而不敢跳出來的話，那他真的只能看到井口那麼大點的天空。
  • 10道棘手的Java面試題,看看你能答對幾個?
    如果您對原文感興趣，也可以通過這個連結查看：https://levelup.gitconnected.com/tricky-java-interview-questions-cfc546fd03abQ1：下面Java代碼的輸出是什麼？