mysql报错Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column问题解决

报错信息:

Failed to prepare:SELECT tag_id,count(*) as num FROM articles_tag WHERE status=1 GROUP BY tag_id ORDER BY num DESC,id DESC LIMIT 10
Array ( [0] => 42000 [1] => 1055 [2] => Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'web_db.articles_tag.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by )

mysql语句:
SELECT tag_id,count(*) as num FROM articles_tag WHERE status=1 GROUP BY tag_id ORDER BY num DESC,id DESC LIMIT 10

问题分析
在mysql中,用group by 分组时 报错 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'web_db.articles_tag.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by,这个主要是违背了mysql开启的校验 only_full_group_by 检测。

这个问题主要是mysql 开启了only_full_group_by ,同时 SELECT 语句不够标准。对于 GROUP BY 聚合操作,如果在 SELECT或者ORDER 中的列,没有在 GROUP BY 中出现,那么这个 SQL 是不合法的,因为列不在 GROUP BY 句中,所以对于设置了这个 mode 的数据库,在使用 GROUP BY 的时候,就要用 MAX(),SUM(),ANT_VALUE() 这种聚合函数,才能完成 GROUP BY 的聚合操作。

only_full_group_by 说白了就是要求,select 的列要全部来自于group by 中的列,不能 select 之外的列。 这个主要是mysql版本,5.7之前是默认关闭的,5.7之后的高版本都是自动打开 only_full_group_by 校验检测的。

规则核心原则如下,没有遵循原则的sql会被认为是不合法的sql
1. order by后面的列必须是在select后面存在的
2. select、having或order by后面存在的非聚合列必须全部在group by中存在

解决问题

解决方法1:临时关闭 only_full_group_by 检测(这个最粗暴,最简单,但是要重启mysql服务)

查看 sql_mode 属性:
查看 Session级:SELECT @@sql_mode;
查看 GLOBAL级:SELECT @@GLOBAL.sql_mode;

修改 Session级:
set @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
或者
修改 GLOBAL级:
set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

解决方法2:永久关闭 only_full_group_by 检测

方法1只是临时修改,在mysql重启后,还是会有ONLY_FULL_GROUP_BY;还可以选择永久关闭 only_full_group_by 检测。

my.cnf 配置
[mysqld][mysql] 下添加如下设置:
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'

重启服务:service mysqld restart

解决方法3:修改优化sql语句

比如
SELECT tag_id,count(*) as num FROM articles_tag WHERE status=1 GROUP BY tag_id ORDER BY num DESC,id DESC LIMIT 10
修改为
SELECT tag_id,count(*) as num FROM articles_tag WHERE status=1 GROUP BY tag_id ORDER BY num DESC LIMIT 10

解决方法4:使用 ANY_VALUE() 抑制 ONLY_FULL_GROUP_BY 的影响。

any_value() 函数,这个就是用来解决 only_full_group_by 问题的。即:将多出来的字段使用这个函数就好了。

如果不想关闭mysql的ONLY_FULL_GROUP_BY全局设置,仅仅想让当前sql忽略其影响,则可以使用 ANY_VALUE(cloum) 忽略ONLY_FULL_GROUP_BY的影响,详见官方文档!
mysql8.0官网:ANY_VALUE(cloum) 可以抑制 ONLY_FULL_GROUP_BY 的影响:https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html

例如:
SELECT * FROM articles GROUP BY type_id;
改为
SELECT any_value(id) AS id,any_value(name) AS name,type_id FROM articles GROUP BY type_id;

done!

出处:www.l1mn.com

原文标题:mysql报错Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column问题解决

原文地址:https://www.l1mn.com/p/2qux14.html

本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接。

评论

皖ICP备2023023451号

Copyright © L1MN.COM 联系方式:l1mnfw@163.com