MySQL GROUP BY 行为 [英] MySQL GROUP BY behavior

问题描述

给定下表'foo'

ID | First Name | Last Name
----------------------------
67   John        Smith
----------------------------
67   Bill        Jacobs

以下查询将返回什么 first_name 和 last_name 以及为什么?

What first_name and last_name will the following query return and why?

SELECT * FROM foo WHERE ID = 67 GROUP BY ID

推荐答案

MySQL 任意选择一行.在实践中，常用的 MySQL 存储引擎返回组中第一行的值，相对于物理存储.

MySQL chooses a row arbitrarily. In practice, commonly used MySQL storage engines return the values from the first row in the group, with respect to the physical storage.

create table foo (id serial primary key, category varchar(10));

insert into foo (category) values 
  ('foo'), ('foo'), ('foo'), ('bar'), ('bar'), ('bar');

select * from foo group by category;

+----+----------+
| id | category |
+----+----------+
|  4 | bar      |
|  1 | foo      |
+----+----------+

其他人认为 MySQL 允许您运行此查询是正确的，即使它具有任意且可能具有误导性的结果.SQL 标准和大多数其他 RDBMS 供应商都不允许这种含糊不清的 GROUP BY 查询.这称为单值规则:选择列表中的所有列必须明确属于 GROUP BY 条件的一部分，或者在聚合函数内，例如COUNT()、MAX() 等

Other folks are correct that MySQL allows you to run this query even though it has arbitrary and potentially misleading results. The SQL standard, and most other RDBMS vendors, disallow this kind of ambiguous GROUP BY query. This is called the Single-Value Rule: all columns in the select-list must be explicitly part of the GROUP BY criteria, or else inside an aggregate function, e.g. COUNT(), MAX(), etc.

MySQL 支持 SQL 模式 ONLY_FULL_GROUP_BY 如果您尝试运行违反 SQL 标准语义的查询，这会使 MySQL 返回错误.

MySQL supports a SQL mode ONLY_FULL_GROUP_BY that makes MySQL return an error if you try to run a query that violates SQL standard semantics.

AFAIK，SQLite 是唯一允许分组查询中有歧义列的其他 RDBMS.SQLite 返回组中最后行的值:

AFAIK, SQLite is the only other RDBMS that allows ambiguous columns in a grouped query. SQLite returns values from the last row in the group:

select * from foo group by category;

6|bar
3|foo

<小时>

我们可以想象那些不会有歧义但仍然违反 SQL 标准语义的查询.

---

We can imagine queries that would not be ambiguous, yet still violate the SQL standard semantics.

SELECT foo.*, parent_of_foo.* 
FROM foo JOIN parent_of_foo 
  ON (foo.parent_id = parent_of_foo.parent_id) 
GROUP BY foo_id;

没有合乎逻辑的方法会产生模棱两可的结果.foo 中的每一行都有自己的组，如果我们 GROUP BY foo 的主键.所以来自 foo 的任何列在组中只能有一个值.如果组是由 foo 的主键定义的，那么即使连接到由 foo 中的外键引用的另一个表，每个组也只能有一个值.

There's no logical way this could produce ambiguous results. Each row in foo gets its own group, if we GROUP BY the primary key of foo. So any column from foo can have only one value in the group. Even joining to another table referenced by a foreign key in foo can have only one value per group, if the groups are defined by the primary key of foo.

MySQL 和 SQLite 相信您可以设计逻辑上明确的查询.正式地，选择列表中的每一列都必须是 GROUP BY 条件中的列的功能依赖.如果你不遵守这一点，那是你的错.:-)

MySQL and SQLite trust you to design logically unambiguous queries. Formally, every column in the select-list must be a functional dependency of the columns in the GROUP BY criteria. If you don't adhere to this, it's your fault. :-)

标准 SQL 更加严格并且不允许一些可能明确的查询——可能是因为它对于 RDBMS 来说太复杂而无法确定.

Standard SQL is more strict and disallows some queries that could be unambiguous--probably because it would be too complex for the RDBMS to be sure in general.

这篇关于MySQL GROUP BY 行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！