侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 285 篇文章
  • 累计创建 125 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

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

孔子说JAVA
2022-07-07 / 0 评论 / 0 点赞 / 63 阅读 / 3,923 字 / 正在检测是否收录...

MySQL5.7.5及以上版本在进行group by查询报错:ORDER BY clause is not in GROUP BY clause and contains nonaggregated columnSELECT 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语句中,也没有使用聚合函数。

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

  1. 可正确执行的语句:
select u.api_id, count(u.param_id) from base_api_param u
where u.`status` = 0
GROUP BY u.api_id
  1. 执行有报错的语句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
  1. 执行有报错的语句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

image-1657156770747

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

3、解决办法

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

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

  1. 办法一:只在当前查询页面有效,不能一次性解决问题!

执行下述语句:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

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

select version(), @@sql_mode;
  1. 办法二:修改 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.serviceservice 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. 执行有报错的语句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
  1. 执行有报错的语句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后,不能用双引号来引用字符串,因为它被解释为识别符
0

评论区