MySql在ONLY_FULL_GROUP_BY模式下分组查询报错问题的解决

By adminmysql360On 2022年12月26日2022年12月26日

MySQL5.7.5及以上版本在进行group by查询报错：ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 或 SELECT list is not in GROUP BY clause …。这是因为在MySQL5.7之后，sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态，此模式要求分组查询时的列除聚合函数外必须包含在group by字段里，而我们的查询语句不符合要求，如 SELECT proviance_code, proviance_name from area GROUP BY proviance_code;，此语句中 proviance_name 列不在group by语句中，也没有使用聚合函数。

相关文章可参考：MySql中group by分组查询的最佳搭档ANY_VALUE函数

1、正确与错误语句对比示例

可正确执行的语句：

select u.api_id, count(u.param_id) from base_api_param u
where u.status = 0
GROUP BY u.api_id

执行有报错的语句1：

select u.api_id, count(u.param_id) from base_api_param u
where u.status = 0
GROUP BY u.api_id
ORDER BY u.create_time desc

具体报错信息如下：

[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘bspvue.u.create_time’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

执行有报错的语句2：

select u.api_id, u.code_type, count(u.param_id) from base_api_param u
where u.status = 0
GROUP BY u.api_id

具体报错信息如下：

[Err] 1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘bspvue.u.code_type’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

2、错误原因

从上述错误语句的报错原因中可以看到，它们的最后都是同样的语句 this is incompatible with sql_mode=only_full_group_by，这句话的意思是指我们编写的查询语法与 sql_mode 的配置 only_full_group_by 不兼容。

sql_mode 是 mysql 的一个配置项，根据配置的值不同可提供不同的功能，这是一个功能配置项，不是性能配置项。

MySQL 5.7.5及以上版本开启了依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下会开启），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。（5.7.5之前，MySQL没有检测到功能依赖关系，默认情况下不启用ONLY_FULL_GROUP_BY）。即 MySQL5.7.5及以上版本在进行group by 查询时，查询的所有列除了来自于聚合函数（sum、avg、max等）的结果外，其他列都要求在group by字段里，否则会报 [Err] 1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated… 类似的错误。

我们可以查询mysql的sql_mode模式：

select version(), @@sql_mode;

返回结果：

8.0.29 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

从查询结果可以看到，mysql是开启了ONLY_FULL_GROUP_BY SQL模式的。

3、解决办法

3.1 解决方案一：禁用only_full_group_by功能

禁用only_full_group_by功能，即把 sql_mode中的 only_full_group_by 去掉即可。

办法一：只在当前查询页面有效，不能一次性解决问题！

执行下述语句：

SET sql_mode=(SELECT REPLACE(@@sql_mode,’ONLY_FULL_GROUP_BY’,”));

再次查询sql_mode后可以发现sql_mode中已经没有了ONLY_FULL_GROUP_BY：

select version(), @@sql_mode;

办法二：修改 my.cnf 文件的 sql_mode （一次性解决问题）

查找 my.cnf 文件： find / -name my.cnf 或者 whereis my.cnf
获取 sql_mode 字段内容： SELECT @@sql_mode;
去掉 sql_mode 字段中的 only_full_group_by
编辑 my.cnf： vim /etc/my.cnf 将上一步骤的 sql_mode 字段内容添加至 my.cnf的底部。
保存文件，重启mysql服务： systemctl restart mysqld.service 或 service mysqld restart。

my.cnf添加内容可参考：

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

修改位置：linux系统/etc/my.cnf文件，windows 系统 my.ini

3.2 解决方案二：使用 any_value 函数

将没有使用 group by 的字段用 any_value 函数包裹起来，表示取结果中的任意一个值。

执行有报错的语句1 – 修改方法：

select u.api_id, count(u.param_id) from base_api_param u
where u.status = 0
GROUP BY u.api_id
ORDER BY u.create_time desc

修改为：

select u.api_id, count(u.param_id) from base_api_param u
where u.status = 0
GROUP BY u.api_id
ORDER BY any_value(u.create_time) desc

执行有报错的语句2 – 修改方法：

select u.api_id, u.code_type, count(u.param_id) from base_api_param u
where u.status = 0
GROUP BY u.api_id

修改为：

select u.api_id, any_value(u.code_type), count(u.param_id) from base_api_param u
where u.status = 0
GROUP BY u.api_id

4、sql_mode可选值说明

ONLY_FULL_GROUP_BY：

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO：

该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES：

在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

NO_ZERO_IN_DATE：

在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：

设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：

在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER：

禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION：

如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT：

将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

ANSI_QUOTES：

启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

浏览量: 55