NULL值的5个坑，你踩过几个？

重生之我在二本学院拿offer当牌打
212 阅读10分钟

摘要：从一次"明明有数据却查不出来"的诡异bug出发，深度剖析MySQL中NULL值的5个反直觉行为。通过真实案例展示NULL = NULL返回NULL、COUNT(column)不统计NULL、NOT IN遇到NULL全军覆没、索引对NULL的特殊处理、以及排序规则的陷阱，配合时序图和测试用例，揭秘NULL值在SQL中的诡异逻辑，给出避坑指南和最佳实践。

💥 翻车现场

周一早上，测试同学在群里@了哈吉米。

测试同学：@哈吉米 用户列表查询有bug，明明有10个未填手机号的用户，怎么查不出来？
哈吉米：发下你的查询条件
测试同学：查询"手机号不等于138开头"的用户

哈吉米看了下SQL：

-- 查询手机号不是138开头的用户
SELECT * FROM user WHERE phone != '138%';

-- 结果：0行

哈吉米："怎么可能？明明有用户没填手机号（phone是NULL）啊！"

看了下数据：

SELECT id, username, phone FROM user;

+----+----------+---------------+
| id | username | phone         |
+----+----------+---------------+
| 1  | alice    | 13800138000   |
| 2  | bob      | 13900139000   |
| 3  | charlie  | NULL          |  ← NULL怎么查不出来？
| 4  | david    | NULL          |
| 5  | eve      | 15000150000   |
+----+----------+---------------+

哈吉米："phone != '138%' 应该包括NULL吧？为什么查不出来？"

下午，南北绿豆和阿西噶阿西来了。

南北绿豆："这是NULL值的第一个坑：NULL和任何值比较，结果都是NULL！"
哈吉米："？？？"
阿西噶阿西："NULL在SQL里是个特殊值，有很多反直觉的行为，我给你讲讲。"

🕳️ 坑1：NULL = NULL 返回NULL

问题重现

-- 测试
SELECT NULL = NULL;

+-------------+
| NULL = NULL |
+-------------+
| NULL        |  ← 不是TRUE，也不是FALSE，是NULL！
+-------------+

-- 再测试
SELECT NULL != NULL;

+--------------+
| NULL != NULL |
+--------------+
| NULL         |  ← 还是NULL
+--------------+

-- 甚至
SELECT NULL = 1;    -- NULL
SELECT NULL != 1;   -- NULL
SELECT NULL > 1;    -- NULL
SELECT NULL < 1;    -- NULL

核心原则：NULL和任何值比较（=、!=、>、<），结果都是NULL，不是TRUE也不是FALSE。

为什么会这样？

南北绿豆："因为NULL表示'未知'，未知和任何值比较，结果都是未知。"

NULL的含义：
- 不是空字符串('')
- 不是0
- 而是"未知"、"不存在"

逻辑：
- NULL = 1？不知道（NULL可能是1，也可能不是）→ NULL
- NULL != 1？不知道（NULL可能不是1，也可能是）→ NULL
- NULL = NULL？不知道（两个未知值不一定相等）→ NULL

正确的NULL判断

-- ❌ 错误写法
SELECT * FROM user WHERE phone = NULL;
-- 0 rows（查不到任何数据）

SELECT * FROM user WHERE phone != NULL;
-- 0 rows（也查不到任何数据）

-- ✅ 正确写法
SELECT * FROM user WHERE phone IS NULL;
-- 查到phone为NULL的行 ✅

SELECT * FROM user WHERE phone IS NOT NULL;
-- 查到phone不为NULL的行 ✅

回到翻车现场

-- 原始SQL（错误）
SELECT * FROM user WHERE phone != '138%';
-- 逻辑：NULL != '138%' → NULL → WHERE子句为NULL → 不返回这行

-- 正确写法
SELECT * FROM user WHERE phone NOT LIKE '138%' OR phone IS NULL;
-- 或者
SELECT * FROM user WHERE (phone IS NULL OR phone NOT LIKE '138%');

执行流程：

graph TD
    A[扫描user表] --> B{phone != '138%'?}
    B -->|phone = '13800138000'| C[FALSE，不返回]
    B -->|phone = '15000150000'| D[TRUE，返回]
    B -->|phone = NULL| E[NULL，不返回]
    
    F[正确写法] --> G{phone IS NULL OR phone NOT LIKE '138%'?}
    G -->|phone = NULL| H[TRUE，返回 ✅]
    G -->|phone = '15000150000'| I[TRUE，返回 ✅]
    
    style E fill:#FFB6C1
    style H fill:#90EE90

