奇怪的 MySQL AVG() 异常 NULL 值

2022-01-06 00:00:00 sql null mysql

我在做什么:

create table sample (id INT(10) PRIMARY KEY AUTO_INCREMENT,name varchar(255),marks INT(10));

insert into sample (name,marks) VALUES('sam',10);
insert into sample (name,marks) VALUES('sam',20);
insert into sample (name,marks) VALUES('sam',NULL);
insert into sample (name,marks) VALUES('sam',NULL);
insert into sample (name,marks) VALUES('sam',30);

select AVG(marks) from sample GROUP BY(name);

我预期的输出:

平均值 = (10+20+30)/5 = 12

AVG = (10+20+30)/5 = 12

MYSQL的输出:

平均值 = (10+20+30)/3 = 20

AVG = (10+20+30)/3 = 20

理想情况下，我想要的是 MYSQL 应该得到 5 行的总和并将其除以 5，但它只除以 3(非 NULL 行)

Ideally what i wanted is that MYSQL should get the sum of 5 rows and divide it by 5 , but it only divides by 3 (the non-NULL rows)

为什么会发生这种情况，我该怎么做才能获得正确的 AVG，即 60/5?PS:我不能让标记字段为 NOT NULL ，在我的数据库设计中，标记字段允许为 NULL.

Why does this occur and what can i do to get the correct AVG ie 60/5 ? PS: I cannot make the marks field NOT NULL , in my db design the marks field is allowed to be NULL.

谢谢

推荐答案

这是正确的行为，因为 NULL 与数字 0 不同.
这可能会让一些不会说英语的人感到惊讶，因为在许多语言中，null"是指null".等价于零".

This is the correct behavior, because NULL is not the same as the number 0.
This might surprise some non-english speakers, because in many languages "null" is equivalent to "zero".

概念上，NULL 指的是未知值"，因此它与其他值的处理方式不同.这就是为什么聚合函数像AVG() 忽略 NULLs.

AVG() 计算所有已知"的平均值.仅值.(= 不是 NULL)

来自 MySQL 文档:

除非另有说明，否则组函数会忽略 NULL 值.

Unless otherwise stated, group functions ignore NULL values.

此外，请阅读 NULL 概念" rel="nofollow noreferrer">MySQL 手册的3.3.4.6 使用 NULL 值"部分.

Also, read about the concept of NULLs in Section "3.3.4.6 Working with NULL Values" of the MySQL manual.

为了得到你想要的，你可以这样做

To get what you want, you might do

SELECT AVG(IFNULL(marks, 0)) 
FROM sample 
GROUP BY name;

IFNULL()<如果值为 NULL，则/code> 返回计算的第二个参数，否则通过该值.

IFNULL() returns the second argument for calculations if the value is NULL or passes through the value otherwise.

对于NULL 的概念有更多常见的误解.这些也在部分5.5.3 问题"中进行了解释使用手册的 NULL":

There are more common misunderstandings regarding the concept of NULL. These are also explained in Section "5.5.3 Problems with NULL" of the manual:

  • 在 SQL 中，NULL 值与任何其他值相比永远不会为真，即使是 NULL.一个包含 NULL 的表达式总是产生一个 NULL 值，除非在表达式中涉及的运算符和函数的文档中另有说明.

    即:NULL == 0 导致 NULL 而不是 true.同样 NULL == NULL 导致 NULL，而不是 true.
  • 要搜索 NULL 的列值，您不能使用 expr = NULL 测试.要查找 NULL 值，您必须使用 IS NULL 测试.
  • 当使用DISTINCT、GROUP BY 或ORDER BY 时，所有NULL 值都被视为相等.
  • 使用 ORDER BY 时，NULL 值首先显示，如果指定 DESC 以降序排序，则最后显示.
  • 对于某些数据类型，MySQL 专门处理 NULL 值.如果将 NULL 插入 TIMESTAMP 列，则会插入当前日期和时间.
  • 如果将 NULL 插入到具有 AUTO_INCREMENT 属性的整数或浮点列中，则会插入序列中的下一个数字.
  • 定义了 UNIQUE 键的列仍然可以包含多个 NULL 值.
  • In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.

    i.e.: NULL == 0 results in NULL instead of true. Also NULL == NULL results in NULL, instead of true.
  • To search for column values that are NULL, you cannot use an expr = NULL test. To look for NULL values, you must use the IS NULL test.
  • When using DISTINCT, GROUP BY, or ORDER BY, all NULL values are regarded as equal.
  • When using ORDER BY, NULL values are presented first, or last if you specify DESC to sort in descending order.
  • For some data types, MySQL handles NULL values specially. If you insert NULL into a TIMESTAMP column, the current date and time is inserted.
  • If you insert NULL into an integer or floating-point column that has the AUTO_INCREMENT attribute, the next number in the sequence is inserted.
  • A column that has a UNIQUE key defined can still contain multiple NULL values.

相关文章