MySQL/Language/Using NULL

概述

NULL是SQL的一個特殊值，同義"Unknown"。 NULL值可賦值、可比較。

 INSERT into Singer
        (F_Name, L_Name, Birth_place, Language) 
        values 
        ("", "Homer", NULL, "Greek"),
        ("", "Sting", NULL, "English"),
        ("Jonny", "Five", NULL, "Binary");

空字符串不是NULL。

與NULL比較：

 
 SELECT * from Singer WHERE Birth_place IS NULL;
 or
 SELECT * from Singer WHERE Birth_place IS NOT NULL;
 or
 SELECT * from Singer WHERE isNull(Birth_place)

如果某列存在 NULL 值時，如果在該列上執行「不等於」查詢，select結果不包含為 NULL 值的行。解決辦法是增加isnull(col_name)查詢條件。

COUNT函數指定列名時不計數NULL值所在的行，但指定參數為*則統計NULL：

 select count(Birth_place)，count(*) from Singer;
 0, 1

阿里巴巴《Java開發手冊》強制規定：不要使用 count(列名) 或 count(常量) 來替代 count()，count() 是 SQL92 定義的標準統計行數的語法，跟資料庫無關，跟 NULL 和非 NULL 無關。 說明：count(*) 會統計值為 NULL 的行，而 count(列名) 不會統計此列為 NULL 值的行。

使用 count(distinct col1, col2) 查詢時，如果其中一列為 NULL，那麼即使另一列有不同的值，那麼查詢的結果也會將數據丟失

使用sum在包含NULL的列上計算，結果為NULL 。 可使用select ifnull(sum(col_name), 0) from tbk_name

絕大多數操作符如果有一個操作數為NULL，結果也將是NULL：

 SELECT (NULL=NULL) OR (NULL<>NULL) OR (NOT NULL) OR (1<NULL) OR (1>NULL) OR (1 + NULL) OR (1 LIKE NULL)

COALESCE函數

函數COALESCE (expression_1, expression_2, ...,expression_n)從左至右依次計算各參數表達式，遇到非null值即停止計算並返回該值。如果所有的表達式都是空值，最終將返回一個空值。

例如，把空值當作數值0：

 SELECT COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

把NULL的日期當作當前日期：

 ORDER BY (COALESCE(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))

 EXP(SUM(LOG(COALESCE(''*the fieldName you want to multiply*'',1)))

 SELECT t4.gene_name, COALESCE(g2d.score,0), 
 COALESCE(dgp.score,0), COALESCE(pocus.score,0) 
 FROM t4 
 LEFT JOIN g2d ON t4.gene_name=g2d.gene_name 
 LEFT JOIN dgp ON t4.gene_name=dgp.gene_name 
 LEFT JOIN pocus ON t4.gene_name=pocus.gene_name;

IFNULL函數

用於判斷第一個表達式是否為 NULL，如果為 NULL 則返回第二個參數的值，如果不為 NULL 則返回第一個參數的值 (同COALESCE一樣，能做到在判斷第一個值為空的情況下，直接返回第二個值,不同的是ifnull只能傳兩個參數，而COALESCE可以傳遞多個參數)

 IFNULL(expr1,expr2)

如果expr1不為NULL, IFNULL()返回expr1, 否則返回expr2.

 mysql> SELECT IFNULL(1,0);
 -> 1
 mysql> SELECT IFNULL(NULL,10);
 -> 10
 mysql> SELECT IFNULL(1/0,10);
 -> 10.000
 mysql> SELECT IFNULL(1/0,'yes');
 -> 'yes'

isnull

判斷是不是null,若是則返回1，若不是返回0

nullif

若第二個參數等於第一個參數則返回null，否則返回第一次參數

反直覺的麻煩

空值處理可能是反直覺的。例如下述語句將刪除表中所有行：

 DELETE FROM my_table WHERE field > NULL --(or function returning NULL)

如果希望ORDER BY時讓空值排在最後：

 SELECT * FROM my_table ORDER BY ISNULL(field), field [ ASC | DESC ]

確定一張表中哪些列禁止為空：

SELECT *
FROM  `information_schema`.`COLUMNS`
WHERE IS_NULLABLE = 'NO' AND TABLE_NAME = 'my_table'