MySQL COALESCE 函数

介绍

在MySQL中，COALESCE函数是一种非常有用的函数。COALESCE函数用于返回参数中的第一个非NULL值。如果所有的参数都是NULL，那么COALESCE函数将返回NULL。

COALESCE函数可以接受任意数量的参数，并按照参数的顺序进行判断。如果参数列表中的某个参数是非NULL值，COALESCE函数将立即返回该值，不再继续判断后续的参数。

COALESCE函数可以用于各种场景，如返回非NULL值，替代NULL值，以及对多个列进行判断等。下面将详细介绍COALESCE函数的语法、用法和示例。

语法

COALESCE函数的语法如下：

COALESCE(value1, value2, ..., valuen)

其中，value1, value2, …, valuen 是要进行判断的参数。如果所有参数都是NULL，那么COALESCE函数将返回NULL。如果参数列表中的某个参数是非NULL值，COALESCE函数将立即返回该值。

示例

返回非NULL值

假设有一个学生表 students，其中包含学生的信息，包括学生的姓名 name 和年龄 age。

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO students (id, name, age) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 20),
(3, 'Charlie', 25);

现在，我们想要查询学生的姓名和年龄，如果年龄为NULL，返回默认值”Unknown”。

可以使用COALESCE函数来实现此需求：

SELECT name, COALESCE(age, 'Unknown') AS age FROM students;

运行以上查询语句，将会得到以下结果：

+---------+---------+
|  name   |   age   |
+---------+---------+
| Alice   | Unknown |
| Bob     | 20      |
| Charlie | 25      |
+---------+---------+

可以看到，当年龄为NULL时，COALESCE函数返回了”Unknown”作为替代值。

替代NULL值

在某些情况下，我们可能需要将NULL值替换为其他的非NULL值。COALESCE函数可以很方便地实现这个功能。

假设我们有一个购物订单表 orders，其中包含订单的信息，包括订单ID id，订单金额 amount，以及产品名称 product。有些订单的产品名称可能为NULL。

现在，我们想要将订单的产品名称为NULL的记录，替换为”Unknown”。

可以使用COALESCE函数来实现此需求：

SELECT id, COALESCE(product, 'Unknown') AS product, amount FROM orders;

假设订单表中的数据如下：

INSERT INTO orders (id, product, amount) VALUES
(1, 'Product 1', 100),
(2, NULL, 200),
(3, 'Product 3', 300);

运行以上查询语句，将会得到以下结果：

+----+------------+--------+
| id |  product   | amount |
+----+------------+--------+
|  1 | Product 1  |    100 |
|  2 | Unknown    |    200 |
|  3 | Product 3  |    300 |
+----+------------+--------+

可以看到，订单表中产品名称为NULL的记录被替换成了”Unknown”。

对多个列进行判断

COALESCE函数也可以同时对多个列进行判断，并返回第一个非NULL值。

假设在学生表 students 中，我们除了记录学生的姓名 name 和年龄 age 之外，还记录了学生的邮箱 email。

现在，我们想要查询学生的姓名和联系方式，如果学生的邮箱为NULL，返回学生的手机号码 phone。如果学生的手机号码也为NULL，返回默认联系方式”Unknown”。

可以使用COALESCE函数嵌套来实现此需求：

SELECT name, COALESCE(email, COALESCE(phone, 'Unknown')) AS contact FROM students;

假设学生表中的数据如下：

INSERT INTO students (id, name, age, email, phone) VALUES
(1, 'Alice', NULL, 'alice@example.com', NULL),
(2, 'Bob', 20, NULL, '1234567890'),
(3, 'Charlie', 25, 'charlie@example.com', '9876543210'),
(4, 'David', 22, NULL, NULL);

运行以上查询语句，将会得到以下结果：

+---------+-------------------+
|  name   |      contact      |
+---------+-------------------+
| Alice   | alice@example.com |
| Bob     | 1234567890        |
| Charlie | charlie@example.com |
| David   | Unknown           |
+---------+-------------------+

可以看到，COALESCE函数嵌套的方式能够非常灵活地对多个列进行判断，并返回第一个非NULL值。