比較運(yùn)算符中使用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使用運(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: 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比較時(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)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
您可能感興趣的文章:- 為什么mysql字段要使用NOT NULL
- MySQL中關(guān)于null值的一個(gè)小問(wèn)題
- MySQL null的一些易錯(cuò)點(diǎn)
- MySQL null與not null和null與空值''''''''的區(qū)別詳解
- MySQL IFNULL判空問(wèn)題解決方案
- 區(qū)分MySQL中的空值(null)和空字符('''')
- 詳解mysql不等于null和等于null的寫(xiě)法
- mysql中null(IFNULL,COALESCE和NULLIF)相關(guān)知識(shí)點(diǎn)總結(jié)
- 詳解mysql三值邏輯與NULL