logo标签列表

MySQL是否违反标准，允许选择不在GROUP BY子句中的列？

mysqlgroup-bysql-standard

我习惯使用微软技术，包括SQL Server。今天我遇到了一个问答，其中引用了MySQL文档中的以下段落：

标准SQL会拒绝你的查询，因为在聚合查询中，你不能选择不在GROUP BY子句中的非聚合字段。MySQL扩展了GROUP BY的使用方式，使得选择列表可以引用未在GROUP BY子句中命名的非聚合列。这意味着在MySQL中，前面的查询是合法的。你可以利用这个特性来提高性能，避免不必要的列排序和分组。然而，这主要在每个非聚合列的所有值对于每个组都相同时才有用。服务器可以自由选择每个组中的任何值，所以除非它们相同，否则选择的值是不确定的。

MySQL是否违反了标准？它是如何做到的？允许这样做的结果是什么？

enter image description here

这个 Stack Overflow 的 问题和回答 被导入以保留其内容，以防止 关闭和删除。 - Paul White
3个回答
标准SQL会拒绝你的查询，因为在聚合查询中，你不能选择那些既不是聚合字段又不在GROUP BY子句中的非聚合字段。 这个说法在1992年之前是正确的。 但从2003年开始，这个说法显然是错误的。 根据SQL-2003标准，来自http://www.wiscorp.com/的6IWD6-02-Foundation-2011-01.pdf第398页的第7.12段（查询规范）： 17. 如果T是一个分组表，则让G成为T的分组列集合。在每个((select list))中的((value expression))中，引用T的列的每个列引用都应该引用一些对G有函数依赖关系的列C，或者应该包含在聚合参数的((set function specification))中，其聚合查询是QS。
现在，MYSQL已经通过允许不仅仅是与分组列功能相关的列，而是允许所有列来实现了这个功能。这给那些不理解分组工作原理并且得到意外结果的用户带来了一些问题。 但你说得对，MySQL添加了一个与SQL标准冲突的功能（尽管你似乎认为原因不对）。这并不完全准确，因为他们添加了一个符合SQL标准的功能，但并不是最好的方式（更像是简单的方式），但它确实与最新的标准冲突。 回答你的问题，MySQL添加这个功能（扩展）的原因，我想是为了符合最新的SQL标准（2003+）。为什么他们选择以这种方式实现（不完全符合），我们只能推测。 正如@Quassnoi和@Johan用例子回答的那样，这主要是一个性能和可维护性问题。但是很难让关系数据库管理系统变得足够聪明（排除Skynet），能够识别功能相关的列，所以MySQL开发人员做出了选择： 我们（MySQL）为您（MySQL用户）提供了这个符合SQL-2003标准的功能。它可以提高某些GROUP BY查询的速度，但有一个限制。您必须小心（而不是SQL引擎），以确保SELECT和HAVING列表中的列在功能上依赖于GROUP BY列。如果不是这样，可能会得到不确定的结果。 如果您想禁用它，可以将sql_mode设置为ONLY_FULL_GROUP_BY。

这一切都在MySQL文档：GROUP BY扩展（5.5）中 - 尽管不是上面的措辞，而是像您的引用一样（他们甚至忘记提到它是从标准SQL-2003偏离而不是标准SQL-92）。我认为这种选择在所有软件中都很常见，包括其他RDBMS。它们是为了性能、向后兼容性和许多其他原因而做出的。例如，Oracle有著名的''与NULL相同，而SQL-Server可能也有一些。

还有彼得·鲍曼（Peter Bouman）的博客文章，其中捍卫了MySQL开发人员的选择：揭穿GROUP BY的神话。

