比較運算符中使用NULL
mysql> select 1>NULL; +--------+ | 1>NULL | +--------+ | NULL | +--------+ 1 row in set (0.00 sec) mysql> select 1NULL; +--------+ | 1NULL | +--------+ | 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 1NULL; +--------+ | 1NULL | +--------+ | 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使用運算符（>、、>=、=、!=、>）或者（in、not in、any/some、all）比較時，返回值都為NULL，NULL作為布爾值的時候，不為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: 0 mysql> 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比較時，無法查詢出為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值時，不論什么情況下，整個sql的查詢結(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。
查詢語句中使用exists、not exists對比test1.a=test2.a，因為=不能比較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)
再繼續(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個sql使用is null查詢出了結(jié)果，第2個sql中count(a)返回的是0行。
結(jié)論：count(字段)無法統(tǒng)計字段為NULL的值，count(*)可以統(tǒng)計值為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)建了一個表test3，字段a未指定不能為空，插入了一條NULL的數(shù)據(jù)，報錯原因：a 字段的值不能為NULL，我們看一下表的創(chuàng)建語句：
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è)置為not null。
結(jié)論：當(dāng)字段為主鍵的時候，字段會自動設(shè)置為not null。
看了上面這些還是比較暈，NULL的情況確實比較難以處理，容易出錯，最有效的方法就是避免使用NULL。所以，強(qiáng)烈建議創(chuàng)建字段的時候字段不允許為NULL，設(shè)置一個默認(rèn)值。
總結(jié)
到此這篇關(guān)于Mysql NULL導(dǎo)致的神坑的文章就介紹到這了,更多相關(guān)Mysql NULL導(dǎo)致坑內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
標(biāo)簽：合肥 公主嶺 牡丹江 惠州 天津 呼和浩特 沈陽 阿里
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Mysql NULL導(dǎo)致的神坑》，本文關(guān)鍵詞 Mysql,NULL,導(dǎo)致,的,神坑,Mysql,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
