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

sqlmode查询
sqlmode查询

从查询结果可以看到,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后,不能用双引号来引用字符串,因为它被解释为识别符