【Java报错】数据库查询报错this is incompatible with sql_mode=only_full_group_by
报错信息
完整报错信息如图
Error querying database. Cause: java.sql.SQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'lst_as.t.create_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ### The error may exist in URL [jar:file:/data/wxjz/version/run/linjiashop-admin-api-as-1.0.jar!/BOOT-INF/lib/linjiashop-core-1.0.jar!/com/jiexun/user/mapper/RcustomerAccountInfo.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT DATE_FORMAT(t.create_time, '%Y-%m') AS date, SUM(t.wait_repayment/100) AS waitingPay FROM t_customer_account_info t WHERE t.create_time = ( SELECT MAX(t2.create_time) FROM t_customer_account_info t2 WHERE t2.account_id = t.account_id AND YEAR(t2.create_time) = YEAR(t.create_time) AND MONTH(t2.create_time) = MONTH(t.create_time) ) AND t.tenant_id = ? GROUP BY DATE_FORMAT(t.create_time, '%Y-%m') ORDER BY t.create_time; ### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'lst_as.t.create_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'lst_as.t.create_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
报错SQL语句
SELECT DATE_FORMAT(t.create_time, '%Y-%m') AS date, SUM(t.wait_repayment/100) AS waitingPay FROM t_customer_account_info t WHERE t.create_time = ( SELECT MAX(t2.create_time) FROM t_customer_account_info t2 WHERE t2.account_id = t.account_id AND YEAR(t2.create_time) = YEAR(t.create_time) AND MONTH(t2.create_time) = MONTH(t.create_time) ) AND t.tenant_id = ? GROUP BY DATE_FORMAT(t.create_time, '%Y-%m') ORDER BY t.create_time;
错误原因
这个错误是由于MySQL的ONLY_FULL_GROUP_BY模式导致的。在ONLY_FULL_GROUP_BY模式下，SQL查询中的ORDER BY子句中的列必须出现在GROUP BY子句中，或者在聚合函数中使用。
如上述SQL，在SQL查询中，ORDER BY t.create_time中的t.create_time没有出现在GROUP BY子句中，因此导致了这个错误。
解决方法
1.修改SQL查询
将ORDER BY子句中的列改为聚合函数或添加到GROUP BY子句中。
可以将ORDER BY子句中的t.create_time改为ORDER BY date，因为date已经在GROUP BY子句中。修改后的SQL查询如下：
SELECT DATE_FORMAT(t.create_time, '%Y-%m') AS date, SUM(t.wait_repayment/100) AS waitingPay FROM t_customer_account_info t WHERE t.create_time = ( SELECT MAX(t2.create_time) FROM t_customer_account_info t2 WHERE t2.account_id = t.account_id AND YEAR(t2.create_time) = YEAR(t.create_time) AND MONTH(t2.create_time) = MONTH(t.create_time) ) AND t.tenant_id = ? GROUP BY DATE_FORMAT(t.create_time, '%Y-%m') ORDER BY date;
2.修改MySQL配置
2.1 通过SQL命令临时修改（重启后失效）
如果你暂时不想修改配置文件，可以使用SQL命令临时修改sql_mode。但请注意，这种方法在MySQL重启后会失效。
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
2.2 修改MySQL配置文件（永久生效，推荐）
首先找到MySQL配置文件，MySQL的配置文件通常是my.cnf（在Linux上）或my.ini（在Windows上）。你可以通过以下命令找到配置文件的位置：
mysql --help | grep my.cnf
然后编辑配置文件：
- 打开配置文件，找到或添加[mysqld]部分。
- 修改或添加sql_mode配置，移除
ONLY_FULL_GROUP_BY。
例如，修改后的配置文件内容如下：
最后保存并重启MySQL服务，在Linux上，你可以使用以下命令重启MySQL服务
sudo systemctl restart mysql
在Windows上，你可以通过服务管理器（通过运行services.msc打开）重启MySQL服务
至此就解决了以上报错问题