欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

上一篇:

下一篇: