一条 NOT IN 子查询的 SQL,因为子查询里混进了一个 NULL,把本该返回几千行的结果集变成了空,我栽进了 SQL 三值逻辑的坑:一次 NULL 处理的深度复盘
那个 bug 诡异得让我怀疑数据库坏了:我有一条统计查询,要找出"所有从来没被任何订单引用过的商品",写法很直观——SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM orders)。这条 SQL 本该返回几千个"没卖过的商品",可它返回了空,一行都没有。我反复确认数据——明明有大把商品从没出现在订单里啊!我把子查询单独跑了一遍,结果更让我困惑:子查询确实返回了一大堆 product_id。我对着这条"逻辑上完全正确"的 SQL 排查了大半天,直到我注意到子查询的结果里,混着一个 NULL(有些订单的 product_id 是 NULL),才终于想起 SQL 那个最反直觉的特性,后背发凉:SQL 用的是三值逻辑——一个条件的结果不只有 TRUE 和 FALSE,还有第三种:UNKNOWN(未知)。而 NULL 代表"未知/缺失",任何和 NULL 的比较(= NULL、!= NULL)结果都是 UNKNOWN,而非 TRUE/FALSE。我的 id NOT IN (..., NULL, ...),等价于 id != ... AND id != NULL AND ...;只要这一串里有一个 id != NULL,它的结果就是 UNKNOWN;而 ... AND UNKNOWN 在 SQL 里永远不会是 TRUE(最多是 UNKNOWN 或 FALSE);于是没有任何一行能满足整个 NOT IN 条件——结果就空了。就因为子查询里混进了一个 NULL,整个 NOT IN 就"全军覆没"了。这篇就把这次"NOT IN 遇上 NULL、三值逻辑"的坑,从头到尾复盘一遍。
故障现场:NOT IN 的子查询里混进了 NULL
问题代码,是一条几乎人人都会这么写的 NOT IN 查询:
-- ✗ 出问题的查询: NOT IN 子查询, 而子查询结果里有 NULL
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM orders); -- product_id 列里有 NULL!
-- 期望: 返回所有"没被任何订单引用过"的商品(几千行);
-- 实际: 返回【空】! 一行都没有!
-- 复现这个坑:
-- 假设 orders 的 product_id 有这些值: 1, 2, 3, NULL
-- 那么 id NOT IN (1, 2, 3, NULL) 等价于:
-- id != 1 AND id != 2 AND id != 3 AND id != NULL
-- 而 "id != NULL" 的结果【不是TRUE也不是FALSE, 是 UNKNOWN】(任何和NULL比都是UNKNOWN);
-- 于是整个 AND 链: (...) AND UNKNOWN
-- - 如果前面的 != 都成立(TRUE), TRUE AND UNKNOWN = UNKNOWN(不是TRUE);
-- - WHERE 子句只保留结果为 TRUE 的行; UNKNOWN 的行【不会被保留】;
-- → 所以没有任何一行能满足 → 结果空。
-- 对比: IN 不受NULL影响这么严重
-- id IN (1, 2, 3, NULL): id=1 时 (TRUE OR ...) = TRUE, 能命中; NULL只是"多一个永不匹配的值";
-- 但 NOT IN 因为是 AND 链 + 取反, 一个NULL就让整个条件"塌掉"。
-- 关键: SQL是三值逻辑(TRUE/FALSE/UNKNOWN); NULL参与比较得UNKNOWN;
-- NOT IN (含NULL的集合) 会因为 "x != NULL = UNKNOWN" 而让整个条件永不为TRUE → 结果空。
第一次想通这个"空结果"时,我又震惊又无奈:"就因为子查询里有一个 NULL,我这条本该返回几千行的查询就变成空了?而且它不报错、就默默地返回空?"这个坑最致命的地方在于它的静默性:它不报任何错——SQL 语法完全正确、能执行、返回一个"看起来正常"的空结果集;你很难想到"空"是因为子查询里藏了个 NULL,只会困惑"明明有数据怎么查不出来"。而且它极度依赖数据:只要子查询结果里恰好有 NULL 就触发;开发测试时的数据里如果没 NULL,它表现完全正常,可一到生产、数据里混进了 NULL,它就悄悄返回空——又是一个"测试很乖、生产骗人"的坑。下面就来拆解,SQL 的三值逻辑和 NULL。
第一件事:搞懂 SQL 的三值逻辑和 NULL 的特殊性
我认真重学了 SQL 的三值逻辑,才彻底理解 NULL 为什么这么坑。
SQL 的三值逻辑 与 NULL 的特殊性
【核心: SQL逻辑有TRUE/FALSE/UNKNOWN三个值; NULL表示"未知", 任何和NULL的比较都得UNKNOWN; WHERE只保留TRUE的行】
1. 三值逻辑(three-valued logic):
- 普通编程是二值逻辑: 条件结果只有 TRUE / FALSE;
- SQL是【三值逻辑】: TRUE / FALSE / UNKNOWN(未知);
- 多出的 UNKNOWN, 就是 NULL 带来的。
2. NULL 是什么: 它表示"未知 / 缺失 / 不适用", 不是"0", 也不是"空字符串";
- 关键: 任何值和 NULL 做比较(=, !=, >, < ...), 结果都是 UNKNOWN, 不是TRUE/FALSE;
- 因为"和一个未知的东西比较", 结果当然也是"未知"。
- 所以: x = NULL → UNKNOWN(不是TRUE); x != NULL → UNKNOWN; 连 NULL = NULL 也是 UNKNOWN!
3. UNKNOWN 在 AND/OR 里怎么传播:
- TRUE AND UNKNOWN = UNKNOWN; FALSE AND UNKNOWN = FALSE;
- TRUE OR UNKNOWN = TRUE; FALSE OR UNKNOWN = UNKNOWN;
- → 一个UNKNOWN混进AND链, 结果最多是UNKNOWN(出不来TRUE)。
4. WHERE 子句的规则: 【只保留条件结果为 TRUE 的行】;
- 结果为 FALSE 或 UNKNOWN 的行, 都【不保留】;
- → 这就是为什么 NOT IN(含NULL) 会让结果空: 条件成了UNKNOWN, 行被全部排除。
5. 由此衍生的一堆"NULL陷阱":
- WHERE col = NULL 永远查不到(应 IS NULL); col != NULL 也查不到(应 IS NOT NULL);
- NOT IN (含NULL) 结果空(本文);
- COUNT(col) 不统计NULL的行; SUM/AVG 忽略NULL;
- NULL 参与算术(NULL + 1) 结果是 NULL;
- 拼接 CONCAT 含NULL(某些库)结果为NULL。
一句话: SQL是三值逻辑, NULL表示未知、任何和它的比较都得UNKNOWN, 而WHERE只保留TRUE的行;
这导致 =NULL查不到(用IS NULL)、NOT IN含NULL结果空、聚合忽略NULL等一系列陷阱。
这套逻辑,是整个坑的根。三值逻辑:普通编程是二值(TRUE/FALSE),SQL 是三值(TRUE/FALSE/UNKNOWN),多出的 UNKNOWN 就是 NULL 带来的。NULL 表示"未知/缺失"(不是 0、不是空字符串),任何值和 NULL 比较结果都是 UNKNOWN(和未知的东西比当然也未知),所以 x = NULL、x != NULL 甚至 NULL = NULL 都是 UNKNOWN。UNKNOWN 在 AND/OR 里传播:一个 UNKNOWN 混进 AND 链,结果最多是 UNKNOWN(出不来 TRUE)。WHERE 只保留 TRUE 的行(FALSE 和 UNKNOWN 都不保留)——这就是 NOT IN(含 NULL)结果空的原因。由此衍生一堆陷阱:=NULL 永远查不到(用 IS NULL)、NOT IN 含 NULL 结果空、COUNT(col) 不计 NULL、NULL 参与算术得 NULL。一句话:SQL 是三值逻辑,NULL 表示未知、任何和它的比较都得 UNKNOWN,而 WHERE 只保留 TRUE 的行;这导致 =NULL 查不到(用 IS NULL)、NOT IN 含 NULL 结果空、聚合忽略 NULL 等一系列陷阱。
第二件事:正解——用 NOT EXISTS,或过滤掉 NULL,并正确处理 NULL
搞懂了原理,正解就清晰了:把 NOT IN 改成 NOT EXISTS(不受 NULL 这个陷阱影响)、或在子查询里过滤掉 NULL;并用 IS NULL/IS NOT NULL 处理 NULL、用 COALESCE 给默认值。
-- ====== 正解一(推荐): 用 NOT EXISTS 替代 NOT IN ======
SELECT * FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.product_id = p.id
);
-- → NOT EXISTS 是"存在性判断": 对每个product, 看orders里【存不存在】引用它的行;
-- 它【不受子查询里NULL的影响】(NULL的product_id不会等于任何p.id, 自然不算"存在");
-- → 正确返回所有没被引用的商品。这是NOT IN遇NULL问题的标准解法。
-- ====== 正解二: 在子查询里过滤掉 NULL ======
SELECT * FROM products
WHERE id NOT IN (
SELECT product_id FROM orders WHERE product_id IS NOT NULL -- ★ 排除NULL
);
-- → 子查询里加 IS NOT NULL 把NULL滤掉, NOT IN就不会"塌掉"了。
-- (但仍推荐NOT EXISTS, 语义更清晰、还常常性能更好。)
-- ====== 正解三: 正确判断 NULL ——用 IS NULL / IS NOT NULL ======
-- ✗ WHERE col = NULL -- 永远查不到(UNKNOWN)
-- ✓ WHERE col IS NULL -- 查"是NULL的行"
-- ✗ WHERE col != NULL -- 永远查不到
-- ✓ WHERE col IS NOT NULL -- 查"不是NULL的行"
-- ====== 正解四: 用 COALESCE / IFNULL 给NULL一个默认值 ======
SELECT COALESCE(nickname, username, '匿名') AS display_name FROM users;
-- → COALESCE返回第一个非NULL的值; 处理"可能为NULL的列"时给个兜底默认值。
-- 比较含NULL的两列是否"相等"(把两个NULL也算相等):
-- ✗ a = b -- 当a,b都是NULL时, 结果UNKNOWN(不算相等)
-- ✓ a <=> b -- MySQL的"NULL安全等于": NULL<=>NULL为TRUE, 一个NULL一个非NULL为FALSE
-- ✓ (a = b OR (a IS NULL AND b IS NULL)) -- 通用写法
-- ====== 设计层面: 能不用NULL就别滥用 ======
-- - 给列设 NOT NULL + DEFAULT(如数量默认0、状态默认''), 从源头减少NULL;
-- - 但NULL有它的语义(表示"确实未知/不适用"), 该用还得用——关键是用对、并处理好它。
-- 核心: NOT IN遇NULL会结果空, 改用NOT EXISTS(推荐)或子查询过滤NULL; 判NULL用IS NULL/IS NOT NULL;
-- 给NULL兜底用COALESCE; 比较含NULL用<=>; 设计上合理用NOT NULL+DEFAULT减少意外的NULL。
修复的核心,是"避开 NOT IN 的 NULL 陷阱,并始终正确处理 NULL"。正解一(推荐):用 NOT EXISTS 替代 NOT IN——它是"存在性判断",不受子查询里 NULL 的影响(NULL 的 product_id 不等于任何 id、不算"存在"),正确返回结果,是标准解法。正解二:子查询里加 IS NOT NULL 过滤掉 NULL。正解三:判 NULL 用 IS NULL/IS NOT NULL(=NULL 永远查不到)。正解四:用 COALESCE/IFNULL 给默认值、比较含 NULL 用 <=>(NULL 安全等于)。设计层面:能用 NOT NULL+DEFAULT 就从源头减少 NULL,但 NULL 有它的语义、该用还得用对。归根结底:NOT IN 遇 NULL 会结果空,改用 NOT EXISTS(推荐)或过滤 NULL;判 NULL 用 IS NULL/IS NOT NULL;兜底用 COALESCE;比较含 NULL 用 <=>;设计上合理用 NOT NULL+DEFAULT 减少意外 NULL。
第三件事:SQL 里 NULL 相关的其他常见坑
排查后我把 SQL 中 NULL 相关的其他常见坑也系统梳理了一遍。
SQL 中 NULL 相关的其他常见坑
# 1. NOT IN遇NULL结果空(本文): UNKNOWN让条件不为TRUE。→ NOT EXISTS / 过滤NULL。
# 2. = NULL查不到: NULL比较得UNKNOWN。→ 用 IS NULL / IS NOT NULL。
# 3. != / <> 不包含NULL行: WHERE col != 'x' 不会返回 col 为NULL的行(NULL!='x'是UNKNOWN)。
# → 想包含NULL要 OR col IS NULL。
# 4. COUNT(col) vs COUNT(*): COUNT(col)不计NULL, COUNT(*)计所有行。→ 注意区别。
# 5. SUM/AVG忽略NULL: 聚合函数跳过NULL(AVG的分母也不含NULL行)。→ 理解其语义。
# 6. NULL参与算术/拼接: NULL + 1 = NULL; 某些库 CONCAT含NULL结果NULL。→ 用COALESCE兜底。
# 7. 唯一约束允许多个NULL: 唯一索引下, 多行的该列都为NULL通常【不算冲突】(NULL!=NULL)。
# 8. ORDER BY中NULL的位置: NULL排在最前还是最后因数据库而异。→ 用 NULLS FIRST/LAST 或 ISNULL排序。
# 共同根源: NULL不是一个"值", 而是"未知"这个状态; 它遵循三值逻辑、不能用普通的=比较;
# 把NULL当成普通值(或当成0/空串)来处理, 就会在比较、聚合、约束上踩各种坑。
# 核心: 把NULL理解为"未知"(三值逻辑); 判NULL用IS [NOT] NULL、NOT IN改NOT EXISTS、兜底用COALESCE;
# 清楚聚合/约束/排序里NULL的特殊行为; 设计上合理用NOT NULL+DEFAULT减少不必要的NULL。
排查让我把 NULL 的其他坑也梳理清了。一、NOT IN 遇 NULL 结果空(本文)。二、= NULL 查不到(用 IS NULL)。三、!= 不包含 NULL 行(要 OR col IS NULL)。四、COUNT(col) 不计 NULL(对比 COUNT(*))。五、SUM/AVG 忽略 NULL。六、NULL 参与算术/拼接得 NULL。七、唯一约束允许多个 NULL。八、ORDER BY 中 NULL 的位置。它们的共同根源是:NULL 不是一个"值"而是"未知"这个状态;它遵循三值逻辑、不能用普通的 = 比较;把 NULL 当普通值(或当 0/空串)处理,就会在比较、聚合、约束上踩坑。核心是:把 NULL 理解为"未知"(三值逻辑);判 NULL 用 IS [NOT] NULL、NOT IN 改 NOT EXISTS、兜底用 COALESCE;清楚聚合/约束/排序里 NULL 的特殊行为;设计上合理用 NOT NULL+DEFAULT 减少不必要的 NULL。下面这张图,是这次 NOT IN+NULL 坑的成因与解法:
第四件事:NULL 行为速查表
这次踩坑后,我把 NULL 在各种场景下的行为整理成一张表,随时对照。
| 操作 | 结果/行为 | 正确做法 |
|---|---|---|
| col = NULL | UNKNOWN, 查不到 | 用 col IS NULL |
| col != NULL | UNKNOWN, 查不到 | 用 col IS NOT NULL |
| NOT IN (含NULL) | 结果空(本文) | NOT EXISTS / 过滤NULL |
| col != 'x' | 不含col为NULL的行 | OR col IS NULL |
| NULL + 1 / 算术 | NULL | COALESCE兜底 |
| COUNT(col) | 不计NULL行 | 要计全部用COUNT(*) |
| 唯一索引多个NULL | 通常不算冲突 | 注意业务唯一性 |
这张表把 NULL 的行为钉清了。核心是:NULL 在 SQL 里的行为处处和"普通值的直觉"不同——比较得 UNKNOWN(不是 false)、参与运算得 NULL、聚合被忽略、唯一约束不算冲突;几乎每一个涉及 NULL 的操作,都有一个"反直觉"的行为和一个"正确做法"。它给我的最大启发是:NULL 的所有这些"怪异",其实都统一地源于一个观念:NULL 表示"未知(unknown)"——一旦你接受"NULL 不是一个值,而是'我不知道这里是什么'这个状态",它的所有行为就都变得合理了:和"未知"比较当然得"未知(UNKNOWN)";"未知"+1 当然还是"未知";统计时跳过"未知"也合理。这让我领悟到一个理解抽象概念的方法:面对一个"行为怪异、规则一堆"的概念,与其去死记它的每一条规则,不如去抓住它背后那个"统一的心智模型/本质定义"——抓住了"NULL = 未知"这个本质,你就不用背"=NULL 查不到、NOT IN 会空、COUNT 不计"这一堆规则,而能从本质推导出它们;"理解本质 > 记忆规则"——好的本质模型能让一堆零散的规则变成可推导的、自洽的整体。抓住"NULL = 未知"这个本质来推导它的所有行为——是这个坑带给我的认知方法。
第五件事:IN 和 NOT IN 在 NULL 上的不对称
这次还让我看清了一个微妙之处:IN 和 NOT IN 对 NULL 的反应是不对称的。我对比成表。
| 表达式 | 含NULL时 | 为什么 |
|---|---|---|
| x IN (1,2,NULL) | x=1时能命中 | OR链, 一个TRUE就TRUE |
| x NOT IN (1,2,NULL) | 恒不为TRUE→空 | AND链, 一个UNKNOWN就塌 |
| IN 的本质 | x=a OR x=b OR... | OR对UNKNOWN较"宽容" |
| NOT IN 的本质 | x!=a AND x!=b AND... | AND对UNKNOWN很"脆弱" |
这张表道出了一个微妙但关键的不对称。核心是:IN 展开是 OR 链(x=a OR x=b...),OR 对 UNKNOWN"宽容"(只要有一个 TRUE 整体就 TRUE),所以含 NULL 也能正常命中;而 NOT IN 展开是 AND 链(x!=a AND x!=b...),AND 对 UNKNOWN"脆弱"(一个 UNKNOWN 就让整体出不来 TRUE),所以含一个 NULL 就"全塌";同一个 NULL,在 IN 里无伤大雅,在 NOT IN 里却是致命的。它给我的深刻启发是:这提醒我:"取反(NOT)"往往会让逻辑变得更微妙、更易错——一个在"肯定形式"下没问题的东西,加上"否定"后,可能因为德摩根定律(NOT(OR)=AND)、因为对边界/NULL/空集的处理不同,而产生意想不到的行为;"正向条件"和"它的否定"不是简单的镜像,否定常常藏着额外的陷阱(NULL、空集、边界)。这给了我一种处理否定逻辑的警觉:写"否定/排除"类的条件(NOT IN、NOT EXISTS、!=、NOT LIKE)时,要格外当心边界情况——特别想一想"遇到 NULL 会怎样、遇到空集会怎样、它和正向条件真的对称吗";"否定条件比肯定条件更需要仔细推敲边界",尤其在有 NULL/三值逻辑的 SQL 里。看清 IN 与 NOT IN 对 NULL 的不对称、对否定逻辑的边界格外当心——是这个坑带给我的细致认知。
第六件事:写涉及 NULL 的查询时,我现在的检查习惯
现在每当我写涉及可能为 NULL 的列的查询,我都会按这张图先想一想:
这张图的精髓,是"这列可能为 NULL 就特殊对待,别用普通值的直觉"。列可能有 NULL 时:判 NULL 用 IS [NOT] NULL、NOT IN 改 NOT EXISTS、!= 排除要 OR IS NULL、运算用 COALESCE、聚合分清 COUNT(col)/COUNT(*)。这套习惯,让我从"把列都当非 NULL 来写"变成了"先想这列会不会有 NULL"——核心始终是:NULL 是"未知"、遵循三值逻辑,涉及可能为 NULL 的列要特殊处理,别用普通值的直觉。
我立下的几条规矩
这场"NOT IN 遇 NULL 结果空"的事故,换来了我写 SQL 时,刻进骨子里的几条铁律:
- NULL 表示"未知",SQL 是三值逻辑。TRUE/FALSE/UNKNOWN,WHERE 只保留 TRUE。
- 任何和 NULL 的比较都是 UNKNOWN。=NULL、!=NULL 都查不到。
- 判 NULL 用 IS NULL / IS NOT NULL。绝不用 = / !=。
- NOT IN 遇 NULL 结果空。改用 NOT EXISTS,或子查询过滤 NULL。
- != 排除某值不含 NULL 行。想包含要 OR col IS NULL。
- NULL 参与运算得 NULL、聚合忽略 NULL。用 COALESCE 兜底,分清 COUNT。
- 抓住"NULL=未知"本质,设计上合理用 NOT NULL+DEFAULT 减少意外 NULL。
附:几个一眼看清 NULL 行为的小实验
为了让团队彻底记住 NULL 的三值逻辑,我整理了几条能在数据库里直接跑、一眼看清行为的小实验。
-- 1. 和NULL比较, 结果都是NULL(代表UNKNOWN), 不是0/1
SELECT 1 = NULL, 1 != NULL, NULL = NULL; -- 全返回 NULL(即UNKNOWN)
SELECT 1 IS NULL, NULL IS NULL; -- 0(假), 1(真) —— 这才是判NULL的正确姿势
-- 2. NOT IN 含NULL 直接让结果空(用常量集合复现, 不用建表)
SELECT * FROM (SELECT 5 AS x) t WHERE x NOT IN (1, 2, NULL); -- 空! (本文的坑)
SELECT * FROM (SELECT 5 AS x) t WHERE x NOT IN (1, 2); -- 返回x=5 (没NULL就正常)
-- 3. != 不包含NULL行
-- 假设表里有 status: 'A', 'B', NULL
-- SELECT * FROM t WHERE status != 'A'; -- 只返回'B', 【不含NULL的行】!
-- SELECT * FROM t WHERE status != 'A' OR status IS NULL; -- 才包含NULL的行
-- 4. 聚合忽略NULL
-- SELECT COUNT(*), COUNT(col), SUM(col), AVG(col) FROM t;
-- → COUNT(*)算所有行; COUNT(col)/SUM/AVG 都跳过col为NULL的行(AVG分母也不含)
-- 5. NULL安全等于 <=>(MySQL)
SELECT NULL <=> NULL, NULL <=> 1, 1 <=> 1; -- 1, 0, 1 (NULL<=>NULL 为真!)
这几个能直接跑的小实验,把 NULL 的抽象规则变成了"眼见为实"。核心是:用 SELECT 1 = NULL、x NOT IN (1,2,NULL) 这类不用建表就能跑的最小语句,亲眼看到"和 NULL 比返回 NULL、NOT IN 含 NULL 返回空、!= 不含 NULL 行"——抽象的三值逻辑瞬间变成了看得见的结果。它给我的启发是:对付"抽象、易忘、反直觉"的规则(NULL 的行为、运算符优先级、类型转换),最好的记忆和验证方式,是写一个"最小可运行的实验"亲手跑一遍——"SELECT 一行就能验证"远比"读十遍文档"印象深;而且当你不确定某个 NULL 行为时,当场写个最小 SELECT 验证一下,比凭记忆猜可靠得多。这其实是一种高效的工作方式:"不确定就写个最小实验验证",而不是凭模糊的记忆去赌——数据库的最小 SELECT、编程语言的几行测试脚本、API 的一次小调用,都是低成本、高确定性的"验证工具";"动手验证一下"的习惯,能帮你避开大量"我以为是这样"的坑。用最小可运行实验亲眼验证 NULL 的行为、不确定就动手验证——是这个坑教我的实用方法。
附二:NULL 到底该不该用
踩完这个坑,团队里有人提议"干脆所有列都设 NOT NULL、彻底不用 NULL 算了"。这个想法值得辨析一下。
关于"该不该用 NULL",我的体会是要分情况、不走极端。核心是:NULL 有它不可替代的语义——它表示"确实未知、尚未填写、不适用"这种状态,这和"0""空字符串"是不同的含义("余额是 0"和"余额未知/还没开户"是两回事;"评分为 0 分"和"还没评分"是两回事);该用 NULL 表达"真的没有值"时,就该用,用 0/空串硬凑反而会混淆语义、引入错误。但另一方面:NULL 也不该滥用——对那些"逻辑上一定有值"的列(数量、状态、计数),就该设 NOT NULL + DEFAULT,从源头杜绝意外的 NULL(以及它带来的三值逻辑麻烦);"能明确有默认值的就别留 NULL,确实可能'无值/未知'的才用 NULL"。它给我的启发是:对待 NULL(以及任何"有用但有坑"的特性),正确的态度不是"因噎废食地全盘禁用",也不是"不加思考地随意使用",而是"理解它的语义和代价,在合适的地方用、并正确地处理它"——禁用它会失去它表达"未知"的能力;滥用它会到处踩三值逻辑的坑;用对地方 + 处理得当,才能既享受它的表达力、又不被它的坑所伤;"不极端、按语义和场景审慎取舍",是对待一切双刃剑特性的成熟态度。理解 NULL 的语义价值与代价、在合适处用并正确处理而非走极端——是这个坑带给我的、关于如何对待双刃剑特性的认知。
写在最后
回头看,这场由"子查询里一个 NULL"引发的、结果集离奇变空的事故,真正教给我的,远不止"NOT IN 要小心 NULL、改用 NOT EXISTS"这一个技巧。它让我对"很多概念之所以坑,是因为我们把它套进了一个'看似贴切、实则错误'的心智模型里",有了一次刻骨的体会。我栽跟头,根源在于我对 NULL 有一个错误的心智模型:我下意识地把它当成了一个"特殊的值"——像一个"空的值""0 那样的值",以为它能像普通值一样参与 =、!=、IN 的比较。可 NULL 的本质根本不是"一个值",而是"未知(unknown)"这个状态——它表示"这里有没有值、是什么值,我不知道"。当我用"它是个值"的模型去理解它,它的所有行为(和它比较得 UNKNOWN、NOT IN 全塌)对我来说就是"莫名其妙的怪异";可一旦我换成"它是'未知'"的正确模型,这些行为瞬间就都顺理成章了——"和一个未知的东西比较,结果当然是未知"。这让我领悟到一个关于"理解"的深刻认知:我们对一个概念的"心智模型(mental model)",深刻地决定了我们能否正确地运用它——一个错误的心智模型(NULL 是个值),会让概念的正确行为显得"反直觉、全是坑",逼你去死记一堆例外;一个正确的心智模型(NULL 是未知),会让同样的行为变得"自然、可推导";"很多'坑',坑的不是概念本身,而是我们脑子里那个错误的模型"——是错误的模型让我们对正确的行为感到意外。这给了我一种学习时的根本追求:学一个概念,不要满足于"记住它的用法和一堆例外",而要努力找到那个"能让它所有行为都自洽、可推导"的正确心智模型——NULL 是"未知"、指针是"地址"、引用是"别名"、Promise 是"未来的值";"建立正确的心智模型",比"记住一堆规则和例外"更省力、更深刻、也更不容易出错——因为对的模型能让你推导出行为,而不必去背诵。抓住"NULL = 未知"这个正确的心智模型、用对的模型让一切行为自洽可推导——这,是我用一次 NOT IN+NULL 的事故,换来的、关于数据库、也关于如何真正理解一个概念的、最朴素也最深刻的领悟。如果这篇复盘,能让你下次写 NOT IN 子查询、或处理任何可能为 NULL 的列时,脑子里浮现出"NULL 是'未知',不是值",从而本能地用上 NOT EXISTS、IS NULL,那我对着那个离奇的空结果集排查的这大半天,就值了。
—— 别看了 · 2026