MySQL中由于sql_mode不兼容问题(sql_mode=only_full_group_by)导致正常的sql执行报错的问题
###Error querying database.
Cause: java.sql.SQLSyntaxErrorException:
Expression #1
of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘db.table.xxx’ which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
1. 当目标数据库的配置文件是我们可以接触的情况下
# 解决办法下my.cnf中添加以下几行
[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
改完后,重启mysql服务
ps: 其实就是去掉了通过select @@sql_mode;
查询出的结果中的only_full_group_by
部分
2. 无法接触到数据库,只能远程访问时
这种情况下,我们只能改自己的sql,在包含group by的子查询中,对select后的字段用ANY_VALUE(xxx)处理一下
# 原sql,执行会报错
SELECT user_id, order_id, FROM t_order GROUP BY some_id
# 处理后
SELECT ANY_VALUE(user_id) as user_id, ANY_VALUE(order_id) as order_id, FROM t_order GROUP BY some_id
如果你的mysql数据库在docker里,你能通过docker exec 进入的话,也可以直接改配置