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_byn; 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;

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
分享
二维码
< <上一篇
下一篇>>