Mysql数据库报错:select list is not in group by clause and contains .....sql_mode=only_full_group_by问题
程序员文章站
2023-12-28 14:29:04
...
最近使用Mysql时遇到select list is not in group by clause and contains nonaggregated…sql_mode=only_full_group_by这个错误,查询资料最后解决了,在这里分享一下,我用的是Mysql5.7,Mysql5.7默认会开启only_full_group_by 模式,在这个模式下,group by会变成和 distinct类似,只会获取受其影响的字段信息,和其他未受其影响的字段不能共存,就会报出上述错误,这样group by的功能将变的受限,所以Mysql中有另一个函数解决这个问题
解决办法如下:
第一种:使用any_value()函数
1、在sql查询语句中不需要group by的字段上使用any_value()函数,用法:any_value(字段名)
如果需要修改的sql数量比较多的情况下这种方法不太合适,需要将所有的sql都修改一遍
第二种:关闭only_full_group_by 模式
1、查询sql_model的配置
select @@global.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、将第一段ONLY_FULL_GROUP_BY去掉,重新设置值
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
3、上面是改变了全局sql_mode,对于新建的数据库有效。对于已存在的数据库,则需要在对应的数据库下执行:
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';
推荐阅读
-
Mysql数据库报错:select list is not in group by clause and contains .....sql_mode=only_full_group_by问题
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.
-
linux上,mysql使用聚合函数group by 时报错:SELECT list is not in GROUP BY clause and contains nonaggre的问题
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-
解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.