奇怪的MySQL AVG（）异常NULL值

Question

我在做什么 ：

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

MYSQL的输出：

平均值=（10 + 20 + 30）/ 3 = 20

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

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

谢谢

Answer 1

这是正确的行为，因为NULL与number不同0。

从概念上讲，它NULL意味着“缺失的未知值”，并且与其他值的处理方式有所不同。这就是为什么聚合函数（如AVG()ignore NULLs）的原因。

AVG()仅计算所有 已知值 的平均值。（= 不为 NULL =未知）

从MySQL文档：

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

另外，请 在MySQL手册的“
3.3.4.6使用NULL值”
中阅读有关NULLs 的概念。

为了得到想要的东西，你可以做

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

IFNULL()如果值是NULL或通过该值，则返回第二个参数进行计算。

关于…的概念还有更多的误解NULL。在手册的“ 5.5.3
NULL问题”
中也对此进行了说明：

• 在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值。