数据库原理与选型 / 系统课时
COALESCE、NULLIF 与空值替代
COALESCE、NULLIF 与空值替代
本课解决查询结果出现空值、空字符串和除零风险的问题。完成后,你可以为字段提供默认值,将特定内容转换为 NULL,并用简洁的 SQL 表达式替代重复的条件判断。
学习目标
- 使用
COALESCE返回第一个非NULL值。 - 使用
NULLIF将特定值转换为NULL。 - 组合两个函数处理空字符串和安全除法,并根据业务语义选择默认值。
使用 COALESCE 提供默认值
COALESCE(a, b, c) 从左到右检查参数,返回第一个不是 NULL 的值。如果所有参数都是 NULL,结果也是 NULL。SQLite 要求 COALESCE 至少接收两个参数。
例如,用户可以设置昵称,但部分用户只填写了邮箱。查询时希望优先显示昵称,没有昵称就显示邮箱,两者都缺失时显示“未设置”。
WITH users(nickname, email) AS (
VALUES
(NULL, 'a@example.com'),
('', 'b@example.com')
)
SELECT
nickname,
email,
-- 按优先级选择展示名称,空字符串不会触发回退
coalesce(nickname, email, '未设置') AS display_name
FROM users
ORDER BY email;预期输出(以 NULL 表示数据库空值,空白单元格表示空字符串):
nickname | email | display_name
NULL | a@example.com | a@example.com
| b@example.com |第一行的昵称是 NULL,因此返回邮箱。第二行的昵称是空字符串 '',虽然没有可见字符,但它不是 NULL,因此函数直接返回空字符串,不再检查邮箱。
这说明 COALESCE 只判断是否为 NULL,不判断字符串是否为空,也不会自动删除空格。如果业务规则认为空字符串也代表未填写,需要先使用 NULLIF 进行转换。
将特定值转换为空值
NULLIF(a, b) 在两个值相等时返回 NULL,否则返回第一个参数 a。它适合把某个具有特殊含义的值统一表示为数据库空值。
例如,用户昵称可能包含普通空格。可以先通过 trim() 删除两端空格,再将结果中的空字符串转换为 NULL,最后提供展示默认值。
SELECT
-- 空格裁剪后得到空字符串,再由 NULLIF 转为 NULL
NULLIF(trim(' '), '') AS normalized,
coalesce(NULLIF(trim(' '), ''), '匿名') AS display_name;预期输出:
normalized | display_name
NULL | 匿名执行过程可以分为三步:
trim(' ')删除首尾的普通空格,得到''。NULLIF('', '')判断两个值相等,返回NULL。COALESCE(NULL, '匿名')返回第一个非空值,即“匿名”。
这种写法常用于处理表单提交的空字符串,使后续默认值逻辑保持统一。直接使用 NULLIF(nickname, '') 只能识别完全为空的字符串,不能识别由普通空格组成的昵称。
需要注意,默认的 trim() 主要去除字符串两端的普通空格,不会自动清除所有种类的空白字符。此外,这些表达式只改变当前查询的计算结果,不会修改数据库中原本存储的值。
避免除零并提供回退值
计算成功率时,分母可能是 0。例如当天没有任何请求,total_count 和 success_count 都为 0,此时成功率没有可计算的数值。
将分母 0 转成 NULL,可以明确表达“当前比例无法计算”;再使用 COALESCE,可以根据业务规则将结果回退为 0。
WITH metrics(total_count, success_count) AS (
VALUES
(0, 0),
(10, 8)
)
SELECT
total_count,
success_count,
-- 分母为 0 时转换为 NULL,保留无法计算的语义
success_count * 100.0 / NULLIF(total_count, 0) AS rate,
-- 仅在需要数值默认值时回退为 0
coalesce(
success_count * 100.0 / NULLIF(total_count, 0),
0
) AS rate_with_default
FROM metrics
ORDER BY total_count;预期输出:
total_count | success_count | rate | rate_with_default
0 | 0 | NULL | 0
10 | 8 | 80.0 | 80.0当总数为 10、成功数为 8 时,计算结果为 8 * 100.0 / 10 = 80.0。这里使用 100.0,让 SQLite 进行浮点数计算,避免整数除法导致小数部分丢失。
当总数为 0 时,NULLIF(total_count, 0) 返回 NULL,后续除法结果也为 NULL。SQLite 的除零结果本身就是 NULL,但显式使用 NULLIF 更容易表达设计意图,也有利于将 SQL 迁移到其他数据库。
rate 和 rate_with_default 代表不同的业务语义:前者保留“无法计算”,后者把无法计算的情况显示为 0。如果用于统计报表,建议保留 NULL,避免把“没有数据”和“成功率确实为 0%”混为一谈;只有在展示或业务规则明确要求时才提供默认值。
选择正确的默认值
默认值必须符合业务语义。未知折扣不一定等于 0,未知姓名也不一定应该显示空字符串。尤其需要区分“没有设置”和“已经确定为零”这两种情况。
例如,商品价格分为原价和折扣价。如果业务明确约定 discount_price 为 NULL 代表没有折扣,就可以使用原价作为最终价格。
WITH products(list_price, discount_price) AS (
VALUES
(99.0, NULL),
(99.0, 79.0)
)
SELECT
list_price,
discount_price,
-- 未设置折扣价时使用原价,而不是统一回退为 0
coalesce(discount_price, list_price) AS final_price
FROM products
ORDER BY discount_price IS NOT NULL, discount_price;预期输出:
list_price | discount_price | final_price
99.0 | NULL | 99.0
99.0 | 79.0 | 79.0这里的默认逻辑是“没有折扣价时使用原价”,比统一填 0 更符合真实含义。但是,如果 NULL 表示“折扣价尚未录入或确认”,直接使用原价可能掩盖数据缺失问题,此时应保留 NULL 或单独显示待确认状态。
可以在 SQLite 客户端中分别执行上述四条独立查询,检查以下结果:空字符串不会触发 COALESCE 回退;只包含普通空格的文本经过 trim 和 NULLIF 后能够显示“匿名”;分母为 0 时原始比例为 NULL;没有折扣价时最终价格按约定回退到原价。实际终端的表格格式和 NULL 显示方式可能因客户端设置不同而变化。
使用这两个函数时,应先确定哪些值代表缺失,再决定是否提供默认值。NULLIF 负责按规则将特定值转换为 NULL,COALESCE 负责按优先级选择可用值;组合使用即可覆盖常见的空值归一化、字段回退和安全比例计算。