哈吉米："卧槽，原来WHERE子句的结果如果是NULL，这行就不会返回！"

阿西噶阿西："对！WHERE子句必须是TRUE才返回，NULL不行。"

🕳️ 坑2：COUNT(column)不统计NULL

问题重现

-- 测试数据
CREATE TABLE test_count (
  id INT,
  name VARCHAR(50),
  phone VARCHAR(20)
);

INSERT INTO test_count VALUES 
(1, 'alice', '13800138000'),
(2, 'bob', NULL),
(3, 'charlie', NULL),
(4, 'david', '15000150000');

-- 查询
SELECT 
  COUNT(*) AS count_all,
  COUNT(phone) AS count_phone,
  COUNT(1) AS count_1
FROM test_count;

+-----------+-------------+---------+
| count_all | count_phone | count_1 |
+-----------+-------------+---------+
| 4         | 2           | 4       |
+-----------+-------------+---------+

对比：

写法统计NULL吗？结果
COUNT(*)✅ 统计4
COUNT(1)✅ 统计4
COUNT(phone)❌ 不统计2（只统计非NULL的phone）

南北绿豆："这是COUNT的设计：COUNT(column)会跳过NULL值！"

实际案例

-- 统计有手机号的用户数
SELECT COUNT(phone) FROM user;  -- 8（有8个用户填了手机号）

-- 统计没有手机号的用户数
SELECT COUNT(*) - COUNT(phone) FROM user;  -- 2（有2个用户没填）

-- 或者
SELECT COUNT(*) FROM user WHERE phone IS NULL;  -- 2

坑：SUM/AVG也会忽略NULL

CREATE TABLE score (
  student_id INT,
  math_score INT,
  english_score INT
);

INSERT INTO score VALUES 
(1, 90, 85),
(2, 80, NULL),  -- 英语缺考
(3, 75, 88);

-- 计算平均分
SELECT 
  AVG(math_score) AS avg_math,
  AVG(english_score) AS avg_english
FROM score;

+----------+-------------+
| avg_math | avg_english |
+----------+-------------+
| 81.67    | 86.50       |
+----------+-------------+

-- 注意：
-- avg_math = (90 + 80 + 75) / 3 = 81.67
-- avg_english = (85 + 88) / 2 = 86.50  ← 只统计了2个人，忽略了NULL

阿西噶阿西："如果想把NULL当作0，需要用COALESCE。"

SELECT 
  AVG(COALESCE(english_score, 0)) AS avg_english_with_zero
FROM score;

+----------------------+
| avg_english_with_zero |
+----------------------+
| 57.67                |  -- (85 + 0 + 88) / 3
+----------------------+

🕳️ 坑3：NOT IN遇到NULL全军覆没

问题重现

-- 查询不在黑名单的用户
SELECT * FROM user 
WHERE user_id NOT IN (
  SELECT blocked_user_id FROM blacklist
);

-- 假设blacklist表数据：
SELECT blocked_user_id FROM blacklist;
+------------------+
| blocked_user_id  |
+------------------+
| 100              |
| 200              |
| NULL             |  ← 有一个NULL值
+------------------+

-- 结果：0 rows（一个都查不出来！）

哈吉米："卧槽，为啥一个都查不出来？"

南北绿豆："因为NOT IN遇到NULL会出问题。"

原理分析

-- 展开NOT IN的逻辑
WHERE user_id NOT IN (100, 200, NULL)

-- 等价于
WHERE user_id != 100 
  AND user_id != 200 
  AND user_id != NULL

-- 但是 user_id != NULL 永远是 NULL
-- 所以整个表达式变成
WHERE user_id != 100 AND user_id != 200 AND NULL

-- 任何值 AND NULL = NULL
-- WHERE子句为NULL，不返回任何行

执行流程：

graph TD
    A[user_id = 1] --> B{NOT IN 100, 200, NULL?}
    B --> C{1 != 100?} 
    C -->|TRUE| D{1 != 200?}
    D -->|TRUE| E{1 != NULL?}
    E -->|NULL| F[TRUE AND TRUE AND NULL = NULL]
    F --> G[不返回 ❌]
    
    style G fill:#FFB6C1

解决方案

方案1：过滤掉NULL

