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

最新推荐文章于 2026-06-23 18:00:29 发布

转载最新推荐文章于 2026-06-23 18:00:29 发布 · 36 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：https://blog.csdn.net/weixin_44299027/article/details/151722377

GEO检测

标签

#sql #mysql #数据库

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)
运行项目并下载源码
1
功能：按从左到右的顺序检查每个参数。
返回值：返回第一个不为 NULL 的参数值。
如果所有参数都为 NULL：则返回 NULL。
二、主要应用场景
2.1 场景 1：替换 SELECT 查询中的 NULL 值
-- 示例数据
CREATE TABLE employees (
id INT,
name VARCHAR(50),
salary DECIMAL(10,2),
bonus DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, '张三', 5000.00, NULL),
(2, '李四', 6000.00, 1000.00),
(3, '王五', NULL, 500.00);

-- 使用 COALESCE 处理 NULL
SELECT
name,
COALESCE(salary, 0) as salary, -- 如果salary为NULL，显示0
COALESCE(bonus, 0) as bonus, -- 如果bonus为NULL，显示0
COALESCE(salary, 0) + COALESCE(bonus, 0) as total_income
FROM employees;
运行项目并下载源码
sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
结果：

name salary bonus total_income
张三 5000.00 0.00 5000.00
李四 6000.00 1000.00 7000.00
王五 0.00 500.00 500.00
运行项目并下载源码
1
2
3
4
2.2 场景 2：多字段优先级选择
-- 用户联系方式表
CREATE TABLE user_contacts (
user_id INT,
phone VARCHAR(20),
mobile VARCHAR(20),
email VARCHAR(50)
);

INSERT INTO user_contacts VALUES
(1, NULL, '13800138000', 'zhang@email.com'),
(2, '010-12345678', NULL, NULL),
(3, NULL, NULL, 'wang@email.com');

-- 优先选择手机号，其次电话，最后邮箱
SELECT
user_id,
COALESCE(mobile, phone, email, '无联系方式') as primary_contact
FROM user_contacts;
运行项目并下载源码
sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
结果：

user_id primary_contact
1 13800138000
2 010-12345678
3 wang@email.com
运行项目并下载源码
1
2
3
4
2.3 场景 3：在 WHERE 子句中使用
-- 查找没有工资记录但可能有奖金的员工
SELECT name, bonus
FROM employees
WHERE COALESCE(salary, 0) = 0 AND bonus IS NOT NULL;
运行项目并下载源码
sql
1
2
3
4
2.4 场景 4：与聚合函数结合使用
-- 计算平均工资（NULL值视为0）
SELECT AVG(COALESCE(salary, 0)) as avg_salary
FROM employees;
运行项目并下载源码
sql
1
2
3
三、与其他类似函数比较
3.1 与 ISNULL 和 NVL 的比较
COALESCE：是 SQL 标准函数，可以接受两个或更多参数，更具灵活性。
ISNULL (SQL Server 特有)：通常只接受两个参数，功能与 COALESCE 类似，但非标准。
NVL (Oracle 特有)：也是处理两个参数的空值替换函数。
总结：COALESCE 是一个强大的工具，用于优雅地处理空值，提供默认值或从多个备选值中选择一个有效值，广泛应用于数据库操作和数据处理中。

3.2 COALESCE 与 IFNULL 的区别
特性   COALESCE   IFNULL
参数数量   多个参数   只能有两个参数
灵活性   更高，可处理多个备选值   较低
标准兼容性   SQL标准函数   MySQL特有函数
四、使用技巧
4.1 技巧 1：设置默认值链
-- 多层备选方案
SELECT
name,
COALESCE(
mobile,
phone,
CONCAT('邮箱: ', email),
'暂无联系方式'
) as contact_info
FROM user_contacts;
运行项目并下载源码
sql

1
2
3
4
5
6
7
8
9
10
4.2 技巧 2：在 UPDATE 语句中使用
-- 将NULL奖金更新为0
UPDATE employees
SET bonus = COALESCE(bonus, 0)
WHERE bonus IS NULL;
运行项目并下载源码
sql
1
2
3
4
4.3 技巧 3：在 ORDER BY 中使用
-- 优先按工资排序，工资为NULL的排后面
SELECT name, salary
FROM employees
ORDER BY COALESCE(salary, 0) DESC;
运行项目并下载源码
sql
1
2
3
4
五、注意事项
性能考虑：COALESCE 会按顺序评估每个参数，直到找到第一个非 NULL 值
数据类型：所有参数应该是相同或兼容的数据类型
与空字符串区别：NULL 表示缺失值，空字符串 '' 是有效值
六、总结
COALESCE 是处理 NULL 值的强大工具
支持多个参数，比 IFNULL 更灵活
可用于 SELECT、WHERE、ORDER BY、UPDATE 等各种场景
能够有效避免因 NULL 值导致的计算错误和显示问题
掌握 COALESCE 函数能让你的 SQL 查询更加健壮和易读！
————————————————
原文链接：https://blog.csdn.net/weixin_44299027/article/details/151722377