比較運(yùn)算符中使用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)

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

準(zhǔn)備數(shù)據(jù)

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)

上面3條數(shù)據(jù)，認(rèn)真看一下，特別是注意上面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)

結(jié)論：當(dāng)IN和NULL比較時(shí)，無(wú)法查詢(xún)出為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)

結(jié)論：當(dāng)NOT IN 后面有NULL值時(shí)，不論什么情況下，整個(gè)sql的查詢(xún)結(jié)果都為空。

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)

上面我們復(fù)制了表test1創(chuàng)建了表test2。

查詢(xún)語(yǔ)句中使用exists、not exists對(duì)比test1.a=test2.a，因?yàn)?不能比較NULL，結(jié)果和預(yù)期一致。

判斷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)

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

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

聚合函數(shù)中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的沒(méi)有統(tǒng)計(jì)出來(lái)。 count(b)返回了1行記錄，為NULL的2行記錄沒(méi)有統(tǒng)計(jì)出來(lái)。 count(*)可以統(tǒng)計(jì)所有數(shù)據(jù)，不論字段的數(shù)據(jù)是否為NULL。

再繼續(xù)看

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個(gè)sql使用is null查詢(xún)出了結(jié)果，第2個(gè)sql中count(a)返回的是0行。

結(jié)論：count(字段)無(wú)法統(tǒng)計(jì)字段為NULL的值，count(*)可以統(tǒng)計(jì)值為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

上面我們創(chuàng)建了一個(gè)表test3，字段a未指定不能為空，插入了一條NULL的數(shù)據(jù)，報(bào)錯(cuò)原因：a 字段的值不能為NULL，我們看一下表的創(chuàng)建語(yǔ)句：

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)

從上面的腳本可以看出，當(dāng)字段為主鍵的時(shí)候，字段會(huì)自動(dòng)設(shè)置為not null。

結(jié)論：當(dāng)字段為主鍵的時(shí)候，字段會(huì)自動(dòng)設(shè)置為not null。

看了上面這些還是比較暈，NULL的情況確實(shí)比較難以處理，容易出錯(cuò)，最有效的方法就是避免使用NULL。所以，強(qiáng)烈建議創(chuàng)建字段的時(shí)候字段不允許為NULL，設(shè)置一個(gè)默認(rèn)值。

總結(jié)

NULL作為布爾值的時(shí)候，不為1也不為0 任何值和NULL使用運(yùn)算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all），返回值都為NULL 當(dāng)IN和NULL比較時(shí)，無(wú)法查詢(xún)出為NULL的記錄 當(dāng)NOT IN 后面有NULL值時(shí)，不論什么情況下，整個(gè)sql的查詢(xún)結(jié)果都為空 判斷是否為空只能用IS NULL、IS NOT NULL count(字段)無(wú)法統(tǒng)計(jì)字段為NULL的值，count(*)可以統(tǒng)計(jì)值為null的行 當(dāng)字段為主鍵的時(shí)候，字段會(huì)自動(dòng)設(shè)置為not null NULL導(dǎo)致的坑讓人防不勝防，強(qiáng)烈建議創(chuàng)建字段的時(shí)候字段不允許為NULL，給個(gè)默認(rèn)值

到此這篇關(guān)于Mysql NULL導(dǎo)致的神坑的文章就介紹到這了,更多相關(guān)Mysql NULL導(dǎo)致坑內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！