比較運算子中使用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)123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103

結論：任何值和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: 0mysql> select * from test1;+------+------+| a    | b    |+------+------+|    1 |    1 ||    1 | NULL || NULL | NULL |+------+------+3 rows in set (0.00 sec)12345678910111213141516

上面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)123456789101112131415161718192021

結論：當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)1234567891011121314151617

結論：當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)1234567891011121314151617181920212223242526

上面我們複製了表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)12345678910111213141516171819202122232425262728293031

看上面的效果，返回的結果為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)1234567

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)123456789101112131415

上面第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 null12345

上面我們建立了一個表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)1234567891011

從上面的指令碼可以看出，當欄位為主鍵的時候，欄位會自動設定為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，給個預設值