MySQL解决ONLY_FULL_GROUP_BY的几个方法

问题

以 employee 示例数据库为例，测试环境用了一条语句：

SELECT * FROM employees GROUP BY gender;

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

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：

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设置变量的文档，可以在一条语句中设置多个变量，比如我们就可以这么写：

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

参考的一些资料：