在2011年，正如@Mark Byers在DBA.SE的相关问题的评论中告诉我们的那样，PostgreSQL 9.1添加了一个新功能（发布日期：2011年9月）专门为此目的设计。它比MySQL的实现更加严格，并更接近标准。 后来，在2015年，MySQL宣布在5.7版本中改进了行为，以符合标准，并且实际上能够识别功能依赖关系（甚至比Postgres的实现更好）。文档：MySQL处理GROUP BY（5.7）和Peter Bouman的另一篇博客文章：MySQL 5.7.5：GROUP BY尊重功能依赖关系！

original author

简短回答
这是一个速度优化技巧

默认情况下启用了该设置，但可以通过此设置禁用：https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

详细回答 非标准的简写group by子句之所以存在是为了提高速度。
MySQL允许程序员确定所选字段是否在group by子句上具有功能依赖性。
数据库不进行任何测试，只是选择找到的第一个结果作为字段的值。
这样可以大大提高速度。

考虑以下代码：

SELECT f1, f2, f3, f4 FROM t1 GROUP BY f2   
-- invalid in most SQL flavors, valid in MySQL
MySQL只会选择它找到的第一个值，花费最少的时间。
f1、f3、f4将来自同一行，但如果涉及多个表和连接，这种关系将会破裂。 为了在SQL Server中做类似的事情，你需要做一些操作。 
SELECT MIN(f1), f2, MIN(f3), MIN(f4) FROM t1 GROUP BY f2  
-- valid SQL, but really a hack

数据库现在必须检查所有的结果来找到最小值，费力地忙碌着。
f1、f3、f4很可能彼此之间没有关联，并且不会来自同一行。

然而，如果你这样做：

SELECT id as `primary_key`, count(*) as rowcount, count(f2) as f2count, f2, f3, f4 
FROM t1 
GROUP BY id

其余的字段都将在id上具有功能依赖关系。
Rowcount始终为1，f2count要么为0（如果f2为空），要么为1。

在连接操作中，涉及许多表的情况下，如下所示：

例如：

网站1 -> n 主题1 -> n 线程1 -> n 帖子1 -> 1 个人。

如果你进行一个涉及所有表格的复杂查询，并且只对posts.id进行GROUP BY，那么很明显，所有其他字段都对posts.id具有功能依赖关系（而且仅对posts.id）。
因此，在GROUP BY子句中列出更多字段或者强制使用聚合函数是没有意义的。
为了加快速度，MySQL不会强制你这样做。

但是，你需要理解功能依赖的概念以及表中的关系和你编写的连接，这给程序员带来了一定的负担。
然而，使用：

SELECT 
  posts.id, MIN(posts.f2)
  ,MIN(threads.id), min(threads.other)
  ,MIN(topics.id), ....
  ,MIN(website.id), .....
  ,MIN(Person.id), ...
FROM posts p
INNER JOIN threads t on (p.thread_id = t.id)
INNER JOIN topic to on (t.topic_id = to.id)
INNER JOIN website w ON (w.id = to.website_id)
INNER JOIN person pe ON (pe.id = p.person_id)
GROUP BY posts.id   //NEVER MIND THE SYNTAX ERROR WITH THE ALIASES

对程序员来说，完全给予了相同的心理负担。

original author

MySQL允许这样做，这是否违反了标准？如何违反的？它允许你编写这样的查询： 
SELECT  a.*, COUNT(*)
FROM    a
JOIN    b
ON      b.a = a.id
GROUP BY
        a.id
其他系统需要您将所有列从a添加到GROUP BY列表中，这会使查询变得更大、更难维护和效率更低。 在这种形式下（通过PK进行分组），这并不违反标准，因为a中的每一列都与其主键有函数依赖关系。 然而，MySQL实际上并不检查函数依赖性，并允许您选择与分组集无函数依赖关系的列。这可能导致不确定的结果，不能依赖它。唯一可以保证的是列值属于共享分组表达式的某些记录（甚至不属于一条记录！）。 通过将sql_mode设置为ONLY_FULL_GROUP_BY来禁用此行为。 MySQL 5.7.5（MariaDB 10.1.18）已更改以符合标准。

original author