MySQL 如何使用COALESCE（）函数将MySQL null转换为0？

在MySQL中，null通常被用来表示一个缺失的、为定义的值。当一个null值参与算术运算时，计算结果通常也是null。但在某些情况下，我们需要将它转换为0，例如做数据统计时。这时，我们可以使用MySQL函数COALESCE()，本文将详细介绍它的用法。

阅读更多：MySQL 教程

COALESCE()函数的语法

COALESCE()函数用于返回参数列表中第一个非null的值。其语法如下：

COALESCE(expr1, expr2, ...)

其中，expr1、expr2等是参数，可以是任何数据类型。COALESCE()函数会依次检查每个表达式的值，返回第一个不是null的表达式的值。如果所有的表达式的值都是null，则返回null。

使用COALESCE()函数将null值转换为0

下面是一个使用COALESCE()函数将null值转换为0的例子。假设我们有以下的“成绩单”表（scores）:

+----+-------+-------+-------+
| ID | Name  | Math  |  English |
+----+-------+-------+---------+
| 1  | Alice | 90    |  null   |
| 2  | Bob   | null | 70      |
| 3  | Cindy | 80    |  85     |
| 4  | Dave  | null |  null   |
+----+-------+-------+---------+

现在我们想对每个学生的数学和英语成绩求和，如果某个成绩为空，将其视为0。可以使用COALESCE()函数来实现：

SELECT Name, COALESCE(Math, 0) + COALESCE(English, 0) AS TotalScore
FROM scores;

输出结果为：

+-------+------------+
| Name  | TotalScore |
+-------+------------+
| Alice | 90         |
| Bob   | 70         |
| Cindy | 165        |
| Dave  | 0          |
+-------+------------+

在上例中，我们使用了COALESCE()函数将Math和English列中的null值转换为0，然后再求和。另外，COALESCE()函数可以传递多个参数，我们可以一次性将多个列中的null值转换为0，这样更加简洁：

SELECT Name, COALESCE(Math, 0, English, 0) AS TotalScore
FROM scores;

COALESCE()函数的应用场景

COALESCE()函数在web开发中有很多应用场景，例如：

防范SQL注入攻击

在web开发中，如果用户输入的数据为空（null）或者是恶意的SQL语句，都可能导致程序出错或者被攻击。使用COALESCE()函数可以避免这种情况。

例如，我们要查询一个学生的成绩，用户需要提供该学生的ID作为参数。在使用参数之前，我们可以使用COALESCE()函数将其转换为0，这样就可以避免参数为空（null）的情况：

SELECT * FROM scores WHERE ID = COALESCE(@ID, 0);

这样，如果用户没有提供ID参数，程序会将其视为0，而不会出现错误或者被攻击。

统计数据

在做数据统计的时候，经常需要将null值转换为0，这时COALESCE()函数非常有用。

例如，以下是统计某个网站所有访问次数的SQL语句：

SELECT DATE_FORMAT(`visit_time`, '%Y%m%d') AS date, COUNT(*) AS count
FROM `visits`
GROUP BY date;

如果有一天没有访问记录，查询结果中会缺少这一天的记录。为了避免这种情况，我们可以使用COALESCE()函数将缺失的日期补充为0：

SELECT DATE_FORMAT(`visit_time`, '%Y%m%d') AS date, COALESCE(COUNT(*), 0) AS count
FROM `visits`
GROUP BY date;

这样，即使某一天没有访问记录，也会显示该天的访问次数为0。

填充数据

在一些情况下，我们需要对缺失的数据进行填充。COALESCE()函数可以帮助我们实现这个功能。

例如，假设我们有以下的 “订单” 表（orders）：

+--------+------------+-------+
| 日期 | 产品名称 | 销售额 |
+--------+------------+-------+
| 202106 | 产品A     | 1000  |
| 202106 | 产品B     | 2000  |
| 202107 | 产品A     | 1500  |
+--------+------------+-------+

如果我们要查询每个产品每个月的销售额，可以使用下面的SQL语句：

SELECT 日期, 产品名称, SUM(销售额) AS 销售总额
FROM orders
GROUP BY 日期, 产品名称;

但该查询结果会缺少某些月份或产品的数据。如果我们想要查询每个月所有产品的销售总额，我们可以使用COALESCE()函数来填充数据：

SELECT a.日期, b.产品名称, COALESCE(SUM(销售额), 0) AS 销售总额
FROM (
  SELECT DISTINCT 日期 FROM orders
) a
CROSS JOIN (
  SELECT DISTINCT 产品名称 FROM orders
) b
LEFT JOIN orders c ON a.日期 = DATE_FORMAT(c.日期, '%Y%m') AND b.产品名称 = c.产品名称
GROUP BY a.日期, b.产品名称;

这里我们使用了 CROSS JOIN 语句生成了一个包含所有日期和产品的表，然后使用 LEFT JOIN 将该表连接到原来的订单表上，并使用 COALESCE() 函数将缺失的数据填充为0。