Share the solution to the group by query problem in Mysql5.7

This article mainly introduces to you the solution to the group by query problem encountered after upgrading Mysql to 5.7. The article introduces it in detail through the example code, which is a certain reference for friends who also encounter this problem. The value of learning, friends who need it, please follow the editor to learn together.

Found the problem

Recently after upgrading mysql to mysql 5.7, 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

will report the following error:

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 it 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. This sql_mode is used to check the validity of the SQL statement 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 This sentence reminds me that this violates the rules of MySQL, only fully group by, which means that it is grouped first during execution, and the content of the group is taken out according to the query field (select field), so all the query fields should be Within the group by grouping condition; one exception is that if the query field contains an aggregate function, it does not need to be included in the group by, just like my count (id) above.

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

Solution:

1.set@@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_pISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;

Remove ONLY_FULL_GROUP_BY to execute sql normally.

2. Without ONLY_FULL_GROUP_BY, all select fields must be within the group by grouping condition (except fields containing functions). (This problem also occurs if you encounter order by. In the same way, the order by field must also be in group by).

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.

For example, 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 The 3rd method was chosen.

Summarize

The above is the detailed content of Share the solution to the group by query problem in Mysql5.7. For more information, please follow other related articles on the PHP Chinese website!