MySQL 5.7与8中含COUNT聚合函数的查询结果差异原因探究

问题场景 #

将基于Java Spring的遗留产品从MySQL 5.7迁移至MySQL 8.0时，遇到一条包含COUNT聚合函数但未使用GROUP BY的SQL查询，两个版本返回结果不一致：

• 测试场景因bypassing_client条件约束，预期返回空集
• MySQL 5.7返回一条含有效非聚合字段数据的记录，participantsNumber为0
• MySQL 8.0返回一条所有字段（包括participantsNumber）全为NULL的记录
• 移除count(c.callID) as participantsNumber后，两个版本均返回空集

两个版本的sql_mode配置 #

MySQL 5.7

SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

MySQL 8.0

SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

核心原因分析 #

这是MySQL在处理无GROUP BY的聚合查询时，对非聚合字段返回逻辑的版本差异导致的，关键在于ONLY_FULL_GROUP_BY模式下的行为优化：

• COUNT聚合函数的特性：COUNT属于聚合函数，即便没有匹配的行，也会返回一个结果行——当未指定GROUP BY时，整个结果集视为一个组，哪怕组内无数据，也会返回该组的聚合计算结果。
• MySQL 5.7的兼容行为：虽然开启了ONLY_FULL_GROUP_BY，但5.7在无GROUP BY且无匹配行时，会随机选取一条存在的行（哪怕不满足查询条件）的非聚合字段数据返回，同时COUNT返回0。这种处理不符合SQL标准，是历史兼容的遗留行为。
• MySQL 8.0的严格化处理：8.0对ONLY_FULL_GROUP_BY的执行逻辑做了严格对齐标准的优化：无GROUP BY且无匹配行时，非聚合字段没有合法取值来源，因此返回NULL；而COUNT(c.callID)因为没有匹配行（无callID值可统计），也返回NULL（如果是COUNT(*)则会返回0）。
• 移除COUNT后行为一致的原因：去掉聚合函数后，查询变为普通SELECT，无匹配行时自然返回空集，符合SQL标准，所以两个版本表现一致。

内容的提问来源于stack exchange，提问作者lopushen