MySQL的ONLY_FULL_GROUP_BY模式

目前的新项目都是基于mysql最新版本,5.7.17,在实际项目应用过程中也遇到了很多的改变,很多问题都集中在mysql的设置模式上边,今天遇到的问题是ONLY_FULL_GROUP_BY约定

项目中之前习惯的sql语句出现了如下报错
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yunmao.case.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

为了开发更加标准化,没有去调整mysql的模式,只想找到解决的方案,查看mysql官方手册中有这么一段说明

ONLY_FULL_GROUP_BY

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.

from https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

那么如果在不是主键或唯一索引group by的情况要列出其它列怎么办呢?
手册引导在 https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
解决办法:
If you know that, for a given data set, each name value in fact uniquely determines the address value, address is effectively functionally dependent on name. To tell MySQL to accept the query, you can use the ANY_VALUE() function:
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

ANY_VALUE函数说明
https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

Do it!

发表评论

电子邮件地址不会被公开。 必填项已用*标注