MySQL8的ONLY_FULL_GROUP_BY SQL模式兼容问题

1. 问题描述

Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'btc-cloud.t1.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by\n; bad SQL grammar []; nested exce

错误信息是关于MySQL的ONLY_FULL_GROUP_BY SQL模式的一个限制。从MySQL 5.7.5开始，ONLY_FULL_GROUP_BY模式成为了默认的SQL模式，这意味着在执行GROUP BY查询时，SELECT列表中的每个列都必须是聚合函数的一部分，或者是GROUP BY子句中指定的列。

2. 解决方法

1. 修改查询

您可以在SELECT列表中只选择那些在GROUP BY子句中出现的列，或者使用聚合函数（如MAX()、MIN()、SUM()、AVG()等）来处理那些不在GROUP BY子句中的列。

2. 修改SQL模式

如果您需要执行这样的查询，并且不希望改变查询本身，您可以在会话级别或全局级别关闭ONLY_FULL_GROUP_BY模式。但是，这样做可能会影响其他查询的正确性，因此请谨慎操作。

• 会话级别（仅对当前会话有效）：

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

• 全局级别（对所有会话有效，需要重启MySQL）：
  修改my.cnf（Linux）或my.ini（Windows）配置文件，在[mysqld]部分添加以下行：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1

然后重启MySQL服务。
Ubuntu环境详细操作教程

3. 使用ANY_VALUE()函数

在MySQL 5.7.5及以上版本中，可以使用ANY_VALUE()函数来抑制ONLY_FULL_GROUP_BY模式的警告。修改查询，将非聚合列包裹在ANY_VALUE()函数中：

SELECT ANY_VALUE(t1.id), column2, AVG(column3)
FROM t1
GROUP BY column2;
1
2
3