Solve the group by query problem after upgrading Mysql to 5.7

Find the problem

After upgrading MySQL to MySQL 5.7 recently, when performing 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 a sql_mode provided by MySQL, which is used to check the validity of the GROUP BY clause of the SQL statement.

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

This is incompatible this is incompatible with sql_mode=only_full_group_by the MySQL rule, only fully group by, that is, grouping first during execution, and then taking out the fields in the group according to the queried fields (selected fields), so all the queried fields should be in the group by grouping conditions; there is one exception, if the queried fields contain aggregate functions, they do not need to be included in the group by, just like count(id) above.

Later I found that the fields of the Order by sorting condition must also be in the group by, and the sorting fields are also taken from the grouping fields. If you don’t understand, you can go and take a look.

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

Remove ONLY_FULL_GROUP_BY to execute SQL normally.

2. If ONLY_FULL_GROUP_BY is not selected, the select fields must all be included in the group by conditions (except for fields containing functions). (If this problem also occurs when you encounter order by, similarly, the order by fields must also be in group by).

3. Use ANY_VALUE() 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 above SQL statement 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

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.