数据库原理与选型 / 系统课时

COALESCE、NULLIF 与空值替代

COALESCE、NULLIF 与空值替代

本课解决查询结果出现空值、空字符串和除零风险的问题。完成后,你可以为字段提供默认值,将特定内容转换为 NULL,并用简洁的 SQL 表达式替代重复的条件判断。

学习目标

  • 使用 COALESCE 返回第一个非 NULL 值。
  • 使用 NULLIF 将特定值转换为 NULL。
  • 组合两个函数处理空字符串和安全除法,并根据业务语义选择默认值。

使用 COALESCE 提供默认值

COALESCE(a, b, c) 从左到右检查参数,返回第一个不是 NULL 的值。如果所有参数都是 NULL,结果也是 NULL。SQLite 要求 COALESCE 至少接收两个参数。

例如,用户可以设置昵称,但部分用户只填写了邮箱。查询时希望优先显示昵称,没有昵称就显示邮箱,两者都缺失时显示“未设置”。

sql
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 表示数据库空值,空白单元格表示空字符串):

text
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,最后提供展示默认值。

sql
SELECT
  -- 空格裁剪后得到空字符串,再由 NULLIF 转为 NULL
  NULLIF(trim('   '), '') AS normalized,
  coalesce(NULLIF(trim('   '), ''), '匿名') AS display_name;

预期输出:

text
normalized | display_name
NULL       | 匿名

执行过程可以分为三步:

  1. trim(' ') 删除首尾的普通空格,得到 ''。
  2. NULLIF('', '') 判断两个值相等,返回 NULL。
  3. COALESCE(NULL, '匿名') 返回第一个非空值,即“匿名”。

这种写法常用于处理表单提交的空字符串,使后续默认值逻辑保持统一。直接使用 NULLIF(nickname, '') 只能识别完全为空的字符串,不能识别由普通空格组成的昵称。

需要注意,默认的 trim() 主要去除字符串两端的普通空格,不会自动清除所有种类的空白字符。此外,这些表达式只改变当前查询的计算结果,不会修改数据库中原本存储的值。

避免除零并提供回退值

计算成功率时,分母可能是 0。例如当天没有任何请求,total_count 和 success_count 都为 0,此时成功率没有可计算的数值。

将分母 0 转成 NULL,可以明确表达“当前比例无法计算”;再使用 COALESCE,可以根据业务规则将结果回退为 0。

sql
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;

预期输出:

text
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 代表没有折扣,就可以使用原价作为最终价格。

sql
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;

预期输出:

text
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 负责按优先级选择可用值;组合使用即可覆盖常见的空值归一化、字段回退和安全比例计算。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

COALESCE、NULLIF 与空值替代

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通