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'