SELECT * FROM user 
WHERE user_id NOT IN (
  SELECT blocked_user_id FROM blacklist 
  WHERE blocked_user_id IS NOT NULL  -- 关键
);

方案2：用NOT EXISTS（推荐）

SELECT * FROM user u
WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b 
  WHERE b.blocked_user_id = u.user_id
);

-- NOT EXISTS不受NULL影响 ✅

方案3：用LEFT JOIN

SELECT u.* 
FROM user u
LEFT JOIN blacklist b ON u.user_id = b.blocked_user_id
WHERE b.blocked_user_id IS NULL;

IN也有问题（但不致命）

SELECT * FROM user 
WHERE user_id IN (100, 200, NULL);

-- 等价于
WHERE user_id = 100 OR user_id = 200 OR user_id = NULL

-- user_id = NULL 是NULL
-- 但 TRUE OR NULL = TRUE
-- 所以能查到user_id=100和200的行

-- 结论：IN遇到NULL，影响不大
-- 但NOT IN遇到NULL，全军覆没

🕳️ 坑4：索引对NULL的特殊处理

NULL值可以建索引吗？

哈吉米："NULL可以建索引吗？"

阿西噶阿西："可以，而且InnoDB会把NULL值存在索引里。"

CREATE TABLE test_null_index (
  id INT PRIMARY KEY,
  phone VARCHAR(20),
  INDEX idx_phone(phone)
);

INSERT INTO test_null_index VALUES 
(1, '13800138000'),
(2, NULL),
(3, '15000150000'),
(4, NULL);

-- 查询phone IS NULL
SELECT * FROM test_null_index WHERE phone IS NULL;

EXPLAIN:
type: ref          ← 走索引了
key: idx_phone
rows: 2

索引中NULL的存储：

索引树 idx_phone:
  NULL         ← NULL值存在最前面
  NULL
  '13800138000'
  '15000150000'

但有个坑：索引统计不准

-- MySQL估算索引选择性
SELECT 
  CONCAT(table_name, '.', index_name) AS 'index',
  cardinality,
  table_rows,
  ROUND(cardinality / table_rows * 100, 2) AS selectivity
FROM information_schema.STATISTICS s
JOIN information_schema.TABLES t USING (table_schema, table_name)
WHERE table_schema = 'test' AND index_name = 'idx_phone';

+------------------+-------------+------------+-------------+
| index            | cardinality | table_rows | selectivity |
+------------------+-------------+------------+-------------+
| test_null_index.idx_phone | 2  | 4          | 50.00       |
+------------------+-------------+------------+-------------+

-- 问题：cardinality是估算值，NULL可能导致统计不准

唯一索引对NULL的处理

CREATE TABLE test_unique (
  id INT PRIMARY KEY,
  email VARCHAR(100),
  UNIQUE INDEX uk_email(email)
);

-- 可以插入多个NULL（NULL不算重复）
INSERT INTO test_unique VALUES (1, 'alice@example.com');
INSERT INTO test_unique VALUES (2, NULL);
INSERT INTO test_unique VALUES (3, NULL);  -- ✅ 允许

-- 但不能插入重复的非NULL值
INSERT INTO test_unique VALUES (4, 'alice@example.com');  -- ❌ 报错

南北绿豆："唯一索引认为NULL和NULL不相等，所以允许多个NULL。"

🕳️ 坑5：ORDER BY排序的坑

NULL的排序规则

SELECT * FROM test_null_index ORDER BY phone;

+----+---------------+
| id | phone         |
+----+---------------+
| 2  | NULL          |  ← NULL排最前面
| 4  | NULL          |
| 1  | 13800138000   |
| 3  | 15000150000   |
+----+---------------+

SELECT * FROM test_null_index ORDER BY phone DESC;

+----+---------------+
| id | phone         |
+----+---------------+
| 3  | 15000150000   |
| 1  | 13800138000   |
| 2  | NULL          |  ← NULL排最后面（DESC时）
| 4  | NULL          |
+----+---------------+

规则：

  • 升序（ASC）：NULL排最前面
  • 降序（DESC）：NULL排最后面

如何让NULL排在最后？

MySQL 8.0+：

-- NULL排在最后
SELECT * FROM test_null_index 
ORDER BY phone IS NULL, phone;

-- 原理：
-- phone IS NULL 返回 0（FALSE）或 1（TRUE）
-- 先按IS NULL排序（0在前，1在后）
-- 再按phone排序

