sql_mode=only_full_group_by问题解决

数据库分组查询报错：

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘fortress.PartnerSetting.Id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by SQL.sql 3 16

数据库脚本：

select
DISTINCT(AppId),
Id,
MchId,
CompanyId,
PartnerSettingName,
Provider,
ReturnUrl,
NotifyUrl,
RefundNotifyUrl,
Status
from PartnerSetting
where status = 0 GROUP BY AppId;

---

解决办法：

1.查询sql_model脚本：

SELECT @@sql_mode;

查询出来的是:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2.修改sql_model:

set@@sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

可以再次查询sql_model，可以看到去掉了
ONLY_FULL_GROUP_BY

---

总结：查询出来的结果，不论是什么，把前面的 ONLY_FULL_GROUP_BY 去掉，然后剩下的全部复制，粘贴。在set一下，就可以了

---

转载自:https://blog.youkuaiyun.com/dxh0823/article/details/80942103