MySQL8的ONLY_FULL_GROUP_BY SQL模式兼容问题
文章目录
- 1. 问题描述
- 2. 解决方法
- 1. 修改查询
- 2. 修改SQL模式
- 3. 使用`ANY_VALUE()`函数
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',''));
- 全局级别（对所有会话有效，需要重启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
然后重启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;