或者用COALESCE：

SELECT * FROM test_null_index 
ORDER BY COALESCE(phone, 'ZZZZZZZZ');  -- NULL替换成最大值

🛡️ 避坑指南

指南1：尽量避免NULL

设计表时：

-- ❌ 不推荐
CREATE TABLE user (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  phone VARCHAR(20)  -- 允许NULL，容易出问题
);

-- ✅ 推荐
CREATE TABLE user (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  phone VARCHAR(20) NOT NULL DEFAULT ''  -- 默认空字符串，不用NULL
);

什么时候可以用NULL？

1. 确实需要表达"未知"、"不存在"的语义
   - 例如：用户的生日（可能不愿意填）
   - 例如：订单的发货时间（还没发货）

2. 不用NULL的替代方案：
   - 数字：用-1、0表示"未知"
   - 字符串：用空字符串''表示"未知"
   - 日期：用特殊值'1970-01-01'表示"未知"

指南2：查询NULL用IS NULL

-- ❌ 错误
WHERE phone = NULL
WHERE phone != NULL

-- ✅ 正确
WHERE phone IS NULL
WHERE phone IS NOT NULL

指南3：NOT IN改成NOT EXISTS

-- ❌ 危险（子查询可能有NULL）
WHERE user_id NOT IN (SELECT ...)

-- ✅ 安全
WHERE NOT EXISTS (SELECT 1 FROM ... WHERE ...)

-- 或者过滤NULL
WHERE user_id NOT IN (SELECT ... WHERE column IS NOT NULL)

指南4：聚合函数注意NULL

-- COUNT(column)会忽略NULL
SELECT COUNT(phone) FROM user;  -- 只统计非NULL

-- SUM、AVG也会忽略NULL
SELECT AVG(score) FROM test;  -- 忽略NULL的score

-- 如果要把NULL当作0
SELECT AVG(COALESCE(score, 0)) FROM test;

指南5：使用COALESCE处理NULL

-- COALESCE：返回第一个非NULL值
SELECT 
  username,
  COALESCE(phone, email, 'No contact') AS contact
FROM user;

-- 示例
username | phone       | email           | contact
---------|-------------|-----------------|----------------
alice    | 13800138000 | NULL            | 13800138000
bob      | NULL        | bob@example.com | bob@example.com
charlie  | NULL        | NULL            | No contact

🎓 面试标准答案

题目：MySQL中NULL有哪些坑？

答案：

5个核心坑：

  1. NULL = NULL 返回NULL

    • NULL和任何值比较都返回NULL，不是TRUE也不是FALSE
    • WHERE子句为NULL，不返回该行
    • 正确判断：用IS NULL、IS NOT NULL

  2. COUNT(column)不统计NULL

    • COUNT(*)统计所有行
    • COUNT(column)只统计非NULL行
    • SUM、AVG也会忽略NULL

  3. NOT IN遇到NULL全军覆没

    • 子查询有NULL时，NOT IN返回空集
    • 解决方案：用NOT EXISTS或过滤NULL

  4. 索引对NULL的特殊处理

    • 唯一索引允许多个NULL
    • NULL值存在索引最前面
    • 可能导致统计信息不准

  5. ORDER BY的排序规则

    • ASC：NULL排最前面
    • DESC：NULL排最后面

避坑建议：

  • 设计表时尽量NOT NULL + DEFAULT
  • 查询NULL用IS NULL
  • NOT IN改成NOT EXISTS
  • 用COALESCE处理NULL

🎉 结束语

晚上7点，哈吉米终于把所有NULL相关的坑都搞清楚了。

哈吉米："原来NULL这么多坑！以前都是稀里糊涂地用。"

南北绿豆："对，NULL是SQL里最特殊的值，很多反直觉的行为。"

阿西噶阿西："记住：设计表时尽量避免NULL，查询时小心NULL的陷阱。"

哈吉米："还有NOT IN遇到NULL全军覆没，这个太坑了！"

南北绿豆："对，所以推荐用NOT EXISTS，更安全。"

记忆口诀：

NULL比较永返NULL，IS NULL才是正确判
COUNT列名会跳过，NOT IN遇NULL全军覆
唯一索引允多NULL，排序规则要记牢
设计表时避NULL值，COALESCE来兜底

希望这篇文章能帮你避开NULL的5个大坑！下次写SQL，记得小心NULL！💪

avatar
文章
阅读
粉丝