mysql中的NULL值处理函数与应用

COALESCE是最常用的安全取值函数，按序返回首个非NULL值，支持多参数；ifNULL仅支持两参数，NULLIF用于相等时返回NULL；WHERE中判NULL必须用IS NULL；聚合函数自动忽略NULL，但count(*)统计所有行。

COALESCE 函数：最常用的安全取值方式

COALESCE 是 mysql 中处理 NULL 最实用的函数，它按顺序返回第一个非 NULL 的表达式值。和 IFNULL 不同，它支持多个参数，灵活性更高。

• 当查询字段可能为 NULL，又不想让结果直接显示空值时，用 COALESCE(user_name, '未知用户') 比写一堆 IF 判断简洁得多
• 如果传入的所有参数都是 NULL，COALESCE 返回 NULL，这点必须注意——它不会自动 fallback 到空字符串或 0
• 在 ORDER BY 或 GROUP BY 中混用 NULL 值时，COALESCE(status, 'pending') 可避免排序错乱或分组断裂

SELECT id, COALESCE(phone, email, '暂无联系方式') AS contact FROM users;

IFNULL 与 NULLIF：两个参数的极简场景

IFNULL(a, b) 等价于 COALESCE(a, b)，但只接受两个参数；NULLIF(a, b) 则在 a = b 时返回 NULL，否则返回 a。

• IFNULL 适合补默认值这种“一换一”场景，比如 IFNULL(price, 0)，语义清晰且性能略优（MySQL 对双参做了优化）
• NULLIF 常用于“抹掉重复值”，例如 NULLIF(old_value, new_value) 可在审计日志中把未变更字段标为空，方便后续识别真实修改
• 注意：NULLIF 的两个参数类型要兼容，否则触发隐式转换，可能导致意外结果，比如 NULLIF('123', 123) 在严格模式下会报错

UPDATE products SET discount = NULLIF(discount, 0) WHERE id = 1001;

WHERE 条件中判断 NULL 必须用 IS NULL / IS NOT NULL

= NULL 或 != NULL 在 MySQL 中永远返回 UNKNOWN，不是 TRUE 也不是 FALSE，所以不会匹配任何行。

• 错误写法：WHERE status = NULL → 查不到任何数据
• 正确写法：WHERE status IS NULL 或 WHERE status IS NOT NULL
• 如果想统一处理 NULL 和空字符串，得显式写成：WHERE COALESCE(status, '') = ''，但要注意这会绕过索引（除非加函数索引）
• 在联合索引中，IS NULL 可以走索引（MySQL 8.0+），但 COALESCE(status, '') = '' 几乎一定全表扫描

SELECT * FROM orders WHERE shipped_at IS NULL AND order_status != 'cancelled';

聚合函数自动忽略 NULL，但 COUNT(*) 是例外

所有标准聚合函数（SUM、AVG、MAX、MIN）天然跳过 NULL 值，但 COUNT(expr) 和 COUNT(*) 行为完全不同。

• COUNT(*) 统计所有行（包括含 NULL 的行），而 COUNT(column) 只统计该列非 NULL 的行
• 所以 COUNT(id) 和 COUNT(*) 在主键列上结果一致，但在可空字段（如 email）上差异极大
• 想统计“有邮箱的用户数”，必须用 COUNT(email)；若误写成 COUNT(*)，就变成总用户数了

SELECT    COUNT(*) AS total_users,   COUNT(email) AS users_with_email,   COUNT(IF(email IS NULL, 1, NULL)) AS users_without_email FROM users;

实际业务里，NULL 的语义常被模糊化——是“未填写”？“不适用”？还是“数据缺失”？函数只是工具，真正难的是在建表阶段就明确字段是否允许 NULL，以及默认值策略。一旦表上线，改 NOT NULL 约束或补全历史 NULL，代价远高于选对一个函数。