关于 mysql 5.7 默认开启的新特性 sql_mode=only_full_group_by 有一些无法理解的点

下午在写三表联查的 SQL，遇到

Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'Test.Goods_Status.is_send_msg' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这样的问题，然后调查了一下，好像是 MySQL5.7 引入的 only_full_group_by 特性导致的，网上给出的大部分解释是 select 中的属性需要包含在 group by 中，即：

Group By 正确的用法
myql 的 group by 语法为： 
select 选取分组中的列 + 聚合函数 from 表名称 group by 分组的列 
从语法格式来看，是先有分组，再确定检索的列，检索的列只能是参加分组了的列。 
所以问题中，group by 后的 a,b,c 是先确定的，而 select 后面的字段是可变的 
正确的语句
select a,b,c from table_name group by a,b,c,d;
select a,b from table_name group by a,b,c;
select a,max(a) from table_name group by a,b,c;
以下是错误的
select a,b,c from table_name group by a,b;
select a,b,c from table_name group by a;

我这边原先出错的 SQL 是这样的：

SELECT Goods_Information.goods_id,
       Goods_Information.express_number,
       phone,
       container_number,
       pick_up_code,
       (CASE WHEN is_send_msg= '0'
   AND is_pick_up= '0' THEN '0' WHEN is_send_msg= '1'
   AND is_pick_up= '0' THEN '1' END) AS status,
         MAX(operation_time)
  FROM Goods_Information
  LEFT JOIN Goods_Status ON Goods_Information.goods_id= Goods_Status.goods_id
  LEFT JOIN Operation_record ON Goods_Information.goods_id= Operation_record.goods_id
 WHERE is_pick_up= '0'
 GROUP BY Goods_Information.goods_id
 ORDER BY Goods_Information.goods_id

然后我自己看了一下报错，半蒙半猜的试着改成了这样：

SELECT Goods_Information.goods_id,
       Goods_Information.express_number,
       phone,
       container_number,
       pick_up_code,
       (CASE WHEN is_send_msg= '0'
   AND is_pick_up= '0' THEN '0' WHEN is_send_msg= '1'
   AND is_pick_up= '0' THEN '1' END) AS status,
         MAX(operation_time)
  FROM Goods_Information
  LEFT JOIN Goods_Status ON Goods_Information.goods_id= Goods_Status.goods_id
  LEFT JOIN Operation_record ON Goods_Information.goods_id= Operation_record.goods_id
 WHERE is_pick_up= '0'
 GROUP BY Goods_Information.goods_id,
         is_send_msg
 ORDER BY Goods_Information.goods_id

即在 GROUP BY 中加入了 is_send_msg 这个属性，然后就能正常工作了，但是这跟网上对这个 only_full_group_by 模式的解释不一样啊，有一等蒙了。

另外将 select 中有关 is_send_msg 的属性都删去也能查出结果(猜测：select 主表中的属性，使用 group by 时不需要将属性添加进 group by 中？)，也不符合上面说的 select 中的属性需要全部包含在 group by 中的原则啊。

is_pick_up 属性和 is_send_msg 是在一张表上的，按理说这 2 个属性地位应该是一样的啊，但是因为 is_pick_up 在 where 语句被使用了，所以不需要进入 groupBy 了？实际上去掉 Where 后 is_pick_up 这个属性也被提示同样的错误了。Select 中其他几项属性是主表中的，所以不需要加入 group By ？所以这个 only_full_group_by 到底是个什么模式呢？有人说跟 oracle group by 是一样的，但是测试下来不一样啊？希望有大佬帮忙解释一下。

官方的说明好像也没提到具体情况，但是给了一个 ANY_VALUE() 函数来在不关闭这个模式的情况下，使得 SQL 正常工作（也可能是我英语太渣没看懂，学渣自卑）： https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

chitanda

2019-04-30 22:35:00 +08:00

@Wolfsin 之前只看了错误，刚才仔细看了下你的 sql 语句，是这样，select 里面，非 group_by 的字段，你应该确保他们是唯一的，至于为什么你自己思考。如果你确定要选取非唯一的字段，有两个办法：

1 ）写个子查询，比如 select t.id, t.any_thing ,tmp_t.cnt from t join (select id ,count(name) as cnt from t group by id) as tmp_t on t.id=tmp_t.id;

2) 用 window function，select first_name, left(first_name, 1),count(*) over(partition by left(first_name, 1)) from people;参考： https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/ 不过，mysql 这个特性要 8.0 以上才行，Mariadb 什么的好像随便用，可惜了被 Oracle 糟蹋的 MySQL。

我觉得 only_full_group_by 起到的是一个保护作用，至于你说的 ANY_VALUE()，最好不要用，官方说：It simply acts to suppress the test for nondeterminism.

最后，你应该好好去理解并思考下下 group by 的工作方式，已经 sql 语句的工作方式。
官方文档其实已经写了：The query is invalid if name is not a primary key of t or a unique NOT NULL column. In this case, no functional dependency can be inferred and an error occurs:

在附一个介绍 sql 执行顺序的链接，特别有用，我现在记不清都经常翻

Wolfsin

2019-05-01 00:30:32 +08:00

@chitanda #7 您好，谢谢您的回复，那个我先问一下您回复中的唯一是什么意思，因为感觉有歧义，是指

id=1 name=1

这样的一个 id 对应的 name 值唯一，不存在一个 id 对应多个 name，如：

id=1 name=1
id=1 name=2

这种情况，还是指 name 这个字段被 unique 约束，在此基础上，我对您说的“ select 里面，非 group_by 的字段，你应该确保他们是唯一的”这一句话，感到有点无法理解，因为 select 中非 group by 的字段是否 unique 约束好像并不影响结果，如果你说的是 group by 的字段需要 unique，那我是明白您想表达的意思的。

在我的 SQL 中虽然的确筛选了非唯一的字段，但是因为这个字段是跟主键一一绑定（ 1：1 的关系）的（我不太清楚我描述的有没有问题即 1 个 is_send_msg 跟 1 个 id 对应，一个 id 也只能有 1 个 is_send_msg 字段，不存在一个 id 有 2 个 is_send_msg 的值），所以感觉子查询好像有一点多此一举？实际看你给我写的例子，id 可以有多个 name （我是从 count 这个函数中推断出来的，如果说错了请见谅，因为如果 id 和 name 一一对应，count （ name ）的结果应该一直为“ 1 ”）

我会在 APPEND 中补充我 3 张表的数据，如果您方便的话，可以帮忙结合着数据看一看我的 SQL 写的有什么问题吗？

Wolfsin

2019-05-01 01:15:12 +08:00

当然以是否为主键判断，在多表查询中可能不适用，因为 group by a.id 中的 id 字段，在 a 表中是主键，但是在 b 表中只是外键，外键是可以不唯一的，就会产生一对多的情况，导致数据的丢失。而实际，如果 2 张互相关联的表的主键是相同的一个字段，完全就可以把这 2 张表整合成一张表，而不是像我一样故意设计成 2 张表。
如果我这样理解没有问题，那我应该是明白这个 only_full_group_by 的意思了，而导致我 SQL 报错的原因只是数据库在执行前不知道我的数据对应关系，导致这种特定情况下正确的 SQL 被认为存在问题（按我理解，这个报错等级应该归在 warning 而非 error ），但是 SQL 是不像 Java 这类语言一样即使 Warning 也可以正常运行，这大概也是官方给出 ANY_VALUE()这个函数的理由吧。
@Wolfsin #13