【SQL】MySQL中空值处理COALESCE函数

博主介绍：✌全网粉丝24W+，ZEEKLOG博客专家、Java领域优质创作者，掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域✌

技术范围：SpringBoot、SpringCloud、Vue、SSM、HTML、Nodejs、Python、MySQL、PostgreSQL、大数据、物联网、机器学习等设计与开发。

感兴趣的可以先关注收藏起来，在工作中、生活上等遇到相关问题都可以给我留言咨询，希望帮助更多的人。

MySQL中空值处理COALESCE函数

• 一、核心概念
• 二、主要应用场景
  • 2.1 场景 1：替换 SELECT 查询中的 NULL 值
  • 2.2 场景 2：多字段优先级选择
  • 2.3 场景 3：在 WHERE 子句中使用
  • 2.4 场景 4：与聚合函数结合使用
• 三、与其他类似函数比较
  • 3.1 与 `ISNULL` 和 `NVL` 的比较
  • 3.2 COALESCE 与 IFNULL 的区别
• 四、使用技巧
  • 4.1 技巧 1：设置默认值链
  • 4.2 技巧 2：在 UPDATE 语句中使用
  • 4.3 技巧 3：在 ORDER BY 中使用
• 五、注意事项
• 六、总结

COALESCE 是一个在多种编程语言和数据库系统中常见的函数或操作符，主要用于处理空值（NULL）。它的核心功能是 返回其参数列表中的第一个非空值。

一、核心概念

COALESCE(value1, value2, value3, ..., valueN) 

• 功能：按从左到右的顺序检查每个参数。
• 返回值：返回第一个不为 NULL 的参数值。
• 如果所有参数都为 NULL：则返回 NULL。

二、主要应用场景

2.1 场景 1：替换 SELECT 查询中的 NULL 值

-- 示例数据CREATETABLE employees ( id INT, name VARCHAR(50), salary DECIMAL(10,2), bonus DECIMAL(10,2));INSERTINTO employees VALUES(1,'张三',5000.00,NULL),(2,'李四',6000.00,1000.00),(3,'王五',NULL,500.00);-- 使用 COALESCE 处理 NULLSELECT name,COALESCE(salary,0)as salary,-- 如果salary为NULL，显示0COALESCE(bonus,0)as bonus,-- 如果bonus为NULL，显示0COALESCE(salary,0)+COALESCE(bonus,0)as total_income FROM employees;

结果：

name salary bonus total_income 张三 5000.00 0.00 5000.00 李四 6000.00 1000.00 7000.00 王五 0.00 500.00 500.00 

2.2 场景 2：多字段优先级选择

-- 用户联系方式表CREATETABLE user_contacts ( user_id INT, phone VARCHAR(20), mobile VARCHAR(20), email VARCHAR(50));INSERTINTO user_contacts VALUES(1,NULL,'13800138000','[email protected]'),(2,'010-12345678',NULL,NULL),(3,NULL,NULL,'[email protected]');-- 优先选择手机号，其次电话，最后邮箱SELECT user_id,COALESCE(mobile, phone, email,'无联系方式')as primary_contact FROM user_contacts;

结果：

user_id primary_contact 1 13800138000 2 010-12345678 3 [email protected] 

2.3 场景 3：在 WHERE 子句中使用

-- 查找没有工资记录但可能有奖金的员工SELECT name, bonus FROM employees WHERECOALESCE(salary,0)=0AND bonus ISNOTNULL;

2.4 场景 4：与聚合函数结合使用

-- 计算平均工资（NULL值视为0）SELECTAVG(COALESCE(salary,0))as avg_salary FROM employees;

三、与其他类似函数比较

3.1 与 ISNULL 和 NVL 的比较

• COALESCE：是 SQL 标准函数，可以接受两个或更多参数，更具灵活性。
• ISNULL (SQL Server 特有)：通常只接受两个参数，功能与 COALESCE 类似，但非标准。
• NVL (Oracle 特有)：也是处理两个参数的空值替换函数。

总结：COALESCE 是一个强大的工具，用于优雅地处理空值，提供默认值或从多个备选值中选择一个有效值，广泛应用于数据库操作和数据处理中。

3.2 COALESCE 与 IFNULL 的区别

四、使用技巧

4.1 技巧 1：设置默认值链

-- 多层备选方案SELECT name,COALESCE( mobile, phone, CONCAT('邮箱: ', email),'暂无联系方式')as contact_info FROM user_contacts;

4.2 技巧 2：在 UPDATE 语句中使用

-- 将NULL奖金更新为0UPDATE employees SET bonus =COALESCE(bonus,0)WHERE bonus ISNULL;

4.3 技巧 3：在 ORDER BY 中使用

-- 优先按工资排序，工资为NULL的排后面SELECT name, salary FROM employees ORDERBYCOALESCE(salary,0)DESC;

五、注意事项

1. 性能考虑：COALESCE 会按顺序评估每个参数，直到找到第一个非 NULL 值
2. 数据类型：所有参数应该是相同或兼容的数据类型
3. 与空字符串区别：NULL 表示缺失值，空字符串 '' 是有效值

六、总结

• COALESCE 是处理 NULL 值的强大工具
• 支持多个参数，比 IFNULL 更灵活
• 可用于 SELECT、WHERE、ORDER BY、UPDATE 等各种场景
• 能够有效避免因 NULL 值导致的计算错误和显示问题

掌握 COALESCE 函数能让你的 SQL 查询更加健壮和易读！

好了，今天分享到这里。希望你喜欢这次的探索之旅！不要忘记 “点赞” 和 “关注” 哦，我们下次见！🎈

本文完结！

祝各位大佬和小伙伴身体健康，万事如意，发财暴富，扫下方二维码与我一起交流！！！