MySQL5.7版本sql_mode=only_full_group_by问题

0、问题描述

下载安装的最新版的mysql5.7.x版本，默认是开启了 only_full_group_by 模式的，但开启这个模式后，执行原先的 group by 语句就报错。

SELECT * FROM student GROUP BY class;

# 报错，only_full_group_by开启的情况下，只允许返回GROUP BY 涉及的字段
# 一旦开启 only_full_group_by ，group by 的行为就变得和 distinct 一样了，只能获取受到其影响的字段信息，无法和其他未受其影响的字段共存

以下是SQL查询的错误提示信息：

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

1、查看sql_mode

select @@global.sql_mode;

查询出来的值为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2、解决方法

建议采用Percona的方法：Solve Query Failures Regarding ONLY_FULL_GROUP_BY SQL Mode

注意：使用SET GLOBAL sql_mode进行设置以后，需要重新建立数据库链接（重启应用程序或者DataGrid工具）才能生效。

3、后续总结：

实际上，only_full_group_by 模式确实是开启比较好。

• 其一，更符合SQL标准，
• 其二，在 MySQL 中有函数 any_value(field)， 允许返回非分组字段的出现（和关闭 only_full_group_by 模式有相同效果）。

但是考虑到很多已有项目的SQL代码，这个设置方式依然是有实际意义的。

 

此外，在我的印象里，Spring Data JPA中的JPQL不受此问题影响，可能也和application.yaml中具体的SQL方言设置有关，待后续观察。