★ FEATURED ARTICLE 1. 先把三值逻辑这层窗户纸捅破NULL为什么连自己都不认识自己接手过任何一个老库大概率会碰到这种“业务bug”页面明明提交了数据列表却查不到记录明明字段没填搜索框填个空串就是不命中。最后翻代码十有八九是WHERE 列 NULL或者把当成了空字符串处理。在 Oracle 里NULL 表示的是“未知”“缺失”“没有值”它既不是空字符串也不是 0更不是空格。数据库在存储层面只标记了这个位置“没有内容”并没有保存一个具体数值。SQL 里的判断结果不是非真即假而是有三种状态真、假、未知UNKNOWN。1 1返回真1 2返回假而NULL NULL返回未知。两个未知的东西到底相不相等数据库没法判断所以它不会给你一个肯定的“真”。WHERE 子句只保留结果为真的行未知和假一样都会把行丢掉。这就是为什么写WHERE column NULL永远查不出任何行——每一行的比较结果都是 UNKNOWN没有一行能走进结果集。更有意思的是取反一个 UNKNOWN结果还是 UNKNOWN。也就是说WHERE age 30 OR NOT (age 30)这种“怎么说都该成立”的条件遇到age IS NULL的行时前半段是 UNKNOWN后半段也是 UNKNOWNOR 完还是 UNKNOWN这一行照样不出现。业务同事经常跟我抱怨“我明明写的是排除不满足条件的怎么连没有值的也一起没了”其实本质就是三值逻辑在起作用。Oracle 和部分其他数据库还有一个让新人措手不及的行为空字符串会被自动转成 NULL。你写WHERE col 等价于拿 NULL 去做比较结果自然是查不到任何行往表里插INSERT INTO t(a) VALUES ()之后再读出来会发现a IS NULL是真的。这个坑从 MySQL、PostgreSQL 迁过来时特别明显如果老系统里确实需要“空串”这种语义建议在 Oracle 里用 占位或者干脆铺一个默认值别指望能保留原样。1.1 一组小实验彻底理解“未知”与其背文档不如直接在测试库跑几条 SQLSELECT * FROM dual WHERE NULL NULL; -- 0 行 SELECT * FROM dual WHERE NULL IS NULL; -- 1 行 SELECT * FROM dual WHERE ; -- 0 行 已被当作 NULL SELECT * FROM dual WHERE NVL(NULL, 1) 1; -- 1 行这几条跑完基本就能记住普通比较运算符永远匹配不了 NULL唯一的正确姿势是IS NULL和IS NOT NULL还有后面会提到的几个专门函数。反过来说如果你在代码里发现某个过滤条件写着“等于某个空值”它十有八九是个静默错误——不报异常只是结果集里少了一大片数据线上跑几天都不一定能发现。2. NULL 在 WHERE、NOT IN 里造成的“隐性截断”如果说三值逻辑是理论课那下面这些就是实战课。第一个常见现象是 LEFT JOIN 之后前缀条件把左表数据悄悄砍掉。很多人以为写了 LEFT JOIN 就能保住左表的全部行结果习惯性在 WHERE 里加右表字段的条件SELECT * FROM orders o LEFT JOIN payment p ON o.pay_id p.pay_id WHERE p.status PAID;这条 SQL 实际执行时会先把两表连接起来再对连接结果做 WHERE 过滤。那些没有匹配到支付记录的订单p.status是 NULLNULL PAID返回 UNKNOWN于是这些订单全部被滤掉。LEFT JOIN 在逻辑上已经退化成了普通连接。正确的写法是把过滤条件挪到 ON 子句里SELECT * FROM orders o LEFT JOIN payment p ON o.pay_id p.pay_id AND p.status PAID;这样未匹配的订单依然会保留只是右侧字段全部显示为 NULL。这个改法不需要动索引不需要动表结构纯粹是 SQL 语义的问题但在我经手的团队里出现频率高得惊人。第二个常见坑是 NOT IN。如果子查询返回的结果列表里带一个 NULL结果往往不是“排除掉那行”而是整个查询一行都查不出来。看这个很自然的写法SELECT * FROM employee e WHERE e.department_id NOT IN ( SELECT department_id FROM resigned_employee );只要resigned_employee.department_id有任何一行是 NULL数据库就会把条件展开成e.department_id 1 AND e.department_id 2 AND ... AND e.department_id NULL最后那个 NULL的结果永远是 UNKNOWN而多个 AND 条件只要有一个是 UNKNOWN整体结果就是 UNKNOWN于是所有行都会被过滤。有一次我在项目里查“未离职员工”上线后列表直接空白排查半天才发现离职表里几个历史部门的字段是 NULL导致整条 SQL 全军覆没。2.1 NOT IN 与 NOT EXISTS 的差异现场绕开这个坑最简单的办法是把 NOT IN 改成 NOT EXISTSSELECT * FROM employee e WHERE NOT EXISTS ( SELECT 1 FROM resigned_employee r WHERE r.department_id e.department_id );NOT EXISTS 是逐行去查内层有没有匹配记录内层返回 NULL 或者不匹配都不会反过来否定外层行。换句话说NOT IN 的逻辑是“先凑出一个值列表再用集合关系判断”NOT EXISTS 则是“一行一行看是否存在”。前者遇到 NULL 会翻车后者不会。所以我在代码评审里通常直接要求凡是做“排除”语义优先写 NOT EXISTS除非你能百分之百保证子查询的列无 NULL。同样的逻辑也出现在 CASE 表达式里。CASE WHEN col NULL THEN 缺失 ELSE 存在 END永远不会进入‘缺失’分支因为col NULL的结果是 UNKNOWN会直接落到 ELSE。正确的写法是CASE WHEN col IS NULL THEN 缺失 ELSE 存在 END。另外页面搜索时用户没输入条件后端很可能传一个 NULL 参数如果直接拼WHERE name :inputName当参数是 NULL 时结果集同样是空。这种场景建议根据传入值动态拼接或者用这种安全写法WHERE (name :inputName OR (:inputName IS NULL AND name IS NULL))不过要注意这个写法会把表中本来就为 NULL 的行也筛出来所以具体业务里“用户没输入到底要不要筛 NULL 行”一定要和产品确认不要自己拍脑袋。3. 空串、聚合、分组涉及 NULL 的统计口径要提前定数据库在底层的处理上也不止“查不出来”这一个影响。Oracle 里 NULL 占用的实际存储非常小基本就是一个标记位不像定长字符串那样铺满空间这也是它在存储层和空字符串的一个区别。但真正的麻烦在于应用层拿到 NULL 后很多框架会把字段映射成 Java/Kotlin 的 null而不是空字符串序列化、非空校验、接口返回时都会冒出一堆意料之外的问题。聚合函数对 NULL 的态度也经常被人读错。COUNT(*)和COUNT(1)统计的是行数不管这一行是不是全空COUNT(列)统计的是该列非 NULL 的个数。报表里“参与人数”和“总人数”的差异就是这么来的。有一回同事统计上传单据数量直接COUNT(单据编号)可历史数据里不少单据没有编号结果月度报表数量少了一截定位到后来才发现问题出在口径上。SUM、AVG同样会忽略 NULL。更麻烦的是如果某一组的值全部是 NULLSUM 返回 NULLAVG 返回 NULL而不是 0。这个 NULL 一旦被后面的表达式引用就会一路传染比如“增长率 本月销售额 / 上月销售额”上月不存在的组除数是 NULL计算结果是 NULL报表里就莫名其妙多出一片空格。我个人的习惯是统计类查询在最终展示层统一包一层NVL或COALESCE先定好“没数据到底显示 0 还是显示空”别让 NULL 在多个指标之间串来串去。3.1 聚合函数眼中的 NULL 与 COUNT 口径做个简单归类操作对 NULL 的行为典型误区COUNT(*) / COUNT(1)统计行数包含全 NULL 行以为计的是“有值数量”COUNT(列)统计非 NULL 数量空值多时数量悄悄缩水SUM / AVG忽略 NULL全 NULL 时返回 NULL没注意结果不是 0MIN / MAX忽略 NULL全 NULL 时返回 NULL分组统计出现空白GROUP BY所有 NULL 归为一组展示层出现“空分类”GROUP BY把 NULL 归为一组这个行为在分页、看板、按地区统计时特别容易被忽略。某个地域字段没填的记录会汇总成一行 label 为 NULL 的数据。建议统计前用NVL(region, 未知地区)做一次转换至少展示层不会出现一个空分类名。3.2 设计阶段的 NULL 取舍这里要聊一个看起来和查询无关、其实影响最大的决策建表时到底允不允许 NULL。有些团队嫌麻烦所有列一律不加 NOT NULL觉得以后加数据方便。结果一旦数据里混进大量 NULL过滤、统计、索引、关联全都要围着它转代价比建表时多写几个约束大得多。我在新项目里的习惯是三层判断业务上必须有值的列直接 NOT NULL代码里其实总会给默认值 0 或空字符串的列也不用 NULL直接用默认值只有语义上真正是“缺失、未知、尚未发生”的才保留 NULL。例如“优惠券使用时间”适合 NULL“订单状态”必须 NOT NULL。这个决策最好在表结构评审时就定下来等报表对不上数再回头改成本完全不是一个量级。4. NVL、NVL2、COALESCE、NULLIF 到底该用哪个NULL 处理函数是 Oracle 里最常用的八个函数之一但不少人只认 NVL导致某些场景写得很别扭。先说结论函数全家福如下函数签名行为适用场景NVLNVL(expr1, expr2)expr1 为 NULL 时返回 expr2否则返回 expr1单值兜底两参数即可NVL2NVL2(expr1, expr2, expr3)expr1 不为 NULL 返回 expr2为 NULL 返回 expr3NULL/非 NULL 两种分支COALESCECOALESCE(expr1, expr2, ...)从左到右返回第一个非 NULL多字段按优先级取首个非空NULLIFNULLIF(expr1, expr2)两值相等返回 NULL否则返回 expr1把 0/空串转成 NULL防除零DECODEDECODE(expr, search, result, ...)按等值匹配返回结果把 NULL 当成可匹配值做映射NVL 是 Oracle 专属函数如果项目有跨数据库的预期优先写 COALESCE 更稳。COALESCE 可以传多个参数逻辑上更像 CASE 的简化版一旦找到第一个非 NULL 就不再继续往下评估这在候选值是子查询时能省不少成本。地址展示就是一个典型场景省份、城市、区县三个字段哪个有值展示哪个直接COALESCE(province, city, district)一行写完。4.1 函数对比速查表NULLIF 最常见的用途是除零保护。amount / NULLIF(quantity, 0)里quantity 为 0 时 NULLIF 返回 NULL整个除法结果变成 NULL避免数据库直接报 ORA-01476业务层再套一层 NVL 就能得到 0。这个组合写法我在计算费率、分摊金额时用了很多年稳定可靠。这里还要提醒一个容易踩的差异DECODE 对 NULL 的匹配逻辑和 CASE 不同。DECODE(col, NULL, 未知, col)在 col 为 NULL 时能命中‘未知’分支因为 DECODE 内部把两个 NULL 当成同值处理而 CASE 必须写CASE WHEN col IS NULL THEN 未知 ELSE col END。我改过不少存储过程很多老代码用 DECODE 用得挺顺手后来加需求改成 CASE一不下心就漏了 IS NULL 判断结果 NULL 分支老走错。4.2 嵌套场景示例举个常见的三层兜底需求订单展示优先用发货时间没有就按下单时间再没有就按更新时间都没有则显示“无记录”SELECT order_id, NVL(COALESCE(ship_time, order_time, update_time), 无记录) AS display_time FROM orders;内层 COALESCE 反映“多个字段里取第一个有值”外层 NVL 反映“全部为空给兜底文案”。我的使用建议是单值兜底首选 NVL多值轮询首选 COALESCE需要按是否为 NULL 走向不同分支时选 NVL2 或 DECODE。别小看这几个函数的选择它们表面上都能完成“空值替换”但在参数数量、可移植性、短路行为上差异很明显写错不一定报错但后续维护会相当费劲。5. 索引、唯一约束与 NULL 打架一次排查过程的还原NULL 对索引的影响是很多人建索引时完全没考虑过的维度。Oracle 的 B 树索引有一个特点索引键全部为 NULL 的行基本不会进入常规索引条目。换句话说一张表如果某列大部分是 NULL在这个列上建的普通索引对 NULL 部分来说几乎是空的。这带来的直接影响是WHERE col IS NULL这种查询很难走普通索引。优化器一估算发现索引里根本没有这些行走索引反而要回表干脆选了全表扫描。如果表只有几万行问题不大但表到了千万级一个status IS NULL加上其他过滤条件查询时间会从几十毫秒涨到几分钟慢查询日志里全是它。5.1 索引侧的实践现象想要把“是否为 NULL”也纳入索引常用办法是函数索引CREATE INDEX idx_t_col_null ON t (NVL(col, -1)); SELECT * FROM t WHERE NVL(col, -1) -1;这里-1必须选一个业务上不会出现的占位值。需要注意两点一是查询里必须写同样的 NVL 表达式否则优化器无法把普通col IS NULL匹配到函数索引二是占位值一旦冲突会把真实数据和“缺失”混成同一个索引键那比不用索引更危险。占位值的选择和注释建议都写进建表脚本的说明里。5.2 唯一约束与函数唯一索引更隐蔽的是唯一约束对 NULL 的容忍。Oracle 唯一索引和唯一约束允许多个 NULL 行因为数据库认为“未知”与“未知”不相等自然不违反唯一性。可很多业务要求恰恰相反比如用户扩展属性表里user_id attr_code需要唯一但 attr_code 为空时代表的就是“同一类未指定属性”业务上不应该重复插入。普通唯一约束管不住这种场景数据库会放行多条 attr_code 为 NULL 的记录。解决方案是函数唯一索引CREATE UNIQUE INDEX uk_user_attr ON user_attr( user_id, NVL(attr_code, UNKNOWN) );这样 attr_code 为 NULL 的记录会被统一映射成UNKNOWN再插入第二条同样为 NULL 的记录就会触发 ORA-00001 唯一约束冲突。这里要提醒函数唯一索引报错时的提示信息里约束名要提前维护好否则 DBA 半夜收到告警还得去翻 dba_indexes 才能定位是哪个索引在拦数据。5.3 一次统计任务的排查链路有一回处理定时任务某个分片统计表一天能查询上千万次某天突然从几百毫秒涨到秒级。我的排查过程是先拿实际 SQL 看执行计划发现条件长这样WHERE reserve_time :day OR reserve_time IS NULL普通 B 树索引覆盖了reserve_time的等值条件但完全没覆盖reserve_time IS NULL的分支。优化器为了处理这个 OR放弃走索引选择了全表扫描。当时没有急着建函数索引先看了下reserve_time为 NULL 的数据量发现只有几千行但分布在整个表里。最后的方案是拆分成两个查询非空部分走普通索引NULL 部分单独扫一个小结果集再在应用层合并。这样既保住了索引也不用引入占位值。这个案例说明遇到“索引 NULL”的组合问题最好先评估数据分布不要条件反射式地加函数索引方案要和数据特征匹配。6. 我要求团队遵守的一组 NULL 写法与评审红线总结下来NULL 的问题不是“某个函数不会用”而是散落在比较、过滤、统计、索引、约束各个层面的系统性坑。结合几次踩坑教训我现在对新项目的开发规范做着这几条硬要求大家可以直接抄去用。6.1 红线清单所有比较 NULL 的场景只许写IS [NOT] NULL代码里出现 NULL直接打回不管看起来多像“标准写法”。能写 NOT EXISTS 就不要写 NOT IN。除非子查询列已加非空约束否则 NOT IN 遇到隐藏 NULL 会把整个结果集带崩。LEFT JOIN 的右侧表过滤条件尽量放 ON 子句如果放在 WHERE必须明确意图是“过滤掉未匹配行”。统计报表必须写清楚 COUNT 口径COUNT(*)还是COUNT(列)SUM/AVG 对 NULL 组的返回值是 NULL 而不是 0展示层要统一用 NVL/COALESCE 兜底。建表阶段多用 NOT NULL。真正语义为“未知”的列才保留 NULL能用默认值表达的不要让 NULL 泛滥。索引列上套 NVL/COALESCE 之前要先想好函数索引和占位值不让优化器白折腾。6.2 最后的一点心得把这套规则执行一段时间后我最大的体会是NULL 本身不是 bug它是数据库给出的“诚实回答”。问题常常出在写代码的人心里带着“这里肯定有值”的假设。所以每次写新查询我都会先问一遍自己这个字段要是 NULL结果会变成什么样会不会出现行数变少、统计变成空、关联偏移这类静默问题先想清楚这一层再写 SQL比事后靠压测和日志定位要省力得多。把这四条规则贴在你团队的评审清单开头下一次看到有人写WHERE 列 NULL就直接拉着大家一起复习三值逻辑好了。
阅读完成 · 觉得有帮助？
