"only_full_group_by"sql模式对GROUP BY的影响及处理

4,790 阅读4分钟

「这是我参与2022首次更文挑战的第7天,活动详情查看:2022首次更文挑战

前言

前段时间在项目开发过程中发现了系统异常,打开日志查看发现了如下的这个报错:查找相关资料,这是因为mysql数据库的sql_mode=only_full_group_by开启了,因为年前产品急着上线,于是组长说先联系DBA把sql_mode=only_full_group_by关掉,后期再改。

c4738acdb88ffaa46434e01ade7c1095.png

于是,今天组长就把这个任务交给我了。

6c328587e2abaeb11e29f9a303a5b3e3.png

ONLY_FULL_GROUP_BY官方说明:

Reject 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 (uniquely determined by) GROUP BY columns.

拒绝选择列表、 HAVING条件或ORDER BY列表引用非聚合列的查询,这些列既不在GROUP BY子句中命名,也不在功能上依赖于(唯一确定的) GROUP BY列。(谷歌翻译)

解决办法

官方文档:MySQL 对 GROUP BY 的处理

1.关闭sql_mode=only_full_group_by

2.官方说明了:You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column. 您可以在不禁 ONLY_FULL_GROUP_BY用 的情况下通过ANY_VALUE()引用非聚合列来实现相同的效果。

问题复现

下面我们先来做一个小例子复现一下这个错误:

数据准备

  1. 创建表
CREATE TABLE `emp`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	`age` INT(3),
	`salary` DOUBLE(10,2),
	PRIMARY KEY (id)
	)ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. 插入数据
	INSERT INTO `emp` VALUE(null,'张三',21,10000);
	INSERT INTO `emp` VALUE(null,'李四',22,15000);
	INSERT INTO `emp` VALUE(null,'王五',23,20000);
	INSERT INTO `emp` VALUE(null,'赵六',24,25000);
	INSERT INTO `emp` VALUE(null,'钱七',21,10000);
	INSERT INTO `emp` VALUE(null,'孙八',22,15000);
	INSERT INTO `emp` VALUE(null,'李九',23,20000);
	INSERT INTO `emp` VALUE(null,'冯十',24,25000);

查看sql_mode

使用下面的sql查看sql模式是否含有ONLY_FULL_GROUP_BY

SELECT @@session.sql_mode;

07946a889b7d8b71b8b8d4b057905428.png

可以看到sql_mode里面是开启了ONLY_FULL_GROUP_BY的。

如果未开启可以使用如下命令开启:

SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

关闭:

SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

目前我的sql模式ONLY_FULL_GROUP_BY是开启的,接下来我们测试一下GROUP_BY

sql实例

首先我们来一个正确的查询:

SELECT age, Max(salary) FROM emp GROUP BY age;

392a7383f83b16d1074dc4396bb540ab.png

对于聚合列来说没有影响,只对非聚合列检查是否存在与GROUP BY list中。

下面是一个错误的查询:

SELECT id,`name` age, Max(salary) FROM emp GROUP BY age;

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sql_mode_test.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决办法:

  1. 修改sql_mode移除ONLY_FULL_GROUP_BY配置
SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

再次查询:

e80e9b1adcc65b640deddcd039498579.png

  1. 给非聚合列非Group By列加上函数ANY_VALUE(非聚合列)
SELECT ANY_VALUE(id) as id,ANY_VALUE(name) as name ,age, Max(salary) FROM emp GROUP BY age;

908e8f57bf1f4541676106f9eb9468b8.png

显然这两种方法都能让程序不报错,但是这里的id和name并不是确定的唯一的值,没有意义,只是在符合条件的组里挑选了其中一条来填充。

  1. GROUP BY后面加上select列表中的非聚合列,这种方式解决了报错但改变了sql查询结果,所以不适用
SELECT id, `name` age, Max(salary) FROM emp GROUP BY age,id,name;

*需要注意的一点是,当开启了ONLY_FULL_GROUP_BY模式,当查询没有group by语句,select 列表有聚合函数时,会报错

SELECT `name`, MAX(age) FROM emp;

1140 - In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sql_mode_test.emp.name'; this is incompatible with sql_mode=only_full_group_by

这里可以使用ANY_VALUE()来解决:

SELECT ANY_VALUE(`name`) as name , MAX(age) FROM emp;

小结:

sql模式开启ONLY_FULL_GROUP_BY时,如果有GROUP BY,非聚合列要存在必须是GROUP BY 列表中的列,如果不是GROUP BY后面的列,可以使用ANY_VALUE(column)转换为聚合列。

如果没有GROUP BY,SELECT 聚合列和非聚合列不能同时存在,如果要同时存在可以使用ANY_VALUE(column)转换为聚合列。

回到项目

查看mapper文件,我查看了包含GROUP BY的查询sql,看到了这样的SELECT ,SELECT所有字段。

99556f6bf67e03cfa1c2889f9bcbb460.png

这么多要改,ONLY_FULL_GROUP_BY肯定是不能关闭的,只能改sql了,于是我在想有没有办法能够实现在查询的时候自动修改,加上ANY_VALUE(),于是就想到了拦截器,正好,mybits提供了拦截器,可以对sql进行处理,于是我就开始尝试,今天先到这里,后续将会更新我的解决办法。如果大家有更好的方法也可以在评论区留下你的宝贵意见。