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

[Wolfsin]

下午在写三表联查的 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

[JRay]

昨天还因为这个被喷了

[wowo243]

所以为啥不把数据库的这个配置去掉

[chitanda]

看错误，我觉得大概率是你 case 和 group by 的顺序问题，你试试去掉 case

[Wolfsin]

@chitanda #3 去掉 case 后就正常了，但是单加 is_send_msg 属性，会报：
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
本质是 is_send_msg 和 is_pick_up 这 2 个不在主表中的属性和 only_full_group_by 模式导致的问题。
@wowo243 #2 去掉这个配置当然是一个解决方案，但这只是在逃避这个问题吧，现在可以去掉配置解决，但是将来遇到不能去掉配置的情况下，不还是要面对吗？
@JRay #1 因为以前的写好的 SQL 用不了吗 2333，我最开始遇到的时候是避免在 SQL 中用 Group By 来躲过去的，但是今天实在是不写 Group By 查不到要的数据了

[alcarl]

sql 就该认真写，不好好写经常会出奇怪的结果，但是能用。。。。。。很可怕的

[Wolfsin]

@alcarl #5 我也是这么想的，所以才发帖问问这个究竟是怎么回事，现在网上给出的解决方案都是去配置里面关闭 only_full_group_by 模式

[chitanda]

@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 执行顺序的链接，特别有用，我现在记不清都经常翻

[chitanda]

@Wolfsin 我就说这错误怎么好像在哪里见过-_-

[Wolfsin]

@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 写的有什么问题吗？

[liprais]

你有一张表是这样的:
t:
a,b,c
1,2,3
1,3,4
现在你写个 sql 是这样的:
select a,b,max(c) from t group by a
数据库是不知道那个 b 才是你想要的
所以你必须写成
select a,b,max(c) from t group by a,b
或者
select a,max(c) from
(
select a,c from test
) dt group by a

[Wolfsin]

@liprais #10 我明白这个情况下的 group by，Mysql 低版本能给出结果是不合理的，这篇文章已经替我解答了这个疑问
https://blog.csdn.net/liufei198613/article/details/82979034
所以我想明确上面那位朋友说的唯一性的定义，实际在我这边，a 和 b 是一对一的关系，即不存在一个 a 对应多个 b 的情况，即您上面举例的情况在我的表中并不存在。

[liprais]

@Wolfsin 数据库在实际执行前并不知道表里面的数据是啥关系

[Wolfsin]

@liprais #12 那么其实数据库也并不知道 group by 的字段在数据库中究竟是什么定位对吧，因为只要 group by 中的字段是主键，就会产生我说的情况，即数据并不会因为 group by 而丢失，这个情况下 select 中选取非唯一的字段是没有任何问题的。
而当 group by 非主键的情况，才应该考虑是不是会产生因为 group by 而导致的数据丢失，即 Mysq 这个 sql_mode=only_full_group_by 模式考虑到的场景。

[Wolfsin]

当然以是否为主键判断，在多表查询中可能不适用，因为 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

[chitanda]

@Wolfsin
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;

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;

Alternatively, disable ONLY_FULL_GROUP_BY.
来自文档。

去看下文档吧，我觉得文档讲的十分清晰了，就是你自己发的那个链接

[chitanda]

@Wolfsin 英语渣不渣无所谓，只要迈出那一步，以后就很好了

[Wolfsin]

@chitanda #16 嗯嗯，谢谢，我已经明白这个文档的意思了。