外观
10 - SQL 中的 NULL 为什么不等于“空”
摘要与阅读定位
NULL 表示值缺失、未知或不适用,不能当作零或空字符串比较。条件涉及它时,除了真、假,还可能得到“未知”(UNKNOWN);这就是 SQL 的三值逻辑。理解它,才能解释排除查询为什么漏行、外连接如何区分“没记录”和“字段为空”、平均数为什么改变分母。本篇为 05 JOIN、06 过滤位置、07 聚合窗口提供这些基础,也解释 03 字段约束中 CHECK 与唯一键的边界。
基线为 MySQL 8.0.16+ / 8.4、InnoDB;8.0.16 之前不能依赖 CHECK 真正执行约束。初始化与留证遵循 A。以下结果是推导预期,实际行为以目标版本和配置为准。
前置只需 01 查询基础和 03 非空约束。初次先读下面的业务例子与第 1 节,再做第 3 节黑名单对照;聚合、约束和 Java 映射第二遍再读。五张表初始化服务于不同验证,不要求一次理解所有边界。
从业务问题开始:没有支付时间,算不算“早于今天”
假设订单的 paid_at 字段保存支付时间。订单已经创建,但支付时间为 NULL,此时 paid_at < '2026-10-01' 无法判断它是否早于 2026 年 10 月 1 日,因为比较所需的时间未知。这里的日期是固定比较边界,不表示查询执行当天;SQL 不会替你猜一个过去或未来时间。
所以“支付时间未知”“没有支付记录”“支付金额为 0”和“支付备注是空字符串”是不同状态。NULL 可以表达缺失、未知或不适用,但如果业务需要区分原因,还应增加状态或原因字段,不把所有差异都丢掉。
以下无需建表即可运行的小表达式说明差别:
sql
SELECT 0 = 0 AS known_zero,
NULL = 0 AS unknown_vs_zero,
NULL IS NULL AS missing,
'' IS NULL AS empty_text_is_missing;MySQL 预期输出为 1, NULL, 1, 0,其中 1 表示真,0 表示假。零是已知值,所以 0 = 0 为真;NULL = 0 缺少比较所需的值,所以结果未知。IS NULL 专门检查是否缺失,因此 NULL IS NULL 为真,而空字符串 '' 不属于 NULL。
1. 原理:UNKNOWN 不是 FALSE
NULL 描述值的状态,UNKNOWN 描述条件的判断结果。普通比较运算符 =(等于)、<>(不等于)、<、> 遇到 NULL 时,结果是 UNKNOWN,在 MySQL 查询输出中显示为 NULL。即使两侧都是 NULL,也不能认定它们代表相同的已知值。
检查缺失用 IS NULL。MySQL 的 NULL 安全等号 <=> 则明确处理含 NULL 的两侧:NULL <=> NULL 为真,1 <=> NULL 为假。它能判断两侧是否同时缺失,但不会恢复缺失的业务信息。
sql
SELECT NULL=NULL AS ordinary_equal,NULL<>NULL AS ordinary_not_equal,
NULL IS NULL AS missing,NULL<=>NULL AS null_safe_equal,
1<=>NULL AS known_vs_missing;预期输出为 NULL,NULL,1,1,0。组合条件时,AND 表示“且”,OR 表示“或”,NOT 表示取反。未知条件与其他条件组合后,结果不一定仍未知,也不能直接把未知替换为假。
不用先背表:条件“金额大于 100 且已取消”,如果已知没有取消,则整个条件一定不成立;条件“金额大于 100 或已取消”,如果已知已经取消,则整个条件成立。否则缺失金额可能让结论仍未知。这解释了 FALSE AND UNKNOWN 为 FALSE、TRUE OR UNKNOWN 为 TRUE。
| 左值 | 右值 | AND | OR |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| TRUE | UNKNOWN | UNKNOWN | TRUE |
| FALSE | TRUE | FALSE | TRUE |
| FALSE | FALSE | FALSE | FALSE |
| FALSE | UNKNOWN | FALSE | UNKNOWN |
| UNKNOWN | TRUE | UNKNOWN | TRUE |
| UNKNOWN | FALSE | FALSE | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
NOT TRUE 为 FALSE,NOT FALSE 为 TRUE,NOT UNKNOWN 仍为 UNKNOWN:无法判定的条件,取反后也无法判定。若条件 p 为 UNKNOWN,p OR NOT p 仍是 UNKNOWN,而不是二值逻辑中始终成立的条件。
表达式算出结果后,还要看它用在哪里。WHERE 筛选行,HAVING 筛选聚合后的组,二者只保留 TRUE;ON 判断连接是否匹配,只有 TRUE 才匹配,外连接还会保留未匹配行并在缺失的一侧补 NULL。CHECK 检查写入值是否满足约束,却允许 TRUE 或 UNKNOWN,只拒绝 FALSE。因此,WHERE 排除了未知条件,不代表未知就等于假;同一个未知条件放进 CHECK,可能允许写入。
2. 初始化:按问题认识五张小表
结束其他实验事务后,在与业务环境分开的隔离库初始化本篇 null_* 表。下面的 DROP 会删除已有同名表,执行前确认它们仅属于本篇实验,不用于业务表。
| 表 | 用于回答的问题 | 阅读时机 |
|---|---|---|
| null_candidate、null_blacklist | 已知或未知客户是否被排除 | 首先读第 3 节 |
| null_payment | 没有匹配记录与字段缺失如何区分 | 学习外连接时读第 4 节 |
| null_score | 聚合分母、分组与 CHECK 怎样处理 NULL | 第 5—6 节 |
| null_profile | 唯一键是否约束缺失邮箱 | 第 6 节 |
每个小例子只聚焦一个问题。初始化中全部表只含几行数据;第一次可以先只运行候选与黑名单两张表的 CREATE 和 INSERT,再执行第 3 节,其他表按需补齐。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS null_payment;
DROP TABLE IF EXISTS null_blacklist;
DROP TABLE IF EXISTS null_candidate;
DROP TABLE IF EXISTS null_score;
DROP TABLE IF EXISTS null_profile;
CREATE TABLE null_candidate (id BIGINT PRIMARY KEY,customer_id BIGINT NULL) ENGINE=InnoDB;
CREATE TABLE null_blacklist (id BIGINT PRIMARY KEY,customer_id BIGINT NULL) ENGINE=InnoDB;
CREATE TABLE null_payment (
id BIGINT PRIMARY KEY,candidate_id BIGINT NOT NULL,amount DECIMAL(10,2) NULL
) ENGINE=InnoDB;
CREATE TABLE null_score (
id BIGINT PRIMARY KEY,score DECIMAL(5,2) NULL,
CONSTRAINT chk_null_score CHECK (score>=0 AND score<=100)
) ENGINE=InnoDB;
CREATE TABLE null_profile (
id BIGINT PRIMARY KEY,tenant_id BIGINT NOT NULL,email VARCHAR(100) NULL,
UNIQUE KEY uk_null_profile (tenant_id,email)
) ENGINE=InnoDB;
INSERT INTO null_candidate VALUES (1,10),(2,20),(3,30),(4,NULL);
INSERT INTO null_blacklist VALUES (1,10),(2,NULL);
INSERT INTO null_payment VALUES (1,1,90),(2,2,NULL);
INSERT INTO null_score VALUES (1,90),(2,NULL),(3,0),(4,NULL);
INSERT INTO null_profile VALUES (1,1,NULL),(2,1,NULL),(3,1,'a@example.test');3. NOT IN 与 NOT EXISTS:不是无条件等价替代
IN 判断值是否在集合中,NOT IN 对这个判断取反。子查询是嵌在另一条 SQL 中的查询,这里用它取得黑名单编号。先不用子查询,在纸上比较已知客户 20 与列表 (10, NULL):20 = 10 是 FALSE,20 = NULL 是 UNKNOWN。两次比较通过 OR 组合后仍未知,取反后也仍未知,因此 WHERE 不保留该行。
EXISTS 只判断子查询是否返回行,NOT EXISTS 判断它是否一行都不返回,结果只有真或假。下面用黑名单编号与当前候选编号比较,这种引用外层行的子查询称为关联子查询。普通等号遇到 NULL 得到 UNKNOWN,子查询的 WHERE 不保留该行,于是不会形成匹配。
sql
SELECT id FROM null_candidate
WHERE customer_id NOT IN (SELECT customer_id FROM null_blacklist)
ORDER BY id;
SELECT candidate.id FROM null_candidate AS candidate
WHERE NOT EXISTS (
SELECT 1 FROM null_blacklist AS blacklist
WHERE blacklist.customer_id=candidate.customer_id
) ORDER BY candidate.id;
SELECT id FROM null_candidate
WHERE customer_id NOT IN (
SELECT customer_id FROM null_blacklist WHERE customer_id IS NOT NULL
) ORDER BY id;预期分别:空结果;2,3,4;2,3。对于 customer_id=20,IN 的比较包含 FALSE 和 UNKNOWN,NOT 后仍 UNKNOWN。对 NULL 外侧值,普通等号不匹配任何右行,所以 NOT EXISTS 保留 ID 4;过滤子查询 NULL 后的 NOT IN 仍不保留它。
这里“外侧 NULL”指候选表的缺失编号,“内侧 NULL”指黑名单子查询返回的缺失编号。过滤内侧 NULL,不会同时解决外侧 NULL 的业务含义。
业务如果要求“已知身份且不在黑名单”,应在 NOT EXISTS 外层增加 candidate.customer_id IS NOT NULL。对于当前非空黑名单,这样也返回 2、3;黑名单可能为空时,NOT IN 方案也需要显式限制外侧非空,才能持续表达“已知身份”的要求。若把“未知也与未知匹配”当作业务规则,关联条件需换成 <=>,NOT EXISTS 此时只返回 2、3;不能擅自把未知身份视为同一个人。
sql
SELECT id FROM null_candidate
WHERE customer_id NOT IN (
SELECT customer_id FROM null_blacklist WHERE id<0
) ORDER BY id;
SELECT candidate.id FROM null_candidate AS candidate
WHERE NOT EXISTS (
SELECT 1 FROM null_blacklist AS blacklist
WHERE blacklist.id<0 AND blacklist.customer_id=candidate.customer_id
) ORDER BY candidate.id;两条空子查询都预期返回 1,2,3,4,包括外侧 NULL。空集合没有导致 IN 成真的比较,NOT IN 为真;不能把“NULL NOT IN 非空集合”的结论推广到空子查询。字面量 IN () 不是 MySQL 合法空集合写法。
4. 外连接:缺记录与字段缺失
LEFT JOIN 保留左表的每一行:右表有匹配时返回匹配记录,没有匹配时,右侧结果列补为 NULL。因此,结果中的 NULL 可能来自右表真实字段,也可能来自连接补行。判断“没有支付记录”,应检查右表中匹配成功时一定非空的列,例如主键 payment.id。
sql
SELECT candidate.id,payment.id AS payment_id,payment.amount
FROM null_candidate AS candidate
LEFT JOIN null_payment AS payment ON payment.candidate_id=candidate.id
WHERE payment.amount IS NULL
ORDER BY candidate.id;
SELECT candidate.id
FROM null_candidate AS candidate
LEFT JOIN null_payment AS payment ON payment.candidate_id=candidate.id
WHERE payment.id IS NULL
ORDER BY candidate.id;第一条查询的预期候选 ID 为 2、3、4,第二条为 3、4。候选 2 有支付记录,但 amount 为 NULL;候选 3、4 没有匹配记录,payment.id 和 payment.amount 都由外连接补为 NULL。因此,金额为空不能直接证明没有支付记录。若一条候选匹配多条支付记录,连接结果还会出现多行,完整机制见 05;ON/WHERE 移动见 06。
5. 聚合、分组与 DISTINCT 使用不同规则
聚合把多行数据汇总成计数、总和或平均数。COUNT(*) 数所有行,COUNT(score) 只数非 NULL 成绩,SUM(score) 和 AVG(score) 也只使用非 NULL 成绩。因此,平均数的分母是已知成绩数量,不是总行数。DISTINCT 表示去重;COUNT(DISTINCT score) 统计不同的非 NULL 成绩数量。
sql
SELECT COUNT(*) AS all_rows,COUNT(score) AS known_rows,
COUNT(DISTINCT score) AS distinct_known_scores,
SUM(score) AS total,AVG(score) AS average
FROM null_score;
SELECT COUNT(*) AS all_rows,COUNT(score) AS known_rows,SUM(score) AS total
FROM null_score WHERE id IN (2,4);
SELECT COUNT(*) AS all_rows,COUNT(score) AS known_rows,SUM(score) AS total
FROM null_score WHERE id<0;
SELECT score,COUNT(*) AS rows_count
FROM null_score GROUP BY score ORDER BY score IS NULL,score;
SELECT DISTINCT score FROM null_score ORDER BY score;预期:全量 4,2,2,90,45;全 NULL 2,0,NULL;空输入 0,0,NULL。分组为 0→1、90→1、NULL→2;DISTINCT 输出一个 NULL、0、90。分组/去重把 NULL 归为同组,不意味着 NULL=NULL 为真。COUNT DISTINCT 单表达式仍排除 NULL。
COALESCE 返回参数中第一个非 NULL 值,所以 COALESCE(score,0) 会把缺失成绩替换成零。已知成绩为 90 和 0,AVG(score) 的预期值是 (90+0)/2=45;替换后四行都有值,AVG(COALESCE(score,0)) 的预期值变成 90/4=22.5。这改变了平均数的定义,只有业务确实要求“未知按零分”时才适用。
无 GROUP BY 的聚合即使输入为空,也返回一行汇总结果;有 GROUP BY 时,空输入没有可形成的组,因此返回零行。07继续讨论人数、分母和窗口。
6. CHECK 与唯一键:约束不是普通 WHERE
CHECK 检查条件是否为假,并不要求字段一定有值。初始化中的两个 NULL 成绩使 score>=0 AND score<=100 得到 UNKNOWN,因此可以写入;成绩 -1 则使条件为 FALSE。唯一键用于限制重复键值,本例检查同一租户下的邮箱是否重复。下面两个用例分别执行,预期各自失败且原表不变:
sql
INSERT INTO null_score VALUES (5,-1);
INSERT INTO null_profile VALUES (4,1,'a@example.test');若成绩必须存在,需要 NOT NULL 加上 CHECK。MySQL 普通唯一索引允许可空列的多条 NULL;本例 (tenant_id=1,email=NULL) 可以重复,但相同非 NULL (1,'a@example.test') 不行。不能用“唯一索引规定 NULL 彼此不相等”来统一解释普通比较、GROUP BY 和约束三套行为。
若同一租户最多只能有一条缺失邮箱,应先明确这项业务规则,再设计用于唯一约束的统一键值。用特殊占位值(哨兵值)替代 NULL,或用计算表达式生成索引键,都需要保证缺失状态不会与真实邮箱映射到同一个键。其他数据库的复合唯一键规则可能不同,迁移时应按目标版本核验。
7. 排序、空字符串与 Java 映射
sql
SELECT id,customer_id FROM null_candidate
ORDER BY customer_id IS NULL,customer_id,id;
SELECT '' IS NULL AS empty_is_null,''='' AS empty_equal;预期排序 ID 为 1、2、3、4(NULL 最后),空串判断的预期输出为 0,1。排序首先比较 customer_id IS NULL:非 NULL 为 0,NULL 为 1,升序使缺失编号排在最后;再按编号排序,最后用 id 确定相同编号行的次序。显式表达这个意图,可以避免依赖不同数据库的默认 NULL 排序位置。
迁移到 Oracle Database 时,还要注意 SQL 中零长度字符值作为 NULL 处理的差异。这项字符值规则不能直接扩展到所有类型,例如 LOB(大对象),也不能代替对客户端参数绑定行为的检查。迁移测试应覆盖目标 Oracle 版本的字符列、空 LOB、唯一键和驱动绑定方式。
JDBC 是 Java 访问数据库的标准接口,ResultSet 表示查询结果。它的 getLong / getInt 遇 SQL NULL 返回 0,仅凭返回值无法区分“已知零”和“缺失”。读取后必须紧接着调用 wasNull(),因为它检查的是上一次读取的列;读取另一列后再检查,就会判断错对象。下例用 getLong 对应本篇 BIGINT 列,是 JDK 17 方法片段,resultSet 由调用方管理:
java
static Long readNullableCustomer(java.sql.ResultSet resultSet)
throws java.sql.SQLException {
long value = resultSet.getLong("customer_id");
return resultSet.wasNull() ? null : Long.valueOf(value);
}Long 引用能表示 null,long 基本类型不能。还需确认数据库数值是否落在 Java long 的范围内,例如 BIGINT UNSIGNED 的全部范围不能直接映射为正 long。返回对象和接口也应保留业务需要的缺失状态。使用 ORM(对象关系映射框架,把数据库行映射为程序对象)时,要检查框架、配置和类型处理器如何处理 NULL,不能假定所有框架都会转成零。
8. 排障验收与清理
- 查列是否可空、是否有外连接补行,别只看真实数据中“暂时没有 NULL”。
- 把条件表达式本身 SELECT 出来,分辨 FALSE 与 UNKNOWN。
- 对 NOT IN 覆盖外侧 NULL、内侧 NULL、非空无匹配和空子查询。
- 分开统计缺记录、未知字段、零值;检查 CHECK 与 NOT NULL 是否共同表达约束。
- 比较整个结果集,不用 COALESCE 掩盖异常。清理时结束所有事务,在隔离库删除本篇五个 null_* 表。
自然后续:05 JOIN → 06 过滤 → 07 聚合窗口。
9. 名词解释
| 名词 | 含义 |
|---|---|
| NULL / UNKNOWN | 缺失或未知的值 / 条件无法判定真假的逻辑结果;查询通常显示为 NULL |
| IS NULL / NULL 安全等号 | 检查是否缺失 / MySQL <=> 明确比较两侧,包括两侧均 NULL 的情况 |
| NOT IN / NOT EXISTS | 值不在集合中 / 不存在满足条件的行,遇到可空值时语义不同 |
| 外连接补行 | 左侧没有匹配时仍保留左行,右侧结果列用 NULL 表示缺失 |
| COALESCE | 返回第一个非 NULL 参数;给未知值补默认值会改变业务口径 |
| COUNT / AVG | COUNT(*) 数行;COUNT(列) 数非 NULL 值;AVG(列) 对非 NULL 值求平均,分母要明确 |
| wasNull | JDBC 判断上一次读取列值是否为 SQL NULL 的方法,不是检查任意一列 |
参考资料
- MySQL 8.4 Reference Manual:§14.4.2 Comparison Functions and Operators、§15.2.15.3 Subqueries with ANY, IN, or SOME、§15.2.15.4 Subqueries with ALL、§15.1.20.6 CHECK Constraints、§15.1.15 CREATE INDEX Statement。
- JDK 17 java.sql.ResultSet:getInt 与 wasNull 契约;CHECK 接受 UNKNOWN 与唯一索引允许多个 NULL 的具体边界见正文。