MySQL NULL导致的坑

在 MySQL 数据库中，NULL 代表 “未知” 或 “无值”，它的存在可能会给开发者带来一些意想不到的问题，以下是一些常见的由 NULL 导致的 “坑” 及相应的解决办法。

1. NULL 在比较运算中的特殊性

问题描述

在 MySQL 里，NULL 不能使用常规的比较运算符（如 =, !=, >, < 等）进行比较。因为 NULL 表示未知值，任何与 NULL 的比较操作结果都是 NULL，而不是 TRUE 或 FALSE。例如，执行 SELECT 1 = NULL; 或 SELECT 1 != NULL;，结果都会返回 NULL。这会导致在 WHERE 子句中使用这些比较运算符来筛选包含 NULL 值的记录时，无法得到预期的结果。

示例代码

假设有一个 employees 表，其中 salary 列可能包含 NULL 值，执行以下查询：

SELECT * FROM employees WHERE salary = NULL;

这个查询不会返回任何结果，即使 salary 列中存在 NULL 值。

解决办法

要判断一个值是否为 NULL，需要使用 IS NULL 或 IS NOT NULL 运算符。例如，要查询 salary 列为 NULL 的记录，可以使用：

SELECT * FROM employees WHERE salary IS NULL;

要查询 salary 列不为 NULL 的记录，可以使用：

SELECT * FROM employees WHERE salary IS NOT NULL;

2. NULL 对聚合函数的影响

问题描述

部分聚合函数（如 SUM, AVG, COUNT 等）在处理 NULL 值时会有不同的表现。SUM 和 AVG 函数会忽略 NULL 值，而 COUNT 函数在使用不同参数时对 NULL 值的处理也有所不同。例如，COUNT(*) 会统计所有行，包括包含 NULL 值的行；而 COUNT(column_name) 会忽略 column_name 列中为 NULL 的行。

示例代码

假设有一个 sales 表，包含 product_id 和 sales_amount 两列，其中 sales_amount 列可能存在 NULL 值。执行以下查询：

SELECT SUM(sales_amount), AVG(sales_amount), COUNT(*), COUNT(sales_amount) FROM sales;

SUM(sales_amount) 和 AVG(sales_amount) 会忽略 sales_amount 为 NULL 的行，COUNT(*) 会统计所有行，而 COUNT(sales_amount) 会忽略 sales_amount 为 NULL 的行。

解决办法

在使用聚合函数时，要清楚它们对 NULL 值的处理方式，并根据需求选择合适的函数。如果需要将 NULL 值视为 0 进行计算，可以使用 IFNULL 或 COALESCE 函数将 NULL 值替换为 0。例如：

SELECT SUM(IFNULL(sales_amount, 0)), AVG(IFNULL(sales_amount, 0)) FROM sales;

3. NULL 在排序中的影响

问题描述

在使用 ORDER BY 子句对包含 NULL 值的列进行排序时，NULL 值会被视为最小或最大的值，具体取决于排序顺序（升序或降序）。在升序排序时，NULL 值会排在最前面；在降序排序时，NULL 值会排在最后面。

示例代码

假设有一个 students 表，包含 student_name 和 score 两列，其中 score 列可能存在 NULL 值。执行以下查询：

SELECT * FROM students ORDER BY score ASC;

NULL 值的记录会排在最前面。

解决办法

如果需要自定义 NULL 值在排序中的位置，可以使用 IFNULL 或 CASE 语句。例如，要将 NULL 值的记录排在最后面，可以使用以下查询：

SELECT * FROM students ORDER BY IFNULL(score, -1) ASC;

或者使用 CASE 语句：

SELECT * FROM students 
ORDER BY 
    CASE 
        WHEN score IS NULL THEN 1 
        ELSE 0 
    END, score ASC;

4. NULL 对 JOIN 操作的影响

问题描述

在进行 JOIN 操作时，如果连接条件涉及到可能为 NULL 的列，由于 NULL 比较的特殊性，可能会导致一些记录无法正确连接。

示例代码

假设有两个表 orders 和 customers，通过 customer_id 列进行连接，customer_id 列可能存在 NULL 值。执行以下查询：

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

如果 customer_id 为 NULL，连接条件 o.customer_id = c.customer_id 的结果为 NULL，会导致包含 NULL 值的记录无法正确连接。

解决办法

可以使用 IS NULL 或 COALESCE 函数来处理 NULL 值。例如，使用 COALESCE 函数将 NULL 值替换为一个特殊值：

SELECT * FROM orders o 
JOIN customers c 
ON COALESCE(o.customer_id, -1) = COALESCE(c.customer_id, -1);

5. NULL 对唯一约束的影响

问题描述

在设置了唯一约束的列中，NULL 值被视为不同的值。也就是说，同一列中可以存在多个 NULL 值，这可能与我们对 “唯一” 的直观理解有所不同。

示例代码

创建一个包含唯一约束的表：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

可以向 email 列插入多个 NULL 值：

INSERT INTO users (email) VALUES (NULL);
INSERT INTO users (email) VALUES (NULL);

解决办法

如果需要确保某列中除 NULL 值外的其他值唯一，可以在应用层进行额外的检查，或者使用触发器来实现更复杂的约束逻辑。