NULL这个家伙我愿称之为Oracle里最防不胜防的坑。前两天一个朋友发来一条SQL说月度报表统计人数莫名其妙少了一大截我扫了一眼就发现问题了WHERE条件里写了NOT IN子查询结果里带了一个NULL于是整张表一行都查不出来。这种场景在真实项目里反复出现所以这期干脆把NULL从原理到实战完整拆一遍包括三值逻辑、比较运算、聚合函数、排序分页、Kettle和Python连接Oracle等场景下的处理方式。不管是做后端开发、数据开发还是DBA把这期内容吃透能帮你避开九成NULL相关的线上事故。1. 先搞懂NULL的本质不然操作全是瞎猜1.1 NULL是“未知”不是0也不是空串Oracle里空串还等于NULL很多新手最容易犯的错误是把NULL当成0或者空字符串来处理。实际上NULL在Oracle里的语义是“未知的、不存在的、尚未赋值的”。它不是任何具体值而是一种状态。举个生活化的例子你问同事“这个月提成发了多少”他说“没发过”这是0他说“还没算”这是NULL他说“发了”那才是具体数字。三种情况在数据库里必须分开对待。Oracle有一个特殊规则空字符串在Oracle中就是NULL这和SQL Server、MySQL都不一样。你可以自己验证SELECT NVL(, default) FROM dual;结果是default说明空串确实被当成了NULL。这一点非常容易踩坑尤其是在Java或其他编程语言传入空字符串时存进Oracle就自动变成了NULL很多开发第一次遇到都会愣住。还有一个伴随而来的概念叫三值逻辑。普通SQL的判断结果有TRUE和FALSE但NULL参与判断时会出现第三种结果UNKNOWN。WHERE子句只认TRUEUNKNOWN和FALSE一样都不会返回行。所以WHERE comm NULL这种写法哪怕comm本身也是NULL结果依然是UNKNOWN永远查不到任何行。连NULL自己都匹配不上NULL这就是它最难缠的地方。1.2 NULL参与四则运算和拼接结果一个字全变NULLNULL只要参与普通运算结果基本就是NULL。比如salary comm只要comm是NULL整个计算结果就是NULL哪怕salary是10000也一样。这会导致报表里出现莫名的空值比如查询总金额用了SUM(amount)只要某一行amount是NULL虽然SUM会忽略但如果你用amount tax这种行级运算那一行的结果就是NULL。字符串拼接在Oracle里稍微有点特例。用双竖线||拼接时NULL会被当作空字符串处理比如工号: || empno || ,提成: || comm如果comm是NULL结果不会整体变NULL而是得到工号:7369,提成:。这一点和SQL Server不同SQL Server里a NULL会整体变成NULL。但注意Oracle的普通函数如果参数是NULL多数会返回NULL比如ABS(NULL)就是NULL。所以写表达式时不能想当然地认为拼接没事就等于所有函数都容忍NULL。还有CASE表达式的坑。CASE comm WHEN NULL THEN 0 ELSE comm END看起来没问题实际上永远走到ELSE分支因为comm NULL的结果是UNKNOWN而不是TRUE。正确的写法是CASE WHEN comm IS NULL THEN 0 ELSE comm END。这种“等值匹配NULL”的思维惯性是NULL相关Bug的最大来源。1.3 判断NULL的正确姿势只有两个IS NULL 和 IS NOT NULL判断一个值是否为NULLOracle只提供了IS NULL和IS NOT NULL两种可靠写法。一切 NULL、 NULL、! NULL都是错的语法不会报错但逻辑上永远得不到TRUE等于白写。在实际项目里我养成了一个习惯写完一条SQL全局搜一下有没有出现 NULL或者开头的条件。这种低级错误在Code Review里查不出来只有数据对不上时才会暴露。还有一个组合场景要特别注意当你想判断某个字段“没填”时业务上可能希望把NULL和空串都归为一类在Oracle里这俩本来就是一回事直接用WHERE col IS NULL就行但如果你想表达“填了但填的是空格”那就得用WHERE TRIM(col) IS NULL来覆盖。另外Boolean语义的字段常用的写法是WHERE NVL(flag, N) Y这样NULL和N都会被当成否不会出现某个记录因为flag为NULL而在判断中漏掉的情况。这种NVL兜底的习惯在后面的章节还会反复用到。2. 最容易翻车的四个NULL场景2.1 NOT IN 遇上 NULL结果一锅端一行都不留NOT IN遇到NULL是SQL圈最经典的翻车现场没有之一。看这个例子SELECT ename FROM emp WHERE deptno NOT IN (10, 20, NULL);这条SQL期望查出不在10、20部门的员工但实际结果永远是空的。原因在于NOT IN等价于一系列AND条件的组合deptno ! 10 AND deptno ! 20 AND deptno ! NULL。前两个条件可能为TRUE但第三个条件deptno ! NULL的结果是UNKNOWN整个AND表达式的最终结果就变成UNKNOWNWHERE不认于是所有行都被过滤掉了。更隐蔽的场景是子查询带NULL比如SELECT ename FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept_black WHERE enabled Y);只要dept_black表里有一行的deptno是NULL上层查询就全空。这种问题在测试环境往往发现不了因为测试数据不一定有NULL上线后生产数据一跑就出事。解决办法有三种一是外层加AND deptno IS NOT NULL过滤二是改用NOT EXISTS因为EXISTS走的是关联子查询的TRUE/FALSE判断不会因为NULL挂掉三是在子查询里显式干掉NULL比如WHERE deptno IS NOT NULL包一层。我的习惯是只要逻辑表达“不存在”一律优先写NOT EXISTS少碰NOT IN这是最省心的选择。2.2 COUNT(*) 和 COUNT(列)一个算行数一个算非空数COUNT(*)统计的是结果集的总行数包括所有列为NULL的行COUNT(列名)统计的是该列非NULL的行数NULL行会被忽略。这两者之间的差值在报表里经常就是“缺失数据”的数量。举个例子SELECT COUNT(*), COUNT(comm), COUNT(DISTINCT comm) FROM emp;如果emp表有14行其中5行的comm是NULL那么COUNT(*)返回14COUNT(comm)返回9COUNT(DISTINCT comm)返回去重后的非NULL值个数。做“有提成人数”统计时误用COUNT(*)会让数字偏大反过来统计客户付费数时用COUNT(pay_amount)又可能漏掉金额为NULL但实际有订单的客户。关键是先明确业务口径到底要统计“多少条记录”还是“多少个有值”。另外COUNT(1)和COUNT(*)是一回事都是统计所有行COUNT(NULL)的写法虽然不报错但结果永远是0因为NULL本身被忽略。这个冷知识可以作为面试题问人还挺有意思的。2.3 聚合函数里NULL是隐形人SUM、AVG、MAX、MIN全当没看见SUM、AVG、MAX、MIN四个聚合函数在计算时都会忽略NULL行。也就是说SUM(comm)只对非NULL的comm求和AVG(comm)只对非NULL的comm求平均。这里有一个经常被误解的点AVG(comm)的内部实现等价于SUM(comm) / COUNT(comm)分子分母都忽略NULL所以如果某组数据全部comm为NULLSUM(comm)返回NULLAVG(comm)也返回NULL而不是0。做报表时如果不加NVL包一层前端拿到的就是null值直接显示成空白格非常难看。还有更隐蔽的想算“全员工平均提成没提成的算0”如果用AVG(comm)结果会偏大因为分母只算了有提成的人数。正确的口径应该是SUM(comm) / COUNT(*)或者干脆AVG(NVL(comm, 0))。同理“Oracle查询总金额”这种需求最保险的写法永远是SELECT NVL(SUM(amount), 0) FROM orders否则表为空时SUM返回NULL接口直接报错或前端空白。MAX和MIN也一样全NULL时返回NULL。如果业务上希望空值时不显示离谱数据记得在外层用NVL或者应用层兜底。2.4 排序也不会放过你NULL到底排前还是排后Oracle的ORDER BY遇到NULL有自己的默认规则升序ASC时NULL排在最后降序DESC时NULL排在最前。这个默认行为很容易造成认知偏差尤其从MySQL转过来的人MySQL默认NULL在升序时排最前两边正好相反。如果你希望显式控制NULL的位置Oracle提供了NULLS FIRST和NULLS LAST关键字SELECT ename, comm FROM emp ORDER BY comm ASC NULLS LAST; SELECT ename, comm FROM emp ORDER BY comm DESC NULLS FIRST;排序场景里真正的坑是分页查询。如果排序键列存在大量NULL又没有加二级排序键翻页时同样的数据可能出现在不同页或者某条数据直接消失。原因在于NULL行之间的相对顺序不确定执行计划或数据变动都会影响排序结果。解决办法很简单在排序条件末尾加上一个唯一非空列比如主键SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY e.comm NULLS LAST, e.empno) rn FROM emp e ) t WHERE t.rn BETWEEN 11 AND 20;这个技巧在所有分页需求里都适用不管用ROWNUM还是ROW_NUMBER()排序键里至少有一个唯一列收尾翻页就稳了。3. 处理NULL的函数对照六个高频武器一次讲清3.1 NVL和NVL2最常用的兜底组合NVL是Oracle处理NULL最常用的函数函数签名是NVL(expr1, expr2)expr1为NULL时返回expr2否则返回expr1。比如SELECT ename, comm, NVL(comm, 0) AS real_comm FROM emp;NVL2则是加强版签名是NVL2(expr1, expr2, expr3)expr1非NULL返回expr2为NULL返回expr3。比如做标记列SELECT ename, NVL2(comm, 有提成, 无提成) FROM emp;NVL有两个坑要注意。第一NVL(comm, )在Oracle里没有意义因为空串就是NULL兜底值等于没给。第二NVL要求两个参数的类型兼容否则会报ORA-00932类型不一致错误比如数字列别NVL成字符串。实际开发中我每次写完NVL都会顺手确认一下两个参数的数据类型尤其在高并发报表里这种低级错误特别浪费时间。3.2 COALESCE多候选值依次找非NULL标准SQL通用COALESCE是标准SQL语法Oracle、PostgreSQL、MySQL都支持。它接受多个参数从左到右依次判断返回第一个非NULL的值如果全部为NULL就返回NULL。比如SELECT COALESCE(basic_salary, performance_salary, subsidy, 0) FROM emp_salary;这个函数非常适合“多字段取第一个有值”的场景比如员工工资由基本工资、绩效工资、补贴等多个字段组成要取实际生效的那个。和NVL的关键区别在于COALESCE可以传多个参数NVL只能两个而且在标准SQL里COALESCE更通用跨数据库迁移时不容易出问题。COALESCE还有一个特性是短路求值也就是说从左到右一旦找到非NULL值后续参数就不会计算。这个特性偶尔能用来避免除零异常但实际用得不多知道有这回事就行。3.3 NULLIF两值相等就变NULL防除零利器NULLIF的签名是NULLIF(expr1, expr2)如果expr1等于expr2返回NULL否则返回expr1。最经典的使用场景是防除零SELECT amount / NULLIF(days, 0) AS daily_avg FROM orders;如果days为0NULLIF(days, 0)返回NULL整个除法结果就是NULL不会报ORA-01476除数为0。外层再套一个NVL就能安全地展示数据了。另一个用法是做数据清洗NULLIF(TRIM(col), )可以把空串或纯空格的字符串转成NULL。虽然Oracle里空串等于NULL但TRIM函数的结果可能真的是一个空串用NULLIF包一下能让数据口径更统一。需要注意一点两个参数类型要一致否则也会报类型错误。3.4 LNNVL条件反选专门找回UNKNOWN的行LNNVL是Oracle一个很冷门但好用的函数功能是“将条件求反并且把UNKNOWN也当成TRUE”。语法是LNNVL(condition)当条件结果为FALSE或UNKNOWN时返回TRUE。举个例子SELECT ename FROM emp WHERE LNNVL(comm 1000);这条SQL会返回comm小于1000的员工以及comm为NULL的员工。如果用普通写法WHERE NOT (comm 1000)comm为NULL的行不会返回因为NOT UNKNOWN还是UNKNOWN。很多人在写“排除逻辑”时漏掉NULL行LNNVL就是专门补这个洞的。当然你也可以不用LNNVL写成WHERE comm 1000 OR comm IS NULL效果一样。但有些场景条件很复杂比如多个条件取反LNNVL能让SQL更简洁。需要提醒的是LNNVL可能会影响索引使用数据量大的时候要跑一下执行计划再定。3.5 DECODEOracle老牌函数NULL匹配的独家通道DECODE在Oracle里的行为很特殊DECODE(expr, search1, result1, search2, result2, ..., default)它逐对比较expr和search值找到第一个匹配就返回对应的result。关键在“匹配”的逻辑上DECODE在比较时会把两个NULL视为相等这和普通的完全不同。于是你可以写SELECT ename, DECODE(comm, NULL, 0, comm) FROM emp;这句话的意思就是comm为NULL时返回0否则返回comm本身。这在老版本的Oracle世界里是NVL的替代方案直到现在很多旧系统里还能看到这种写法。DECODE配合聚合可以做简单的行列转换比如按月份把多行数据转成一行的经典场景。理解它对NULL的特殊待遇能帮你读懂老代码也能让你在处理“NULL即一种取值状态”的业务时多一个选择。4. 真实项目里的NULL处理实录4.1 分页查询排序键带NULL翻页翻出重复和漏行生产环境里最常见的一个分页事故是第一页和第二页数据互相重复或者某一条数据在第一页出现翻到第二页又消失。很多人第一反应是分页语句写错了其实多半是排序键的问题。比如ORDER BY comm ASC如果comm列有大量NULLOracle默认把NULL行放到最后但在NULL行内部并没有稳定顺序。表里的数据一旦发生变更或者CBO选择了不同的执行计划NULL行的输出顺序就会变化。而Rownum分页是先取前N行再切顺序一变数据就乱了。解决方案是排序条件加二级键强烈建议用主键SELECT * FROM ( SELECT e.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY comm NULLS LAST, empno ) e WHERE ROWNUM 20 ) WHERE rn 10;如果用的是ROW_NUMBER()窗口函数同样记得在ORDER BY里补一把唯一键。这是一个非常容易忽略但影响很大的细节我经手的项目里凡是生产环境的分页SQL我Review时第一眼就找排序键里有没有主键收尾没有就要求改。4.2 Kettle空字符串与NULL局部修改的设置方法Kettle做数据同步时空字符串和NULL的转换问题是高频Bug之一。不同版本和不同步骤对空串的处理不一致有的会把空串转成NULL有的会保留空串。而Oracle因为空串等于NULL看起来就是所有空值都变成了NULL容易让业务误以为数据丢失。Kettle里控制这个行为的开关主要有几处“文本文件输入”步骤中字段页签下有类似“Treat empty strings as NULL”的选项勾选后空串会转成NULL取消则不转。“表输入”步骤可以在SQL里用NVL或CASE把空串统一处理。“字段选择”步骤的元数据页签可以对特定字段单独设置“Null if”和“If null”值实现局部修改。“表输出”步骤也有“Treat empty strings as NULL”的选项决定写入目标表时是否把空串转NULL。实操中我的做法是先在源头统一口径Excel或CSV里真正的空细胞如果业务上代表“无”就直接转NULL如果代表“待补充”就不要转NULL而是映射成一个占位字符串。但这里有个Oracle特有的坑要提醒就算Kettle不转只要空串写进Oracle的VARCHAR2列它还是会被当成NULL因为Oracle内部就是这种规则。所以如果业务要区分“没填”和“填了空”不能在Oracle的一列里做到要么用空格占位要么加一个状态字段。这个属于模型层面的取舍建议在数据同步方案评审阶段就明确。4.3 PL/SQL存储过程入参是不是NULL别用等号判断写存储过程时NULL判断的失误率极高。最常见的是这么写IF p_comm NULL THEN ...或者IF p_comm THEN ...这两条分支永远进不去因为结果都是UNKNOWN。Oracle中空串等于NULL但 NULL依然不成立。正确写法只有一个IF p_comm IS NULL THEN ...一个完整的例子是这样CREATE OR REPLACE PROCEDURE p_update_comm( p_empno IN NUMBER, p_comm IN NUMBER ) IS BEGIN IF p_comm IS NULL THEN UPDATE emp SET comm NULL WHERE empno p_empno; ELSE UPDATE emp SET comm p_comm WHERE empno p_empno; END IF; COMMIT; END;还有动态SQL拼条件的场景比如多条件查询员工列表入参可能什么都不传WHERE 1 1 AND (p_deptno IS NULL OR deptno p_deptno) AND (p_job IS NULL OR job p_job)这里的逻辑是入参为NULL时忽略条件不为NULL时才过滤这是PL/SQL开发里最标准的写法。另外提醒一句Java端传过来的空字符串在Oracle里就是NULL如果你在存储过程里把它当普通字符串处理很容易在NOT NULL约束上栽跟头。4.4 Python连接Oracle查询NULL变None插入NULL用None现在Python连接Oracle推荐用官方新的python-oracledb驱动它是cx_Oracle的后续版本安装很简单pip install oracledb查询数据时Oracle的NULL会自动映射成Python的None这个很好理解。需要注意处理时不要拿None去做字符串拼接否则会得到None这种尴尬结果。正确做法import oracledb conn oracledb.connect(userscott, passwordtiger, dsnlocalhost:1521/orcl) cur conn.cursor() cur.execute(SELECT ename, comm FROM emp WHERE deptno 30) for name, comm in cur: if comm is None: print(f{name}: 没有提成) else: print(f{name}: {comm})插入或更新数据时如果要写NULL直接传None给绑定变量cur.execute( UPDATE emp SET comm :1 WHERE empno :2, (None, 7369) ) conn.commit()这里有个Python层面的坑Python的空字符串绑定到Oracle的VARCHAR2参数后会被当成NULL。所以如果你的业务要区分“空串”和“NULL”在Python代码层就要先把语义处理好比如约定用空格代替空串或者干脆不让空串进入数据库。这个细节经常在接口联调时才暴露双方互相甩锅最后发现是Oracle空串即NULL的规则作祟。5. 高频NULL问题速查表与排查清单场景错误/坑爹写法正确/推荐写法判断字段是否为NULLWHERE col NULLWHERE col IS NULL判断空串OracleWHERE col WHERE col IS NULLNOT IN含NULLWHERE col NOT IN (1, 2, NULL)用NOT EXISTS或先过滤NULL统计总行数COUNT(col)忽略NULL明确口径该用COUNT(*)就用COUNT(*)统计某列非空数COUNT(*)包含NULL行COUNT(col)某列全NULL求和SUM(col)返回NULLNVL(SUM(col), 0)全员平均NULL算0AVG(col)SUM(col) / COUNT(*)或AVG(NVL(col, 0))排序指定NULL位置依赖默认ORDER BY col NULLS LAST/FIRST分页排序键有NULL只排业务列排序键末尾加主键拼接可空列完全不处理NVL(col, )或明确NULL语义防除零a / b直接报错a / NULLIF(b, 0)排除逻辑要含NULLNOT (col 1000)LNNVL(col 1000)或col 1000 OR col IS NULL日常排查时我有一套固定的自查清单先看WHERE条件里有没有 NULL这类写法再看子查询结果集可能包含NULL的列尤其是NOT IN和NOT EXISTS然后统计类SQL明确COUNT和SUM的口径聚合结果统一用NVL兜底最后凡是分页查询检查排序键末尾有没有唯一列。这套流程走下来能拦下绝大多数NULL引发的数据问题。还有两个和性能相关的点值得单独说。一是WHERE col IS NULL通常不走普通B树索引因为全NULL的行不会进入索引条目数据量大时可能全表扫描。如果这个查询很频繁可以建函数索引CREATE INDEX idx_emp_comm_nvl ON emp(NVL(comm, 0));查询时条件也要写成WHERE NVL(comm, 0) 0索引才会生效。二是唯一约束允许多行NULL并存如果你需要“非空唯一”得在表上加约束或配合函数索引来实现否则用户没填的值可能重复插入都成功这种数据问题比NULL本身更难发现。顺带提一句热搜里有个“Oracle过滤不可转为数字的字符串”的问题它和NULL经常一起出现。脏数据里既有NULL又有非数字字符串处理时就分两步NULL用NVL、COALESCE归置非数字字符串用REGEXP_LIKE或CASE WHEN先过滤一层再TO_NUMBER转换。千万别直接对整列做TO_NUMBER一个脏值就会让整条SQL报ORA-01722。我个人这些年最大的体会是NULL问题的根源不在语法而在建模。凡是允许NULL的列所有相关SQL都必须多问一句NULL在这里代表什么是没填、不适用、还是零很多线上事故比如报表金额归零、统计人数变少、接口返回字段缺失最后追查下来都指向这个没问清楚的问题。另外一个小习惯在Oracle里写任何涉及可空列的表达式先写一层NVL或COALESCE兜底等业务场景稳定后再考虑去掉这个顺序能帮你省下大量排查时间。如果这期内容对你有用也欢迎把你自己踩过的NULL坑丢到评论里一起长长记性。