Mysql升级到5.7后遇到的group by查询问题解决
发现问题
最近在将mysql升级到mysql5.7后，进行一些groupby查询时，比如下面的
SELECT*,count(id)ascountFROM`news`GROUPBY`group_id`ORDERBY`inputtime`DESCLIMIT20
就会报如下错误：
SELECTlistisnotinGROUPBYclauseandcontainsnonaggregatedcolumn‘news.id'whichisnotfunctionallydependentoncolumnsinGROUPBYclause;thisisincompatiblewithsql_mode=only_full_group_by.
原因分析
原因是mysql5.7模式中。默认启用了ONLY_FULL_GROUP_BY。
ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode，通过这个sql_mode来提供SQL语句GROUPBY合法性的检查。
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
thisisincompatiblewithsql_mode=only_full_group_by这句话提示了这违背了mysql的规则，onlyfullygroupby，也就是说在执行的时候先分组，根据查询的字段（select的字段）在分组的内容中取出，所以查询的字段全部都应该在groupby分组条件内；一种情况例外，查询字段中如果含有聚合函数的字段不用包含在groupby中，就像我上面的count（id）。
后来发现Orderby排序条件的字段也必须要在groupby内，排序的字段也是从分组的字段中取出。不明白的可以去看一下。
解决办法：
1.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';
去掉ONLY_FULL_GROUP_BY即可正常执行sql.
2.不去ONLY_FULL_GROUP_BY,时select字段必须都在groupby分组条件内（含有函数的字段除外）。（如果遇到orderby也出现这个问题，同理，orderby字段也都要在groupby内）。
3.利用ANY_VALUE()这个函数https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value
ThisfunctionisusefulforGROUPBYquerieswhentheONLY_FULL_GROUP_BYSQLmodeisenabled,forcaseswhenMySQLrejectsaquerythatyouknowisvalidforreasonsthatMySQLcannotdetermine.Thefunctionreturnvalueandtypearethesameasthereturnvalueandtypeofitsargument,butthefunctionresultisnotcheckedfortheONLY_FULL_GROUP_BYSQLmode.
如上面的sql语句可写成
SELECTANY_VALUE(id)asid,ANY_VALUE(uid)asuid,ANY_VALUE(username)asusername,ANY_VALUE(title)astitle,ANY_VALUE(author)asauthor,ANY_VALUE(thumb)asthumb,ANY_VALUE(description)asdescription,ANY_VALUE(content)ascontent,ANY_VALUE(linkurl)aslinkurl,ANY_VALUE(url)asurl,ANY_VALUE(group_id)asgroup_id,ANY_VALUE(inputtime)asinputtime,count(id)ascountFROM`news`GROUPBY`group_id`ORDERBYANY_VALUE(inputtime）DESCLIMIT20
我选用的是第３种方法。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。