MySQL数据库

SQL 中的 NULL 详解

1. NULL 的定义

在 SQL 中，NULL 是一个特殊的逻辑值，表示“未知”。与编程语言中只有 TRUE 和 FALSE 两种逻辑值不同，SQL 中的 NULL 代表“不确定”的状态。
NULL 可以分配给 TEXT、INTEGER 或其他任意数据类型的列，但如果列被声明为 NOT NULL，则不能包含 NULL 值。

示例：

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

• 此处 F_Name 被设置为空字符串 ""，因为我们知道 Homer 和 Sting 没有名字。
• Birth_place 设置为 NULL，因为我们不知道他们的出生地。

2. 检查 NULL

要检查列中是否包含 NULL 值，不能使用常规的比较操作符（如 =），而是使用以下方法：

1. 检查是否为 NULL：

   SELECT * FROM Singer WHERE Birth_place IS NULL;
   

2. 检查是否不为 NULL：

   SELECT * FROM Singer WHERE Birth_place IS NOT NULL;
   

3. 使用函数 ISNULL：

   SELECT * FROM Singer WHERE ISNULL(Birth_place);
   

3. NULL 的行为特点

1. COUNT 不会统计 NULL 值：

   SELECT COUNT(Birth_place) FROM Singer;
   -- 返回结果：0
   

2. SUM 对 NULL 的结果是 NULL：

   SELECT SUM(NULL);
   -- 返回结果：NULL
   

3. 涉及 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
   

4. 处理 NULL 的方法

4.1 COALESCE 函数

COALESCE 函数返回第一个非 NULL 的值。可以用于将 NULL 转换为默认值。

示例：

1. 将 NULL 转换为 0：

   SELECT COALESCE(colname, 0)
   FROM table
   WHERE COALESCE(colname, 0) > 1;
   

2. 在日期字段中，将 NULL 视为当前日期：

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

3. 在联结中处理 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;
   

4.2 IFNULL 函数

IFNULL(expr1, expr2) 函数在 expr1 不为 NULL 时返回 expr1，否则返回 expr2。

示例：

SELECT IFNULL(1, 0); -- 返回 1
SELECT IFNULL(NULL, 10); -- 返回 10
SELECT IFNULL(1/0, 10); -- 返回 10
SELECT IFNULL(1/0, 'yes'); -- 返回 'yes'

5. NULL 的排序

在 ORDER BY 中，可以使用 ISNULL 来控制 NULL 的排序。

示例：

SELECT * FROM my_table
ORDER BY ISNULL(field), field ASC;

• ISNULL(field) 将 NULL 值排在最前面。

6. 删除包含 NULL 的记录

如果在删除操作中使用错误的条件（如 field > NULL），可能会导致所有记录被删除。

示例（错误操作）：

DELETE FROM my_table WHERE field > NULL; -- 删除所有记录

7. 检查列是否允许 NULL

要查看表中哪些列不允许 NULL，可以查询 INFORMATION_SCHEMA.COLUMNS：

示例：

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

总结

• NULL 表示“未知”或“无值”，在逻辑上既不等于任何值，也不与任何值不等。
• 使用 IS NULL 或 IS NOT NULL 检查 NULL 值。
• 通过 COALESCE 和 IFNULL 函数处理 NULL 值，提供默认值或替代值。
• 注意 NULL 在聚合函数（如 COUNT、SUM）中的行为。
• 在排序或删除时，谨慎处理 NULL，避免意外结果。