sql_mode=only_full_group_by问题解决
程序员文章站
2023-12-28 11:53:10
...
数据库分组查询报错:
1 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
推荐阅读
-
“this is incompatible with sql_mode=only_full_group_by“问题解决
-
sql_mode=only_full_group_by问题解决
-
ONLY_FULL_GROUP_BY
-
mysql5.7 only_full_group_by问题
-
Mysql的sql_mode=only_full_group_by问题解决
-
MySQL5.7版本sql_mode=only_full_group_by问题解决办法
-
ONLY_FULL_GROUP_BY
-
mysql高版本 出现this is incompatible with sql_mode=only_full_group_by
-
Mysql 关闭ONLY_FULL_GROUP_BY
-
mysql only_full_group_by以及其他关于sql_mode原因报错详细解决方案