學(xué)習(xí)過關(guān)系型數(shù)據(jù)庫的伙伴都知道楼眷,NULL是指不確定的值之景,在數(shù)據(jù)庫中絕對(duì)是噩夢(mèng)的存在斤富;而空值,一般對(duì)字符串類型而言锻狗,指沒有任何值的字符串類型满力。MySQL中的NULL值和空值區(qū)別:NULL值是未知的,占用空間轻纪,并且不走索引油额。相信很多用MySQL很久的人,對(duì)這兩個(gè)字段屬性的概念還不是很清楚:
1刻帚、我字段類型是not null潦嘶,為什么我可以插入空值?
2崇众、為什么not null的效率比null高掂僵?
3航厚、判斷字段不為空的時(shí)候,到底要select * from table where column <> ''還是要用select * from table where column is not null呢锰蓬?
帶著上面幾個(gè)疑問幔睬,我們來深入研究一下null和not null到底有什么不一樣。
首先芹扭,我們要搞清楚“空值” 和 “NULL” 的概念:
1麻顶、空值是不占用空間的。
2冯勉、MySQL中的NULL其實(shí)是占用空間的澈蚌,下面是來自于MYSQL官方的解釋
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
打個(gè)比方來說,你有一個(gè)杯子灼狰,空值代表杯子是真空的,NULL代表杯子中裝滿了空氣浮禾,雖然杯子看起來都是空的交胚,但是區(qū)別是很大的。
搞清楚“空值”和“NULL”的概念之后盈电,問題基本就明了蝴簇,例子測(cè)試一下:
CREATE TABLE `test` (
`col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
) ENGINE = INNODB;
插入數(shù)據(jù):
mysql> INSERT INTO `test` VALUES (NULL,1);
ERROR 1048 (23000): Column 'col1' cannot be null
MySQL發(fā)生錯(cuò)誤,再來一條:
mysql> INSERT INTO `test` VALUES ('NULL',1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `test` VALUES ('',1);
Query OK, 1 row affected (0.01 sec)
成功插入匆帚。
可見熬词,NOT NULL的字段是不能插入NULL的,只能插入空值或有值吸重,上面的問題1也就有答案了互拾。另外,我這里是'NULL'相當(dāng)于一個(gè)字符串了嚎幸,不是NULL值颜矿,比如IS NULL是查不出來的。
mysql> select * from test where col1 is null;
Empty set (0.00 sec)
而等于’NULL’可以查詢:
mysql> select * from test where col1='null';
+------+------+
| col1 | col2 |
+------+------+
| NULL | 1????|
+------+------+
1 row in set (0.00 sec)
對(duì)于問題2嫉晶,上面我們已經(jīng)說過了骑疆,NULL其實(shí)并不是空值,而是要占用空間替废,所以MySQL在進(jìn)行比較的時(shí)候箍铭,NULL會(huì)參與字段比較,所以對(duì)效率有一部分影響椎镣。
而且對(duì)表索引時(shí)不會(huì)存儲(chǔ)NULL值的诈火,所以如果索引的字段可以為NULL,索引的效率會(huì)下降很多衣陶。
我們?cè)傧騮est的表中插入幾條數(shù)據(jù):
mysql> INSERT INTO `test` VALUES ('', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test` VALUES ('1', '2');
Query OK, 1 row affected (0.00 sec)
現(xiàn)在表中數(shù)據(jù):
現(xiàn)在根據(jù)需求柄瑰,我要統(tǒng)計(jì)test表中col1不為空的所有數(shù)據(jù)闸氮,我是該用“<> ”” 還是 “IS NOT NULL” 呢,讓我們來看一下結(jié)果的區(qū)別教沾。
mysql> select * from test where col1 is not null;
Empty set (0.00 sec)
mysql> select * from test where col2 is null;
+------+------+
| col1 | col2 |
+------+------+
|??????| NULL |
+------+------+
1 row in set (0.00 sec)
mysql> select * from test where col1 <> '';
+------+------+
| col1 | col2 |
+------+------+
| NULL | 1????|
| 1????| 2????|
+------+------+
2 rows in set (0.00 sec)
可以看到蒲跨,結(jié)果迥然不同,所以我們一定要根據(jù)業(yè)務(wù)需求授翻,搞清楚到底是要用那種搜索條件或悲。
MySQL數(shù)據(jù)庫是一個(gè)基于結(jié)構(gòu)化數(shù)據(jù)的開源數(shù)據(jù)庫,SQL語句是MySQL數(shù)據(jù)庫中核心語言堪唐。不過在MySQL數(shù)據(jù)庫中執(zhí)行SQL語句巡语,需要小心兩個(gè)陷阱。
陷阱一:空值不一定為空
空值是一個(gè)比較特殊的字段淮菠。在MySQL數(shù)據(jù)庫中男公,在不同的情形下,空值往往代表不同的含義合陵。這是MySQL數(shù)據(jù)庫的一種特性枢赔。如在普通的字段中(字符型的數(shù)據(jù)),空值就是表示空值拥知。但是如果將一個(gè)空值的數(shù)據(jù)插入到TimesTamp類型的字段中踏拜,空值就不一定為空。此時(shí)為出現(xiàn)什么情況呢低剔?
我先創(chuàng)建了一個(gè)表速梗。在這個(gè)表中有兩個(gè)字段:User_id(其數(shù)據(jù)類型是int)、Date(其數(shù)據(jù)類型是TimesTamp)〗蟪荩現(xiàn)在往這個(gè)表中插入一條記錄姻锁,其中往Date字段中插入的是一個(gè)NULL空值∪锾疲可是當(dāng)我們查詢時(shí)屋摔,其結(jié)果顯示的卻是插入記錄的當(dāng)前時(shí)間。這是怎么一回事呢?其實(shí)這就是在MySQL數(shù)據(jù)庫中執(zhí)行SQL語句時(shí)經(jīng)常會(huì)遇到的一個(gè)陷阱:空值不一定為空替梨。在操作時(shí)钓试,明明插入的是一個(gè)空值的數(shù)據(jù),但是最后查詢得到的卻不是一個(gè)空值副瀑。
在MySQL數(shù)據(jù)庫中弓熏,NULL對(duì)于一些特殊類型的列來說,其代表了一種特殊的含義糠睡,而不僅僅是一個(gè)空值挽鞠。對(duì)于這些特殊類型的列,主要是要記住兩個(gè)。一個(gè)就是筆者上面舉的TimesTamp數(shù)據(jù)類型信认。如果往這個(gè)數(shù)據(jù)類型的列中插入Null值材义,則其代表的就是系統(tǒng)的當(dāng)前時(shí)間。另外一個(gè)是具有auto_increment屬性的列嫁赏。如果往這屬性的列中插入Null值的話其掂,則系統(tǒng)會(huì)插入一個(gè)正整數(shù)序列。而如果在其他數(shù)據(jù)類型中潦蝇,如字符型數(shù)據(jù)的列中插入Null的數(shù)據(jù)款熬,則其插入的就是一個(gè)空值。
陷阱二:空值不一定等于空字符
在MySQL中攘乒,空值(Null)與空字符(’’)相同嗎贤牛?答案是否定的。
在同一個(gè)數(shù)據(jù)庫表中则酝,同時(shí)插入一個(gè)Null值的數(shù)據(jù)和一個(gè)’’空字符的數(shù)據(jù)殉簸,然后利用Select語句進(jìn)行查詢。顯然其顯示的結(jié)果是不相同的堤魁。從這個(gè)結(jié)果中就可以看出喂链,空值不等于空字符。這就是在MySQL中執(zhí)行SQL語句遇到的第二個(gè)陷阱妥泉。在實(shí)際工作中,空值數(shù)據(jù)與空字符往往表示不同的含義洞坑。數(shù)據(jù)庫管理員可以根據(jù)實(shí)際的需要來進(jìn)行選擇盲链。如對(duì)于電話號(hào)碼等字段,可以默認(rèn)設(shè)置為空值(表示根本不知道對(duì)方的電話號(hào)碼)或者設(shè)置為空字符(表示后來取消了這個(gè)號(hào)碼)等等迟杂。由于他們?cè)跀?shù)據(jù)庫中會(huì)有不同的表現(xiàn)形式刽沾,所以數(shù)據(jù)庫管理員需要區(qū)別對(duì)待。筆者更加喜歡使用空值排拷,而不是空字符侧漓。這主要是因?yàn)獒槍?duì)空值這個(gè)數(shù)據(jù)類型有幾個(gè)比較特殊的運(yùn)算字符。如果某個(gè)字段是空字符监氢,數(shù)據(jù)庫中是利用字段名稱來代替布蔗。相反,如果插入的是空值浪腐,則直接顯示的是NULL纵揍。這跟其他數(shù)據(jù)庫的顯示方式也是不同的。
IS NULL和IS NOT NULL關(guān)鍵字议街。如果要判斷某個(gè)字段是否含用空值的數(shù)據(jù)泽谨,需要使用特殊的關(guān)鍵字。其中前者表示這個(gè)字段為空,后者表示這個(gè)字段為非空吧雹。在Select語句的查詢條件中這兩個(gè)關(guān)鍵字非常的有用骨杂。如需要查詢所有電話號(hào)碼為空的用戶(需要他們補(bǔ)充電話號(hào)碼信息),就可以在查詢條件中加入is not null關(guān)鍵字雄卷。判斷NULL用is null或者is not null搓蚪,SQL語句里可以用ifnull函數(shù)來處理。判斷空字符串‘’龙亲,要用=”或者<>”陕凹,SQL語句里可以用if(col,col,0)處理,即:當(dāng)col為true時(shí)(非null鳄炉,及非”)顯示杜耙,否則打印0。
一般情況下拂盯,除了count(0)佑女,count(*)之外,聚合函數(shù)都會(huì)忽略NULL值谈竿,而統(tǒng)計(jì)非NULL值团驱。另外空表也會(huì)產(chǎn)生結(jié)果為NULL的聚合值。當(dāng)聚合列值都是NULL值時(shí)空凸,由于聚合函數(shù)忽略NULL值嚎花,因此,當(dāng)計(jì)算聚合函數(shù)(max呀洲,min紊选,avg和sum)的聚合值時(shí),由于無值可以聚合道逗,數(shù)據(jù)庫引擎不能確定這些聚合函數(shù)的返回值兵罢,因此,數(shù)據(jù)庫引擎返回NULL值滓窍。
mysql> create table temp(id int);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into temp values(null);
Query OK, 1 row affected (0.01 sec)
mysql> select count(0),count(id),max(id),min(id),avg(id),sum(id) from temp;
+----------+-----------+---------+---------+---------+---------+
| count(0) | count(id) | max(id) | min(id) | avg(id) | sum(id) |
+----------+-----------+---------+---------+---------+---------+
|????????1 |???????? 0 |????NULL |????NULL |????NULL |????NULL |
+----------+-----------+---------+---------+---------+---------+
1 row in set (0.00 sec)
聚合函數(shù)(max,min,sum,avg和count)忽略null值卖词,但不代表聚合函數(shù)不返回null值:如果數(shù)據(jù)表為空表,或聚合列值都是null吏夯,那么max,min,sum,avg聚合函數(shù)返回null值此蜈,而count 聚合函數(shù)返回0。聚合函數(shù)的共性:Null values are ignored锦亦。
不再迷惑:當(dāng)不返回任何值時(shí)舶替,數(shù)據(jù)庫引擎不確定返回值,就把無值轉(zhuǎn)換為NULL值杠园。
轉(zhuǎn)自:http://www.ywnds.com/?p=10295