MySQL 中 null 值的那些坑，你踩过吗？

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);
 
INSERT INTO users VALUES 
(1, 'Alice', 25, 'alice@example.com'), 
(2, 'Bob', 30, NULL),
(3, 'Charlie', NULL, 'charlie@example.com');

执行以下查询：

SELECT * FROM users WHERE email = NULL; -- 查不到任何结果 
SELECT * FROM users WHERE email <> NULL; -- 同样查不到任何结果

原因：
null 是一个不确定的值，无法用 = 或 <> 进行比较。任何与 null 的比较都会返回 false。

问题 2：在 `WHERE` 子句中使用 `IS NULL` 和 `IS NOT NULL` 的时候忘记逻辑

有时候，开发者会忘记 IS NULL 和 IS NOT NULL 的正确用法，导致查询结果不符合预期。

示例场景：
继续使用上面的 users 表。

SELECT * FROM users WHERE email IS NULL; -- 正确的结果：Bob 的记录 
SELECT * FROM users WHERE age IS NOT NULL; -- 正确的结果：Alice 和 Charlie 的记录

常见错误：

SELECT * FROM users WHERE email = NULL; -- 错误！返回空结果

问题 3：在 `IN` 和 `NOT IN` 语句中使用 null 值

在 IN 和 NOT IN 语句中使用包含 null 的子查询时，可能会出现意想不到的结果。

示例场景：
假设有两张表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2)
);
 
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);
 
INSERT INTO orders VALUES 
(1, 1, 100.00),
(2, 2, 200.00),
(3, 3, 300.00);
 
INSERT INTO users VALUES 
(1, 'Alice', 'alice@example.com'), 
(2, 'Bob', NULL),
(3, 'Charlie', 'charlie@example.com');

执行以下查询：

SELECT * FROM orders 
WHERE user_id IN (SELECT user_id FROM users WHERE email IS NULL); -- 正确的结果：Bob 的订单

常见错误：

SELECT * FROM orders 
WHERE user_id IN (SELECT user_id FROM users WHERE email = NULL); -- 错误！返回空结果

第二部分：解决 null 值问题的方法

方法 1：使用 `IS NULL` 和 `IS NOT NULL` 进行判断

这是处理 null 值的正确方式。

示例代码：

-- 查询 email 为 null 的用户 
SELECT * FROM users WHERE email IS NULL;
 
-- 查询 age 不为 null 的用户 
SELECT * FROM users WHERE age IS NOT NULL;

注意事项：

IS NULL 和 IS NOT NULL 只能用于判断字段是否为 null，不能与其他条件混合使用。
如果需要结合其他条件查询，可以使用逻辑运算符 AND 或 OR。

方法 2：在 `IN` 和 `NOT IN` 语句中正确处理 null 值

如果子查询中包含 null 值，可以使用 COALESCE 函数将其转换为其他值。

示例代码：

-- 正确的写法：使用 COALESCE 将 null 转换为一个不存在的值 
SELECT * FROM orders 
WHERE user_id IN (
    SELECT COALESCE(user_id, -1) FROM users WHERE email IS NULL 
);

解释：

COALESCE(user_id, -1) 表示如果 user_id 为 null，则返回 -1。
这样可以避免子查询中出现 null 值导致的逻辑错误。

方法 3：在插入和更新数据时明确处理 null 值

在插入或更新数据时，要确保字段允许存储 null 值。如果字段被定义为 NOT NULL，则必须提供非空值。

示例代码：

-- 插入 null 值 
INSERT INTO users (id, name, age, email) 
VALUES (4, 'David', NULL, 'david@example.com'); 
 
-- 更新 null 值 
UPDATE users SET email = NULL WHERE id = 4;

注意事项：

如果字段被定义为 NOT NULL，插入或更新时必须提供有效值。

可以使用 ALTER TABLE 修改字段的约束：

ALTER TABLE users MODIFY COLUMN email VARCHAR(100) NULL;

第三部分：注意事项

注意 1：不要混淆 null 和空字符串

null 表示“不存在”或“未知”。
空字符串（""）表示字段确实存在，但内容为空。

示例场景：

-- 查询 email 为 null 的用户 
SELECT * FROM users WHERE email IS NULL;
 
-- 查询 email 为空字符串的用户 
SELECT * FROM users WHERE email = '';

注意 2：在排序时 null 的行为

在排序时，null 的行为可能与预期不同。默认情况下，null 会被视为最小值（在升序排列中排在最前面）。

示例代码：

-- 按 age 升序排列，null 排在最前面 
SELECT * FROM users ORDER BY age ASC;
 
-- 按 age 降序排列，null 排在最后面 
SELECT * FROM users ORDER BY age DESC;

注意 3：在聚合函数中处理 null 值

聚合函数（如 SUM, AVG, COUNT）会忽略 null 值。

示例场景：

-- 计算所有用户的平均年龄（忽略 null 值）
SELECT AVG(age) FROM users;

总结：正确处理 null 值的三个关键点

使用 IS NULL 和 IS NOT NULL 进行判断
在子查询中使用 COALESCE 处理 null 值
在插入和更新时明确字段是否允许 null 值

通过以上方法，你可以轻松避开 null 值带来的“坑”，写出更健壮的 SQL 语句！

互动时间：你踩过哪些 null 值的坑？

你是否曾经因为 null 值的问题而困惑？
在实际开发中，你是如何处理 null 值的？

欢迎在评论区分享你的经验和心得！

希望这篇文章能帮助你更好地理解和处理 MySQL 中的 null 值问题！如果你觉得这篇文章对你有帮助，记得点赞和分享哦！ 😊

上一篇： kotlin的Str

下一篇：大白话JavaScr