聊到 sql null很多人第一反应是这不就是空值吗用 IS NULL 判断一下不就行了。但真正开始写统计SQL、做数据清洗、做性能优化的时候NULL带来的坑多得能把人埋进去。上个月给业务拉订单支付数据我随手写了句 SUM(pay_amount)结果返回一行 NULL报表直接白屏。排查下来发现是导入工具把整列写成了 NULL聚合函数把所有行都“忽略”了。这种问题在 SQL 里太典型了值得把整个体系梳理一遍。不管是 MySQL 还是 SQL Server、Oracle、PostgreSQLNULL 的处理逻辑有共性也有很多数据库私货。今天就把 NULL 到底是什么、怎么写判断、怎么处理排序、聚合、去重、连表、索引、清洗一次性讲清楚。全文适合日常写 SQL 的运营、数据分析、后端开发看也适合刚入门数据库的新手内容偏实战不会有太多纯理论空谈。1. NULL到底算个什么先把三值逻辑讲透1.1 NULL不是空值是“未定义”先解决一个最基础的认知问题NULL不是空值它是“不知道”“还没填”“没有定义”。我常拿登记表举例子一张员工表里婚姻状况这一栏如果空着你不能说这个人就是“未婚”他可能是已婚但没填也可能是不透露甚至可能是系统导入时忘了迁移。这个“空着的状态”才叫NULL。对比一下0是“没有金额”的数字表示空字符串是“没有任何字符”的文本这两个都是确定的值。NULL则是“这个值压根不存在”。在SQL的世界里NULL既不等于0也不等于空字符串更不等于false。很多新人把这三个概念混成一锅粥后面写出来的SQL自然到处漏风。这个认知直接决定了后面所有写法。你只有打心底里承认“NULL不是一个值而是一种状态”才可能理解为什么比较运算符在NULL面前会失灵为什么聚合函数会忽略它为什么索引在它面前会犹豫。1.2 三值逻辑为什么查着查着就数据少了普通语言里判断条件通常只有true和falseSQL里却有个第三态UNKNOWN。任何值与NULL做比较结果都是UNKNOWN只有 IS NULL 和 IS NOT NULL 能得到 TRUE/FALSE。举例找出工资大于10000的员工WHERE salary 10000不会返回salary是NULL的行因为NULL 10000的结果是UNKNOWN不是FALSE也不是TRUE。如果你以为结果是“工资低于或等于10000的人”那就错了NULL行被静默丢掉了。实际业务里这个坑特别隐蔽。比如查“不是管理层的员工”写WHERE manager_id IS NULL逻辑上没问题但如果写WHERE manager_id NULL那好一条都查不出来因为NULL NULL也是UNKNOWN。这个错误不会报错所以特别容易漏过去。CHECK约束也是三值逻辑的受害者CHECK(age 18)在age为NULL时是通过的因为UNKNOWN不算违例。如果你真要求所有人必须成年得写成CHECK(age 18 OR age IS NULL)再配合NOT NULL或者干脆约束age IS NOT NULL AND age 18。这类问题在数据库设计评审时不注意上线之后就是脏数据。1.3 不同数据库对NULL的隐藏差异跨数据库迁移时NULL的差异很容易踩雷。最典型的是Oracle空字符串会被当成NULL。在Oracle里往一个VARCHAR2列插取出来就是NULL。MySQL、SQL Server、PostgreSQL则把和NULL分得很清楚所以从Oracle迁到MySQL时原来“用空串表示没填”的逻辑全部要重新梳理。排序的默认位置也不一样。MySQL认为NULL最小升序时排在前面SQL Server、Oracle、PostgreSQL则认为NULL最大升序时排在最后。后面我专门讲排序时会展开这里先提个醒。还有唯一约束上的差异MySQL和Oracle的唯一索引允许多个NULL存在也就是说可以插入多行唯一键为NULL的记录SQL Server唯一索引却只允许一个NULL至少旧版本是这样。这些坑在数据迁移和主数据维护时一旦撞上排查成本奇高而且报错信息往往很隐晦不会直接告诉你“是NULL导致的”。2. 判断、转换、兜底日常SQL里最常用的NULL处理写法2.1 判空必须用IS NULL不能用 NULL判断NULL的唯一正确姿势就是 IS NULL 和 IS NOT NULL。不要用 NULL、! NULL、 NULL这些写法在SQL里不会报错但永远返回UNKNOWN等于白写。举个例子你写SELECT * FROM t WHERE phone ! NULL本意是找出所有填了手机号的用户结果查出来是空集因为没有一行能通过条件。正确写法是WHERE phone IS NOT NULL。注意别把SQL Server的ISNULL函数搞混。ISNULL(phone, 000)是“如果phone为NULL就返回000”的取值函数和 IS NULL 判断是两码事。不同数据库这个函数的名字还不一样MySQL叫IFNULLOracle叫NVL作用都很接近但判空的写法永远只有IS NULL这一套。还有一个常见的组合场景很多系统里“没填”既可能是NULL也可能是空字符串尤其从Excel导入的数据空格的、空串的、NULL的掺在一起。处理时要么统一口径要么写成WHERE col IS NOT NULL AND col 把两种情况都排除掉。这个写法在数据清洗时几乎是必用。2.2 COALESCE和IFNULL多列取首值COALESCE是我用得最多的NULL处理函数。它接收一串参数从左往右取第一个非NULL的值比如COALESCE(NULL, NULL, 5)返回5。好处是参数数量不限而且几乎所有主流数据库都支持跨库迁移基本不用改。用它做展示层兜底特别好比如用户昵称为NULL时显示“匿名用户”地址三要素拼接时某一段为空用COALESCE(city,) || COALESCE(area,) || COALESCE(address,)就能避免整段变成NULL。注意这个例子是Oracle和SQL Server的写法MySQL拼接要用CONCAT但思路一样。用的时候有个细节COALESCE的参数类型尽量保持一致。COALESCE(price, 0)没问题但你要是把字符串和数字混在一起数据库会做隐式转换小表还好大表可能影响索引使用。哪怕只是为了让SQL更可读建议显式CAST一下或者干脆在应用层处理完再传参。2.3 NULLIF的妙用反向兜底如果说COALESCE是把NULL变成值那NULLIF就是把特定值变成NULL两者正好反过来。NULLIF(a, b)在a等于b时返回NULL否则返回a。最实用的场景是除零保护。统计人均订单金额时如果没有订单COUNT(*)就是0SUM/0直接报错。写成SUM(amount) / NULLIF(COUNT(*), 0)当COUNT为0时NULLIF返回NULL除法的结果就是NULL外面再包一层COALESCE(... , 0)问题就解决了。很多报表系统的除法统计逻辑都是这么写的。它还有一个妙用把业务里的“魔法值”临时转成NULL。比如某个老系统用-1表示“未填写”你想按NULL逻辑处理直接SELECT NULLIF(score, -1) FROM t后面就能用IS NULL判断了不用改表结构非常实用。2.4 CASE WHEN做业务分支当你要对NULL做多分支处理时CASE WHEN最清楚。比如订单的优惠金额字段可能是NULL、可能为0、可能是正数要分三类统计写CASE WHEN coupon IS NULL THEN 未使用 WHEN coupon 0 THEN 无优惠 ELSE 已优惠 END就好读很多。CASE WHEN的执行顺序是从上到下第一个满足条件的就返回。建议把最严格、最具体的条件放前面NULL判断放前面也可以看业务优先。别为了少写几行用一堆嵌套函数可读性在后期维护里比什么都重要。我见过一个可怕的报表SQL里面套了四层IFNULL加上两个CASE目的是把NULL、空串、空格、null字符串全部归一化。这种SQL能跑但一旦业务口径变化改起来想死。遇到这种复杂清洗我宁愿先开个临时表做一层预处理。3. 排序、去重、连表NULL最容易引发事故的三个场景3.1 ORDER BYNULL的位置并不固定排序时NULL的位置在不同数据库里完全不一样MySQL里NULL默认最小ORDER BY col ASC时NULL在最上面SQL Server、Oracle、PostgreSQL里NULL默认最大ASC时NULL在最后面。你要是写过跨库报表肯定能体会到这个不一致有多烦。统一排序规则的方法一个是标准语法PostgreSQL、Oracle、MySQL 8.0都支持NULLS FIRST/LAST比如ORDER BY col ASC NULLS LAST意思是升序但NULL排在最后。SQL Server包括2008 R2不支持这个语法得用表达式ORDER BY CASE WHEN col IS NULL THEN 1 ELSE 0 END, col。这样NULL永远排最后。这个特点在实际业务里很有用。比如用户列表按最后登录时间倒序没登录过的用户你不能让他排最前面于是ORDER BY CASE WHEN last_login IS NULL THEN 1 ELSE 0 END, last_login DESC先让NULL沉底。排序结果对了产品UI那边就不用再做二次处理。3.2 DISTINCT和GROUP BY的NULL归组DISTINCT会把所有NULL看成同一个值。也就是说一列里只要有一个NULL去重之后最后只会保留一个NULL行。这在大多数场景下是合理的但你要知道它是这个行为。GROUP BY也一样NULL会单独成一组这一组代表“未知的所有值”。报表上如果出现一个null分组别愣着说明有脏数据或者字段设计上有可空列。还有一个容易被忽略的坑多列去重时只要其中有任何一列不为NULL行就不会被合并。比如按(city, address)去重一行是(北京, NULL)另一行是(北京, 朝阳路1号)这两行不会被合并。所以“去除空值”要在去重之前先处理像写UPDATE把NULL统一成空串或固定占位值然后再做DISTINCT。3.3 JOIN与NOT IN子查询让你悄悄丢数据这是NULL引发的最高频事故没有之一。原因就在于NULL NULL结果是UNKNOWN连接条件里只要有一方是NULL两行就匹配不上。拿INNER JOIN举例A表有1000个订单其中10个订单的user_id是NULLJOIN用户表后这10行直接消失。你以为是数据没匹配上其实是NULL不参与匹配。LEFT JOIN稍好主表行会保留但关联表字段全是NULL。更隐蔽的是NOT IN。假设要查没有下过单的用户你写WHERE id NOT IN (SELECT user_id FROM orders)。只要orders.user_id列里有一个NULL整个查询返回空集。为什么因为NOT IN的逻辑是“id不等于子查询里任何一个值才算通过”而id ! NULL永远是UNKNOWN没有任何行能通过。解决方法是把NOT IN改成NOT EXISTS或者先过滤子查询里的NULLWHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL)。我个人强烈建议凡是能写NOT EXISTS的地方就写NOT EXISTS语义更安全性能通常也更好。3.4 唯一约束和主键NULL不是“没填”那么简单主键天然不能为NULL这个大家都理解。但唯一索引和NULL的关系不同数据库的处理方式差别很大。MySQL和Oracle认为“未知值不能互相比较”所以允许插入多条唯一键为NULL的记录SQL Server则把NULL当做一个普通唯一值旧版本只允许一条NULL。这个差别会导致同样的表结构在不同数据库上行为完全不同。业务上的后果举一个我们有个用户推荐码字段要求唯一。没推荐码的用户在MySQL里可以插一万条NULL都没问题同样的表迁到SQL Server第二次插入的时候就报唯一键冲突。遇到这种需求推荐码字段不如直接设NOT NULL没推荐的存空串或UUID省得迁库时炸。更通用的建议是凡是会参与唯一判断的字段最好不要允许NULL。如果业务允许“未填写”那就用空串、用0、用默认值把未知状态显式表达出来。数据库设计阶段多想一步后面能少加很多班。4. 聚合统计和窗口函数报表里的NULL比你想象的更坑4.1 COUNT(*)和COUNT(列)根本是两回事COUNT(*)统计行数不管这些行里有没有NULLCOUNT(列)只统计该列非NULL的行数COUNT(DISTINCT 列)只统计该列去重后非NULL的取值个数。这三者的差别在报表里能直接让你算错指标。最常见的失误是用COUNT(phone)统计总人数结果比实际人数少。因为一部分用户没填手机号那一行就被COUNT(列)扔掉了。正确的做法是SELECT COUNT(*) AS total_user, COUNT(phone) AS has_phone FROM user各自表达自己的口径。COUNT(DISTINCT)也有点反直觉一列全是NULL时COUNT(DISTINCT col)返回0不是1。很多新人以为NULL也要算一个“去重后的值”其实不算。记住这个规则写指标口径时就不会糊了。4.2 SUM/AVG的忽略逻辑与统计口径SUM和AVG在计算时会自动忽略NULL行。这个“自动忽略”有时候是好事有时候是灾难。全部行都是NULL时SUM返回NULL而不是0AVG也返回NULL。你在报表里如果不包COALESCE前端拿到NULL直接展示一个空值看着就很不专业。更麻烦的是AVG的口径问题。假设一个班级10个人2个人缺考成绩列是NULL。AVG(score)算出来是8个人成绩的平均分分母是8不是10。如果你要算全班平均分缺考按0分处理就得写AVG(COALESCE(score, 0))分母就变成了10。这个口径差异在KPI统计里经常引发争论。我一般先问业务NULL是应该被忽略还是应该按默认值参与计算确认清楚再写SQL。别自己拍脑袋最后统计结果对不上挨骂的还是写SQL的人。4.3 GROUP BY和窗口函数中的NULLGROUP BY会把NULL单独分一组这点前面说过。报表上如果不想看到null分组可以在分组前用COALESCE把NULL变成未知或者未填写语义值。别小看这一步很多看报表的业务人员看到“null”两个字根本不知道什么意思。窗口函数里NULL的坑也不少。比如RANK()和DENSE_RANK()排序时NULL按数据库默认规则排在最前或最后会直接影响排名结果。你希望NULL排最后同样的先ORDER BY一个辅助表达式控制NULL位置。还有LAG/LEAD这种取上下文的函数取出来的值本身可能带NULL。比如算“环比增长”上个月金额是NULL这个月是100直接算差值就是NULL。稳妥做法是用LAG(amount, 1, 0)给默认值或者外面套COALESCE。如果连默认值都没有表示数据缺失那比0更值得关注需要人工排查。5. 索引与慢SQL带NULL的字段为什么有时快有时慢5.1 索引对NULL的存储与利用先说一个流传很广的说法索引不存NULL所以IS NULL查不到索引。这个说法不严谨。在MySQL InnoDB里二级索引是会包含NULL值的至少索引行存在但优化器对IS NULL的处理确实比较保守很多时候IS NULL条件的成本估算偏高于是走了全表扫描。实践中的结论是别指望IS NULL能稳定走索引。如果你的查询热点是WHERE deleted_at IS NULL且删除时间大部分行都是NULL那么这列上建索引意义不大因为要扫的数据太多了全表扫更快。真正能充分利用索引的条件是那些带具体值的等值查询。比如WHERE status 1 AND deleted_at IS NULLstatus和deleted_at的复合索引通常能用上因为优化器先用status 1把范围缩小了。但如果你条件里只有deleted_at IS NULL索引可能就不受待见。这种反直觉的行为只有结合成本模型才能理解。5.2 explain主要看哪些信息排查慢SQL时EXPLAIN是第一步。主要看这几列type代表访问类型从好到差大致是 const、eq_ref、ref、range、index、ALL看到ALL就得警惕全表扫描possible_keys和key看候选索引和实际用的索引key等于NULL说明没走索引rows是优化器估算的扫描行数这个数越大基本越慢Extra里的Using where、Using index、Using temporary、Using filesort也各有含义。具体到NULL处理上如果你写WHERE col NULL这种错误表达式执行计划往往会显示key为NULL、type为ALL因为条件恒为UNKNOWN优化器也不知道怎么用索引。另外WHERE (col 1 OR col IS NULL)这种查询由于OR的存在很难用到复合索引最好拆成UNION ALL或者改成IN (1)再专门处理NULL部分。我踩过的一个坑是给一个大表加了索引EXPLAIN显示走了索引但实际查询还是很慢。后来发现是查询里OR了多个条件优化器在某个版本里就是不走复合索引只能靠调整SQL结构解决。EXPLAIN只是参考生产环境最好再用真实数据和ANALYZE验证执行计划。5.3 建表设计NOT NULL DEFAULT还是允许NULLNULL问题最好在设计阶段解决而不是在查询阶段补救。我的经验是先问一问这个字段的NULL有没有业务含义。如果NULL表示“用户确实没有这个值”比如离职时间那保留NULL如果NULL只是占位比如创建人、状态、默认金额那就NOT NULL DEFAULT加上合理默认值。数值字段用0代替NULL前提是0没有特殊业务含义。文本字段常用空字符串代替NULL。时间字段麻烦一点有些系统用1970-01-01或9999-12-31当默认值但这类魔法值会让报表口径更乱我一般只建议“必须填”的时间字段用NOT NULL比如创建时间而允许为NULL的时间字段就老老实实保留NULL。最后提一句ORM和SQL生成器也需要统一约定。Java开发用MyBatis时if testfield ! null里面传NULL和故意不传可能拼出不同的SQL代码里对NULL和空串的区分决定了数据落库后的形态。这个约定最好写进团队规范里别等出问题时再扯皮。6. 数据清洗实战从脏数据到可用数据6.1 清洗SQL去除空值和替换空值数据清洗最常见的需求有两个去掉NULL行或者把NULL替换成有业务含义的值。去掉NULL行的写法很简单DELETE FROM t WHERE col IS NULL生产环境不要直接DELETE大表建议先SELECT确认数据量再分批删。替换NULL的常见SQLUPDATE user SET phone WHERE phone IS NULL;UPDATE user SET score 0 WHERE score IS NULL;一遍过后查询语句就可以直接COUNT(col)和SUM(col)了。这个操作我一般放在例行数据维护脚本里每周跑一次防止业务系统漏数据。另一种情况是查询时不想动表数据只想临时处理。MySQL可以SELECT COALESCE(NULLIF(TRIM(col), ), 未知) AS col_name FROM t把空格、空串、NULL统一变成未知。注意TRIM后面嵌套顺序先去掉空格空串再用NULLIF转NULL最后COALESCE兜底。很多人不会用NULLIF其实在这种场景里它是最优雅的工具。6.2 完整案例订单支付完成率统计来个综合一点的例子。一张orders表存储订单字段包括order_id、user_id、pay_amount、pay_time支付时间的NULL表示该订单还没支付。现在要出一份报表总订单数、已支付订单数、支付总额、支付用户数、人均支付金额。SQL可以这样写SELECT COUNT(*) AS total_orders, COUNT(pay_time) AS paid_orders, COALESCE(SUM(pay_amount), 0) AS total_amount, COUNT(DISTINCT CASE WHEN pay_time IS NOT NULL THEN user_id END) AS paid_users, COALESCE(SUM(pay_amount) / NULLIF(COUNT(pay_time), 0), 0) AS avg_amount FROM orders;这里COUNT(pay_time)统计的是已支付订单行数因为只有支付过的行pay_time才非NULLSUM(pay_amount)其实就是支付金额合计因为未支付行的pay_amount一般是NULL最后人均支付金额用SUM/NULLIF(COUNT(pay_time), 0)避免除零。注意如果未支付订单pay_amount也填了0而不是NULL那COUNT(pay_time)依然能正确区分SUM也正常。但如果pay_amount全是NULLSUM返回NULL必须COALESCE。手工写报表时我会把每步结果先单独查一遍确认口径。这个习惯帮我省了很多返工时间。6.3 应用层与SQL配合的几个注意点SQL层的NULL处理只是链路的最后一环。数据从应用层进来的时候如果一开始就没约定好后面清洗成本很高。比如Java里用MyBatis插入用户如果phone字段是NULL写进数据库是NULL还是空串取决于insert语句里有没有做COALESCE和if判断。接口对接场景也有类似问题。第三方传来的JSON里某些字段是null到了你数据库里如果直接落库就变成了数据库的NULL等你要做统计时又回到前面说的那一堆坑。所以我建议在数据入口层就做转换外部null统一按业务规则转成未知、0或空串能减少很多下游麻烦。另一个注意点是代码里查出来的结果映射到对象时数据库NULL到了Java里就是null到了Python里就是None前端拿到可能是空。这个传播链很长任何一环没统一最终展示都会出问题。所以团队规范里最好白纸黑字写清楚NULL、空串、0分别代表什么。7. 排查实录与避坑清单7.1 常见问题速查表整理一个常见的症状对照表排查时对着看就行现象可能原因推荐解法WHERE col NULL 查不出数据比较返回UNKNOWN非结果为真改用IS NULLNOT IN子查询结果空集子查询结果含NULL改NOT EXISTS或过滤NULLCOUNT(列)明显比COUNT(*)少列中存在NULL被忽略统计总行数用COUNT(*)SUM(列)显示NULL该列全为NULLCOALESCE(SUM(col), 0)AVG(列)与业务口径不符NULL被忽略分母变化AVG(COALESCE(col, 0))ORDER BY后NULL位置不对数据库默认不同NULLS LAST或CASE WHENLEFT JOIN右表字段为NULL关联键有NULL或确实无匹配查关联键是否NULL唯一索引插入多条NULL无报错不同库对NULL唯一性处理不同唯一字段设NOT NULL报表里多了一个null分组GROUP BY对NULL单独成组提前COALESCE成有业务含义值这张表我打印出来贴过工位新同事来问问题先让他们对着表自查一遍很多问题不用我出手就解决了。7.2 别把各种“null报错”混淆在网上搜SQL NULL相关资料的时候会搜出一堆和数据库无关的报错比如NPM的“cannot read properties of null (reading edgesout)”、Nacos配置里的serverAddrnull、内核日志里的kernel null pointer dereference还有各种HTTP接口返回里的data: null。这些都属于“应用层空指针/空对象”问题和SQL的NULL语义完全是两码事。数据库的NULL是一种数据值应用层的null通常表示内存里没有这个对象。排查的时候先分清报错出现在哪个层面是SQL查询结果不对还是程序运行时对象为null还是框架配置中出现了字符串null。别被关键词误导方向错了会白折腾很久。我见过群里有人贴了个NPM报错问“是不是数据库NULL导致”底下还有人认真分析其实完全是前端依赖安装的问题。搜索关键词是一回事定位问题还是要靠经验和上下文判断。7.3 最后一点经验写SQL这么多年我个人的体会是所有数据统计类SQL写完必须用边界数据测一遍。所谓边界数据就是空表、全NULL列、只带NULL关键字的行、超大值、极小值。这几个用例跑完很多NULL相关的坑都能提前暴露。另外就是多跟业务方确认口径。NULL是被忽略还是按默认值参与统计直接决定最终数字而这个数字会影响业务决策。宁可写慢一点也要问清楚。最后送上一句实用建议团队里建一份简单的NULL处理速查卡把判空写法、排序差异、聚合口径、NOT IN陷阱写进去。新人入职看一遍能少踩一半的坑。这份东西我用了几年每次有人拿着诡异的结果来问对照速查卡基本十分钟内就能定位。