Resolution of group by query problems encountered after Mysql upgrade to 5.7

Find a problem

Recently, after upgrading mysql to mysql 5.7, when making some group by queries, such as the following

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

The following error will be reported:

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.

Cause analysis

The reason is in mysql 5.7 mode. ONLY_FULL_GROUP_BY is enabled by default.

ONLY_FULL_GROUP_BY is an sql_mode provided by MySQL, through which the validity of SQL statement GROUP BY is checked.

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 This sentence suggests that this violates the rules of mysql, only fully group by, that is to say, it is grouped first when executing, and it is taken out in the grouping content according to the query fields (fields of select), so all the query fields should be within the grouping conditions of group by; One exception is that the query fields that contain aggregate functions do not need to be included in group by, like my count (id) above.

Later, it was found that the fields of Order by sorting conditions must also be in group by, and the sorted fields are also taken out from the grouped fields. If you don't understand, you can go and see it.

Solution:

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';

sql can be executed normally by removing ONLY_FULL_GROUP_BY.

2. If ONLY_FULL_GROUP_BY is not removed, the select fields must all be within the group by grouping condition (except for fields that contain functions). (If this problem also occurs in order by, the order by field should also be in group by).

STEP 3 Use ANY_VALUE() This function https://dev.mysql.com/doc/refman/5. 7/en/miscellaneous-functions.html # function_any-value

This function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine. The function return value and type are the same as the return value and type of its argument, but the function result is not checked for the ONLY_FULL_GROUP_BY SQL mode.

The sql statement above can be written as

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

I chose the third method.

Summarize