MySQL/語言/使用 NULL

描述

NULL 是 SQL 中的特殊邏輯值。大多數程式語言只有兩個邏輯值：真和假。SQL 還有 NULL，表示“未知”。NULL 值可以被設定。

NULL 是非值，因此可以將其分配給 TEXT 列、INTEGER 列或任何其他資料型別。僅當列被宣告為 NOT NULL 時，它才能不包含 NULL（參見 ALTER TABLE）。

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

不要引用 NULL。如果引用 NULL，那麼你就會命名名為 NULL 的人。由於某些奇怪的原因，NULL 在 Windows XP 的 Varchar 欄位中不會顯示，但在 Fedora 的版本中會顯示，因此 MySQL 的不同版本可能會產生不同的輸出。這裡我們將 Sting 和 Homer 的名字設定為零長度字串 “”，因為我們知道他們沒有名字，但我們也知道我們不知道他們的出生地方。要檢查 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)

記住，COUNT 從不計算 NULL。

 select count(Birth_place) from Singer;
 0
 and sum(NULL) gives a NULL answer.

如果至少一個比較項為 NULL，則正常操作（比較、表示式等）將返回 NULL

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

因為括號內的所有表示式都返回 NULL。這絕對合乎邏輯：如果你不知道 NULL 代表的值，你就不知道它是否等於 1 或不等於 1。請注意，即使 (NULL=NULL) 和 (NOT NULL) 也返回 NULL。

處理 NULL

'COALESCE' 函式可以簡化對 NULL 值的操作。例如，為了避免顯示 NULL 值，將 NULL 視為零，你可以鍵入

 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 field you want to multiply*'',1)))

coalesce() 函式是為了防止嘗試計算 NULL 值的對數，它可能取決於您的具體情況而可選擇使用。

 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;

在您的 SELECT 語句中使用 IFNULL() 是為了將 NULL 轉換為您想要的任何值。

 IFNULL(expr1,expr2)

如果 expr1 不為 NULL，則 IFNULL() 返回 expr1，否則返回 expr2。

IFNULL() 返回一個數值或字串值，具體取決於它使用的上下文。

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

NULL 處理可能非常違反直覺，如果您的刪除語句中有一個錯誤的函式返回 NULL，就會導致問題。例如，以下查詢將刪除所有條目。

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

如果您希望在執行 ORDER BY 時將 NULL 值排在最後，請嘗試以下方法

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

最後，要確定哪些表字段不能為 NULL

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