MariaDB 教程 / 用 IS NULL 比较缺失值,用 COALESCE 选择替代值
NULL 表示缺失或未知信息,判断时使用 IS NULL、IS NOT NULL,而不是普通的等号比较。COALESCE 为结果选择第一个非 NULL 表达式,不会改写源表,也不会跳过空字符串。筛选条件、显示替代值和修正存储数据是不同操作,应先确定需要保留哪些信息。
保留 NULL、空文本与零的区别
未收集的备注可以是 NULL,提交了空白内容的备注可以是空字符串,已知无库存则可以是数字零。如果不加判断地把所有看似空白的值视为相同,就可能抹去“未知”和“已知为空”的差异。NULL、默认值与输入约束教程讨论的是写入时允许什么,本篇关注已经存储的值如何查询。
| 值 | SQL 写法 | 含义 |
|---|---|---|
| NULL | NULL | 缺失或未知 |
| 空文本 | '' | 长度为零的字符串 |
| 零 | 0 | 已知数值为零 |
| 字母 NULL | 'NULL' | 由四个字符组成的字符串 |
本例使用普通字符串语义,不启用 EMPTY_STRING_IS_NULL 等兼容模式。将行为用于其他安装之前,先检查 @@SESSION.sql_mode。参考 NULL 值说明与 COALESCE 官方参考,不要只根据客户端显示的空白判断底层值。
准备同时包含缺失与空备注的数据
用 course_user 连接学习服务器并选择 shopcourse,按照命令行和 UTF-8 SQL 文件方法保存完整代码,使用 SOURCE 和实际完整本机路径执行。SQL 在 MariaDB 客户端里运行,不是在操作系统终端运行。
先执行 SELECT VERSION(), DATABASE() 和 SHOW TABLES。若 products_16_lab 已存在,查看它的定义和记录,确认是否为可丢弃练习对象,不替换应用表。使用独立表初始化以下六行:
USE shopcourse;
CREATE TABLE products_16_lab (
product_id INT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
note VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (product_id),
CONSTRAINT ck_p16_price CHECK (price >= 0),
CONSTRAINT ck_p16_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_16_lab
(product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
(2, '볼펜', '문구', 1500.00, 0, ''),
(3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
(4, '스탠드', '생활', 25000.00, 5, NULL),
(5, '파일', '문구', 1500.00, 50, ''),
(6, '케이블', '전자', 8000.00, 0, '호환 확인');
原韩文字符串保持不变,以便验证 UTF-8。编号 1 至 6 分别代表笔记本、圆珠笔、马克杯、台灯、文件夹和线缆;문구、생활、전자 表示文具、家居、电子。两条有内容备注 선물 포장 和 호환 확인 分别表示礼品包装、检查兼容性。用编号对照状态:
SELECT product_id, note, note IS NULL AS is_missing,
note = '' AS is_empty
FROM products_16_lab ORDER BY product_id;
| 商品 ID | 备注含义 | is_missing | is_empty |
|---|---|---|---|
| 1 | NULL | 1 | NULL |
| 2 | 空字符串 | 0 | 1 |
| 3 | 礼品包装 | 0 | 0 |
| 4 | NULL | 1 | NULL |
| 5 | 空字符串 | 0 | 1 |
| 6 | 检查兼容性 | 0 | 0 |
NULL 备注与空字符串比较得到 NULL,不是零。推理 WHERE 与否定条件时,应区分真、假、未知三个结果;把未知简单当成普通假值,会在“取反”时产生错误结论。IS NULL 的结果则明确表示是否缺失。
明确选择缺失值与非缺失值
SELECT product_id FROM products_16_lab
WHERE note = NULL ORDER BY product_id;
SELECT product_id FROM products_16_lab
WHERE note IS NULL ORDER BY product_id;
SELECT product_id FROM products_16_lab
WHERE note IS NOT NULL ORDER BY product_id;
等号比较 NULL 的查询不返回任何行;IS NULL 返回 1、4;IS NOT NULL 返回 2、3、5、6,其中包括空字符串。非 NULL 不等于有可见文字,只有明确知道业务条件,才能进一步决定是否排除空文本。
再比较下面两个“不等于礼品包装”的条件:
SELECT product_id FROM products_16_lab
WHERE note <> '선물 포장' ORDER BY product_id;
SELECT product_id FROM products_16_lab
WHERE note IS NULL OR note <> '선물 포장' ORDER BY product_id;
第一条返回 2、5、6,NULL 备注的 1、4 因比较结果未知而被排除。第二条明确用 IS NULL OR 加入缺失备注,返回 1、2、4、5、6。源表没有改变,差异来自是否把缺失状态纳入结果。
因此,“不等于某值”并不总是“除此值以外的所有行”。先决定缺失值是否属于目标集合,再写条件。WHERE 条件与括号教程说明了逐步添加条件、显示参与筛选列的检查方法。
选择显示替代值而不重写数据
SELECT product_id, COALESCE(note, '미입력') AS display_note
FROM products_16_lab ORDER BY product_id;
SELECT COALESCE(NULL, NULL, '기본 안내') AS fallback_text;
SELECT NULL = NULL AS ordinary_equal,
NULL <=> NULL AS null_safe_equal;
第一条查询为编号 1、4 显示韩文标签 미입력,意思是“未填写”;编号 2、5 仍为空字符串,因为空字符串不是 NULL。第二条依次遇到两个 NULL,返回 기본 안내,即“默认说明”。候选按列表顺序选择第一个非 NULL 值,若全部为 NULL,结果仍是 NULL。
最后一条比较的 ordinary_equal 为 NULL,null_safe_equal 为 1。MariaDB 的 <=> 是 NULL 安全相等运算符,可以将“两边都为 NULL”判断为相等;仅检查某列是否缺失时,IS NULL 更直观。规则见 NULL 安全相等参考。
替代值的类型应符合后续用途,不应把数字库存与文字标签随意混合后继续计算。重新查询原始 note 及 note IS NULL,可以确认 COALESCE 没有执行 UPDATE,也没有把未填写标签保存回表。它解决的是当前结果怎样展示,而不是补全缺失事实。
识别 NOT IN 列表中的 NULL
SELECT product_id FROM products_16_lab
WHERE category NOT IN ('문구', NULL) ORDER BY product_id;
这条排除查询不返回任何行。文具分类匹配被排除的明确值;其他分类涉及 NULL 的比较仍为未知,也不能通过 WHERE。只删除列表里的 NULL,保留 NOT IN ('문구'),才返回家居和电子分类的 3、4、6。观察后恢复原列表,可再次看到空结果。
应用或子查询生成排除列表时,要验证其中是否包含 NULL。NOT EXISTS 可以表达跨表的不存在判断,但不能不看上下文就把所有 NOT IN 文本替换为 NOT EXISTS。被比较列本身是否可为 NULL 也要单独决定。本例 category 非 NULL,因此能集中观察列表内 NULL 的影响。
填补缺失信息之前先确定数据策略
显示更整齐并不意味着数据已经完整。将未知库存显示成零,可能错误表达为已经确认售罄。需要时使用缺失标签或独立状态,DEFAULT 与 NOT NULL 也只承担其实际输入策略。客户端将 NULL 显示成空白时,应增加 IS NULL 结果列来确认,而不是凭外观猜测。
可以重复运行这些 SELECT 并比较整张表的原始值,确认没有行因显示替代或条件排除而被修改。若结果与预期不同,检查连接目标、初始化是否重复、SQL 模式和实际 note 内容,再决定是否更改业务数据。
重做前检查数据库、实际定义及存储行,备份要保留的内容。确认 products_16_lab 只是本文可丢弃的对象后,仅删除它:
DROP TABLE shopcourse.products_16_lab;
DROP TABLE 同时删除结构和记录,普通 ROLLBACK 不能恢复。清理应与比较实验分开,其他商品表与业务数据不属于本次重置范围。之后可重新运行完整初始化,再按六行的预期状态逐项核对。




