MySQL AVG() 函数悖论：NULL 值造成的异常

MySQL 是一款十分强大的关系数据库管理系统，提供了丰富的 SQL 函数来方便用户进行数据操作和计算。其中，AVG() 函数用于计算数值类型的平均值。然而，我们在使用 AVG() 函数时需要注意，可能会遇到一些奇怪的异常，尤其是与 NULL 值有关的情况。本文就从这个角度进行探讨和分析。

阅读更多：MySQL 教程

AVG() 函数的语法和用法

AVG() 函数用于计算一列（column）的平均值，其语法如下：

AVG(column_name)

其中 column_name 表示要计算平均值的列的名称。AVG() 函数只适用于数值类型（int，double，float 等）的列，不适用于字符串类型等其他类型的列。

示例1：计算学生成绩的平均值

假设我们有一张学生成绩表 scores，其中包含学号、姓名、数学成绩、英语成绩和计算机成绩等多个列。我们可以使用以下语句来计算数学成绩的平均值：

SELECT AVG(math_score) FROM scores;

这会返回一个实数（real）类型的数，表示该列数学成绩的平均值。

AVG() 函数在存在 NULL 值的情况下的异常

AVG() 函数在计算平均值时，如果列中包含 NULL 值，会出现奇怪的异常结果。这是由于 AVG() 函数的工作原理所致：它会先对列中的所有非 NULL 值进行加和操作，然后再除以非 NULL 值的个数来得到平均值。但是，如果列中包含 NULL 值，那么这些 NULL 值在计算总和时会被忽略，这就会导致平均值的计算结果出现错误。

具体来说，AVG() 函数在处理存在 NULL 值的列时，可能会出现以下三种异常情况：

• 结果返回为 NULL
• 平均值偏小
• 平均值偏大

接下来我们以示例的方式来说明这三种异常情况。

结果返回为 NULL

如果列中所有的值都是 NULL，那么 AVG() 函数将返回 NULL。这是因为计算平均值时需要先对值进行求和操作，而如果没有任何有效的值，则无法进行求和操作，结果就为空值。

示例2：计算一个全为 NULL 的列的平均值

假设我们有一个只有一个列 column1 的表 test，其中所有的值都是 NULL。我们可以使用以下语句尝试计算 column1 的平均值：

SELECT AVG(column1) FROM test;

返回结果为 NULL。这就是因为该列中没有任何有效的值，无法进行求和操作，结果为空值。

平均值偏小

如果列中既包含 NULL 值，又包含非 NULL 值，那么 AVG() 函数计算出的平均值可能会偏小。这是因为 NULL 值在计算总和时会被忽略，造成了一定的偏差。

示例3：计算一个含有 NULL 值的列的平均值

假设我们有一个只有一个列 column2 的表 test，其中包含数值和 NULL 值。我们可以使用以下语句计算 column2 的平均值：

SELECT AVG(column2) FROM test;

返回结果为 2，而实际上 column2 中的数值总和是 4，有效值的个数是 2，所以平均值应该是 2。但是由于含有 NULL 值，AVG() 函数忽略了这个 NULL 值，导致计算出的平均值比实际值偏小了一半。

平均值偏大

AVG() 函数还可能会出现一种略微复杂的偏差情况，即平均值偏大。这种情况通常出现在含有多个 NULL 值和少量非 NULL 值的列中。

示例4：计算一个含有多个 NULL 值的列的平均值

假设我们有一个只有一个列 column3 的表 test，其中包含数值和 NULL 值。我们可以使用以下语句计算 column3 的平均值：

SELECT AVG(column3) FROM test;

返回结果为 5，而实际上 column3 中的数值总和是 10，有效值的个数是 2，所以平均值应该是 5。但是由于含有多个 NULL 值，AVG() 函数忽略了这些 NULL 值，导致计算出的平均值比实际值偏大了一倍。

如何解决 AVG() 函数的异常

为了解决 AVG() 函数中存在 NULL 值造成的异常，我们可以采取以下两种方法：

1. 使用 IFNULL() 函数

IFNULL() 函数用于判断一个表达式是否为 NULL 值，如果是，则返回指定的替代值；否则返回表达式的值。我们可以在使用 AVG() 函数时，将 NULL 值替换为零，这样计算得到的总和就会包括空值。

示例5：使用 IFNULL() 函数计算含有 NULL 值的列的平均值

假设我们有一个只有一个列 column4 的表 test，其中包含数值和 NULL 值。我们可以使用以下语句计算 column4 的平均值，其中使用 IFNULL() 函数把 NULL 值替换为零：

SELECT AVG(IFNULL(column4, 0)) FROM test;

这会返回一个正确的平均值。

2. 使用 COUNT() 函数

COUNT() 函数用于计算一个列中非 NULL 值的个数。AVG() 函数在计算平均值时，除以的是非 NULL 值的个数，所以我们可以先统计出有效值的个数，然后再除以该个数来得到平均值。

示例6：使用 COUNT() 函数计算含有 NULL 值的列的平均值

假设我们有一个只有一个列 column5 的表 test，其中包含数值和 NULL 值。我们可以使用以下语句先计算有效值的个数，然后再计算 column5 的平均值：

SELECT AVG(column5) FROM test WHERE column5 IS NOT NULL;

这会返回一个正确的平均值。

总结

AVG() 函数在计算平均值时，存在 NULL 值的列会出现异常结果。我们可以采用 IFNULL() 函数或 COUNT() 函数来解决这个问题。在使用 AVG() 函数时，我们需要谨慎对待 NULL 值，以免出现奇怪的结果。