MySQL解决ONLY_FULL_GROUP_BY的几个方法

问题

employee 示例数据库为例,测试环境用了一条语句:

SELECT * FROM employees GROUP BY gender;

在测试环境运行正常,但是在线上就会有问题,报错如下:

1
2
3
4
5
6
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mse.msc_k8s_cluster.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)

原因

MySQL 在ONLY_FULL_GROUP_BY模式下,有如下约束:

执行了带 GROUP BY 和 ORDER BY的SELECT语句,就需要保证你 SELECT 的列都在 GROUP BY 和 ORDER BY 中。

打个比方,如果有数据如下:

emp_no gender
1 M
2 M

那么SELECT emp_no,gender FROM employees GROUP BY gender;只会返回一条记录,那么emp_no字段无法同时显示1和2,在MySQL看来,这个是不规范的使用方式,所以会有开始的报错。

解决方案

添加列之间的依赖

我们可以让emp_no与gender一一对应,比如emp_no与gender建立联合索引。那么就不会出现emp_no字段的问题了。

但是显然这儿还是有问题的,因为这个对数据有影响,改造成本太高。

ANY_VALUE

我们可以用ANY_VALUE()来告诉MySQL,例子中emp_no的值,任意值就可以了。

这个方案比第一种要好些,但是仍然会有改造成本,要改造查询语句。比如要改成 SELECT ANY_VALUE(emp_no),gender FROM employees GROUP BY gender;

关闭ONLY_FULL_GROUP_BY

这个限制是ONLY_FULL_GROUP_BY模式带来的,可以通过关闭ONLY_FULL_GROUP_BY模式来解决。

全局关闭ONLY_FULL_GROUP_BY

找到 MySQL 配置文件(my.cnf)修改并保存。先通过如下命令找到配置文件路径:

1
2
3
$ mysql --help | grep cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

找到后编辑并保存,重启 MySQL 后生效。

1
2
3
[mysqld]
-sql_mode=ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION
+sql_mode=NO_ENGINE_SUBSTITUTION

通过sql_mode变量来关闭ONLY_FULL_GROUP_BY

查看现在的sql_mode:

1
2
3
4
5
6
7
mysql> SELECT @@sql_mode;
+-------------------------------------------+
| @@sql_mode |
+-------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------+
1 row in set (0.02 sec)

通过如下指令关闭:SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));

在Spring Boot/HikariCP中关闭ONLY_FULL_GROUP_BY

由于connection-init-sql不支持多个MySQL语句,加上原来就有SET SESSION time_zone='+08:00',导致这个不好设置。

翻了下MySQL设置变量的文档,可以在一条语句中设置多个变量,比如我们就可以这么写:

1
2
3
4
5
6
spring:
datasource:
hikari:
connection-init-sql: >
SET SESSION time_zone='+08:00',
SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))

参考的一些资料:

MySQL解决ONLY_FULL_GROUP_BY的几个方法

https://robberphex.com/mysql-fix-only-full-group-by/

作者

Robert Lu

发布于

2021-12-11

许可协议


评论

Robert Lu

关注我的公众号