Mysql 升级到5.7 后group by 报错

Mysql 升级到 5.7 后 group by 问题

升级到mysql 5.7后,但进行一些group by 查询时,比如下面的

SELECT *, count(id) as count FROM news GROUP BY group_id ORDER BY inputtime DESC LIMIT 20

就会报如下错误:

SELECT list is not in GROUP BY clause and contains nonaggregated column ‘news.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.

原因是mysql 5.7 模式中。默认启用了ONLY_FULL_GROUP_BY。

ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode,通过这个sql_mode来提供SQL语句GROUP BY合法性的检查。

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

this is incompatible with sql_mode=only_full_group_by这句话提示了这违背了mysql的规则,only fully group by,也就是说在执行的时候先分组,根据查询的字段(select的字段)在分组的内容中取出,所以查询的字段全部都应该在group by分组条件内;一种情况例外,查询字段中如果含有聚合函数的字段不用包含在group by中,就像我上面的count(id)。
​ 后来发现Order by排序条件的字段也必须要在group by内,排序的字段也是从分组的字段中取出。 不明白的可以去看一下。

解决办法:

  1. 去掉ONLY_FULL_GROUP_BY即可正常执行sql.
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’;
  1. 不去ONLY_FULL_GROUP_BY

    select字段必须都在group by分组条件内(含有函数的字段除外)。(如果遇到order by也出现这个问题,同理,order by字段也都要在group by内)。
    

3.利用ANY_VALUE()这个函数 


https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

如上面的sql语句可写成

SELECT ANY_VALUE(id)as id,ANY_VALUE(uid) as uid ,ANY_VALUE(username) as username,ANY_VALUE(title) as title,ANY_VALUE(author) as author,ANY_VALUE(thumb) as thumb,ANY_VALUE(description) as description,ANY_VALUE(content) as content,ANY_VALUE(linkurl) as linkurl,ANY_VALUE(url) as url,ANY_VALUE(group_id) as group_id,ANY_VALUE(inputtime) as inputtime, count(id) as count FROM `news` GROUP BY `group_id` ORDER BY ANY_VALUE(inputtime) DESC LIMIT 20

此处评论已关闭