万物之中, 希望至美.

MySQL中 only_full_group_by 问题

2018.08.08

最近在试用 MySQL 数据库做查询时,遇到了这么个问题,当我使用 GROUP_BY对要查询的数据进行分组的时候,抛出了一个InternalError的错误,当时我:???

1055, “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘xxx’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by”

意思就是说,你写的 SQL 语句在sql_mode=only_full_group_by模式下是不兼容的,非聚类列‘xxx’没有出现在 GROUP_BY 子句中。

用Google搜了下,在官方文档上发现了这个问题的详细说明,具体请看:MySQL :: MySQL 5.7 Reference Manual :: 12.19.3 MySQL Handling of GROUP BY

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

大致就是在 MySQL 5.7.5 版本之前,遵循 SQL92 标准的 ONLY_FULL_GROUP_BY 模式在默认情况下是关闭的,MySQL 并未强行按照 SQL92 标准编写查询语句,而在 5.7.5 之后的版本中,ONLY_FULL_GROUP_BY 的含义改变了,由于遵循了 SQL99 标准,现在将实现更为复杂的功能。也就是说,有时候写的代码变少了,也同样可以实现查询功能。

解决方法

1.关闭 ONLY_FULL_GROUP_BY 模式

关闭 ONLY_FULL_GROUP_BY 方法 这个方法最简单粗暴,但也同样有着问题,在文档中给出了相关的说明,当 ONLY_FULL_GROUP_BY 模式被禁用时,查询出来的每个组中值是随机选取的,这就导致结果可能不符合你的预期。

2.使用 ANY_VALUE

SELECT name, address, MAX(age) FROM t GROUP BY name;

这么一条查询语句在不禁用 ONLY_FULL_GROUP_BY 模式的情况下,也会出现最开始的错误。

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

而使用 ANY_VALUE 函数后,则不会,ANY_VALUE 同样是随机选取值。

3.更好的SQL查询语句

这个还在学习中,以后再做补充

comments powered by Disqus