使用group by分组时,查询字段没有分组的字段就会报错
[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.id'
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因:
mysql5.7版本和以上的版本都默认开启了only_full_group_by 模式
我这以MySQL8.0.25版本为例
解决方法:
1、查看sql_mode
SELECT @@sql_mode;
查询结果
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
2、将查询的结果去掉ONLY_FULL_GROUP_BY,就成了:
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
3、去阿里云控制台,找到云数据库RDS→参数设置
将STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION设置进去
重新启动云数据库RDS,运行参数变成了:
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
在使用group by分组时,查询字段没有分组的字段就不会报错
标签:full,group,RDS,NO,ZERO,mode,sql,DATE From: https://www.cnblogs.com/Amywangqing/p/16813522.html