MySQL系列关于NUll值的经验总结分析教程

1.娴嬭瘯鏁版嵁

create table test_null (
    id int,
    name varchar(20),
    chinese int,
    math int,
    english int
) charset=utf8;

insert into test_null 
values
(1,null,80,70,68),
(2,'寮犱笁',60,null,null),
(3,'鏉庡洓',null,90,80),
(4,'鐜嬩簲',90,60,75),
(5,null,null,50,95);

缁撴灉濡備笅锛?/p>

2.null鍊煎甫缁欐垜浠�鐨勪笉渚垮奖鍝�

1锛夎繃婊ゆ湁鎵�涓嶅悓锛屽彧鑳戒娇鐢╥s null鎴栬�卛s not null锛?/p>

# null鍊间笉鑳戒娇鐢?== 鎴?!= 鏉ユ瘮杈?
# 涓嬮潰涓ょ�嶇敤娉曢兘鏄�閿欒��鐨?
select *
from test_null
where name == null;

select *
from test_null
where name != null;

# null鍊间竴鑸�浣跨�?is null 鎴?is not null 鏉ユ瘮杈?
# 涓嬮潰涓ょ�嶇敤娉曟墠鏄�姝ｇ‘鐨?
select *
from test_null
where name is null;

select *
from test_null
where name is not null;

2锛夊嚭鐜皀ull鍊硷紝浼氬�艰�? - * /杩愮畻澶辨晥锛?/p>

select 	
	*,(chinese+math+english) as 鎬诲垎
from test_null;

缁撴灉濡備笅锛?/p>

3锛塶ull鍊煎�硅仛鍚堝嚱鏁版棤褰卞搷锛岃仛鍚堝嚱鏁颁細鐩存帴蹇界暐null鍊硷紱

select 
    sum(chinese) 璇�鏂囨�诲垎,
    sum(math) 鏁板�︽�诲垎,
    sum(english) 澶栬��鎬诲垎
from test_null

缁撴灉濡備笅锛?/p>

3.绌烘牸銆佺┖鍊煎拰null锛屾垜浠�搴旇�ユ�庝箞鍒ゆ柇鍛�锛�

1锛夌┖鏍笺�佺┖鍊煎拰null鐨勫尯鍒?/h3>

鐢ㄤ竴涓�褰㈣薄鐨勬瘮鍠绘潵璇存槑杩欎笁鑰呯殑鍖哄埆銆傞�栧厛绌烘牸寰堝ソ鐞嗚В锛屼竴涓�绌哄瓧绗︿覆鍚楋紝鍗犳嵁涓�瀹氱殑绌洪棿澶у皬銆備笉濂界悊瑙ｇ殑鍏跺疄鏄�绌哄�煎拰null锛岀┖鍊肩浉褰撲簬涓�涓�鏉�瀛愭槸鐪熺┖鐘舵�佺殑锛屼粈涔堜篃娌℃湁锛宯ull琛ㄧず鐨勬澂瀛愪腑鏈夌┖姘斻�?/p>

MySQL涓�锛�null鏄�鏈�鐭ョ殑锛屼笖鍗犵敤绌洪棿鐨勩�俷ull浣垮緱绱㈠紩銆佺储寮曠粺璁″拰鍊奸兘鏇村姞澶嶆潅锛屽苟涓斿奖鍝嶄紭鍖栧櫒鐨勫垽鏂�銆傜┖鍊?code>('')鏄�涓嶅崰鐢ㄧ┖闂寸殑锛屾敞鎰忕┖鍊肩殑''涔嬮棿鏄�娌℃湁绌烘牸鐨勩�傚湪杩涜��count()缁熻�℃煇涓�鍒楄�板綍鏁扮殑鏃跺�欙紝濡傛灉瀛樺湪null鍊硷紝浼氳��绯荤粺鑷�鍔ㄥ拷鐣ユ帀锛屼絾鏄�绌哄�间細琚�缁熻�″埌鍏朵腑銆傚垽鏂璶ull浣跨敤鐨勬槸is null鍜?is not null锛屼絾鍒ゆ柇绌哄瓧绗︿娇鐢ㄧ殑鏄?code>= 锛?=锛?<>鏉ヨ繘琛屽�勭悊銆傚�逛�?code>timestamp鏁版嵁绫诲瀷锛屽�傛灉鎻掑�?code>null鍊?/code>锛屽垯鍑虹幇鐨勫�兼槸褰撳墠绯荤粺鏃堕棿銆傛彃鍏ョ┖鍊硷紝鍒欎細鍑虹幇0000-00-00 00:00:00銆傚�逛簬宸茬粡鍒涘缓濂界殑琛�锛屾櫘閫氱殑鍒楀皢null淇�鏀逛�?code>not null甯︽潵鐨勬�ц兘鎻愬崌杈冨皬锛屾墍浠ヨ皟浼樻椂鍊欐病鏈夋瘮瑕佹敞鎰忋�?/p>

2锛夊嚭鐜颁簡null鍊硷紝鎴戝簲璇ユ�庝箞鍔烇紵

閫氳繃涓婇潰鐨勫垎鏋愭垜浠�宸茬粡鐭ラ亾浜嗭紝褰撹〃涓�瀛樺湪null鍊硷紝浼氬�艰嚧鍔犮�佸噺銆佷箻銆侀櫎杩愮畻澶辨晥銆傞偅涔堟垜浠�鎬庝箞澶勭悊杩欎簺null鍊兼瘮杈冨ソ鍛�锛�

绗�涓�绉嶆柟寮忥細鐩存帴浣跨敤is not null灏嗚繖浜沶ull鍊艰繃婊ゆ帀锛屼絾鏄�杩欐牱浼氬皢鍏跺畠闈炵己澶卞�肩殑瀛楁�佃繃婊ゆ帀锛岄�犳垚鏁版嵁鐨勬氮璐广�?/p>

绗�浜岀�嶆柟寮忥細涔熸槸鎴戜滑鎺ㄨ崘鐨勬柟寮忥紝鎴戜滑浣跨敤鍑芥暟杩涜�岀己澶卞�肩殑濉�鍏呫�?/p>

ifnull()鍜宑oalesce()鍑芥暟鐨勪娇鐢�锛�

select 
    	id,
    	coalesce(name,'鏃犲悕姘?) name,
    	coalesce(chinese,0) chinese,
    	ifnull(math,0) math,
    	ifnull(english,0) english
from test_null;

缁撴灉濡備笅锛?/p>

浠ヤ笂灏辨槸MySQL绯诲垪鍏充簬NUll鍊肩殑缁忛獙鎬荤粨鍒嗘瀽鐨勮�︾粏鍐呭�癸紝鏇村�氬叧浜嶮ySQL涓�鐨凬Ull鍊肩殑璧勬枡璇峰叧娉ㄨ剼鏈�涔嬪�跺叾瀹冪浉鍏虫枃绔狅紒