Solve the group by query problem after Mysql is upgraded to 5.7, mysql5.7

Solve the group by query problem after Mysql is upgraded to 5.7, mysql5.7

Problems Found

Recently, after upgrading mysql to mysql 5.7, when performing group by queries, for example

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

The following error is 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 that the mysql 5.7 mode is running. ONLY_FULL_GROUP_BY is enabled by default.

ONLY_FULL_GROUP_BY is a SQL _mode provided BY MySQL. This SQL _mode is used to check the legitimacy of SQL statement GROUP.

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_byThis statement indicates that this violates the mysql rule, only fully group by, that is, grouping before execution, according to the query field (select field) retrieve the group content, so all the queried fields should be in the group by condition. In one case, query fields that contain aggregate functions are not included in group by, just like the count (id) above ).

Later, we found that the Order by sorting condition field must also be in group by, and the sorting field is also retrieved from the grouping field. If you don't understand it, you can check it out.

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 run the SQL statement normally.

2. If ONLY_FULL_GROUP_BY is not used, the select field must all be in the group by condition (except for fields containing functions ). (This problem also occurs if order by is encountered. Similarly, the order by field must be in group ).

3. ExploitationANY_VALUE()The 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 preceding SQL statement can be written

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 3rd methods.

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.