MySQL中空值與NULL的區(qū)別

學(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

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末顾瞪,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌陈醒,老刑警劉巖惕橙,帶你破解...
    沈念sama閱讀 211,948評(píng)論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場(chǎng)離奇詭異钉跷,居然都是意外死亡弥鹦,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,371評(píng)論 3 385
  • 文/潘曉璐 我一進(jìn)店門爷辙,熙熙樓的掌柜王于貴愁眉苦臉地迎上來彬坏,“玉大人,你說我怎么就攤上這事膝晾∷ㄊ迹” “怎么了?”我有些...
    開封第一講書人閱讀 157,490評(píng)論 0 348
  • 文/不壞的土叔 我叫張陵血当,是天一觀的道長(zhǎng)幻赚。 經(jīng)常有香客問我,道長(zhǎng)臊旭,這世上最難降的妖魔是什么落恼? 我笑而不...
    開封第一講書人閱讀 56,521評(píng)論 1 284
  • 正文 為了忘掉前任,我火速辦了婚禮离熏,結(jié)果婚禮上佳谦,老公的妹妹穿的比我還像新娘。我一直安慰自己滋戳,他們只是感情好吠昭,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,627評(píng)論 6 386
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著胧瓜,像睡著了一般。 火紅的嫁衣襯著肌膚如雪郑什。 梳的紋絲不亂的頭發(fā)上府喳,一...
    開封第一講書人閱讀 49,842評(píng)論 1 290
  • 那天,我揣著相機(jī)與錄音蘑拯,去河邊找鬼钝满。 笑死,一個(gè)胖子當(dāng)著我的面吹牛申窘,可吹牛的內(nèi)容都是我干的弯蚜。 我是一名探鬼主播,決...
    沈念sama閱讀 38,997評(píng)論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼剃法,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼碎捺!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 37,741評(píng)論 0 268
  • 序言:老撾萬榮一對(duì)情侶失蹤收厨,失蹤者是張志新(化名)和其女友劉穎晋柱,沒想到半個(gè)月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體诵叁,經(jīng)...
    沈念sama閱讀 44,203評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡雁竞,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,534評(píng)論 2 327
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了拧额。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片碑诉。...
    茶點(diǎn)故事閱讀 38,673評(píng)論 1 341
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖侥锦,靈堂內(nèi)的尸體忽然破棺而出进栽,到底是詐尸還是另有隱情,我是刑警寧澤捎拯,帶...
    沈念sama閱讀 34,339評(píng)論 4 330
  • 正文 年R本政府宣布泪幌,位于F島的核電站,受9級(jí)特大地震影響署照,放射性物質(zhì)發(fā)生泄漏祸泪。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,955評(píng)論 3 313
  • 文/蒙蒙 一建芙、第九天 我趴在偏房一處隱蔽的房頂上張望没隘。 院中可真熱鬧,春花似錦禁荸、人聲如沸右蒲。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,770評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽瑰妄。三九已至,卻和暖如春映砖,著一層夾襖步出監(jiān)牢的瞬間间坐,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,000評(píng)論 1 266
  • 我被黑心中介騙來泰國(guó)打工邑退, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留竹宋,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 46,394評(píng)論 2 360
  • 正文 我出身青樓地技,卻偏偏與公主長(zhǎng)得像蜈七,于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子莫矗,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,562評(píng)論 2 349

推薦閱讀更多精彩內(nèi)容

  • 什么是數(shù)據(jù)庫飒硅? 數(shù)據(jù)庫是存儲(chǔ)數(shù)據(jù)的集合的單獨(dú)的應(yīng)用程序砂缩。每個(gè)數(shù)據(jù)庫具有一個(gè)或多個(gè)不同的API,用于創(chuàng)建狡相,訪問梯轻,管理...
    chen_000閱讀 4,032評(píng)論 0 19
  • MySQL 數(shù)據(jù)庫常用命令 1、MySQL常用命令 create database name; 創(chuàng)建數(shù)據(jù)庫 use...
    55lover閱讀 4,780評(píng)論 1 57
  • Spring Cloud為開發(fā)人員提供了快速構(gòu)建分布式系統(tǒng)中一些常見模式的工具(例如配置管理尽棕,服務(wù)發(fā)現(xiàn)喳挑,斷路器,智...
    卡卡羅2017閱讀 134,633評(píng)論 18 139
  • 如果風(fēng)大滔悉,那么趕緊關(guān)窗伊诵。
    獅子巖蜘蛛閱讀 83評(píng)論 0 0
  • 自己最信奉的一句話莫過于:民以食為天。如果說什么事情可以讓我堅(jiān)持一生回官,那也莫過于是美食曹宴。吃只是個(gè)代表,其中還包含...
    木由游閱讀 262評(píng)論 0 0