12.20.3 MySQL 对 GROUP BY 的处理
SQL-92 和更早版本不允许选择列 table,HAVING
条件或ORDER BY
列 table 引用未在GROUP BY
子句中命名的未聚合列的查询。例如,此查询在标准 SQL-92 中是非法的,因为选择列 table 中未聚合的name
列未出现在GROUP BY
中:
SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o, customers AS c
WHERE o.custid = c.custid
GROUP BY o.custid;
为了使查询在 SQL-92 中合法,必须从选择列 table 中省略name
列或在GROUP BY
子句中命名。
SQL:1999 和更高版本允许在功能上依赖于GROUP BY
列的每个非可选功能 T301 此类非聚合:如果name
和custid
之间存在这种关系,则查询合法。例如,custid
是customers
的主键就是这种情况。
MySQL 5.7.5 及更高版本实现对功能依赖性的检测。如果启用了ONLY_FULL_GROUP_BY SQL 模式(默认情况下是默认设置),则 MySQL 拒绝选择列 table,HAVING
条件或ORDER BY
列 table 引用未在GROUP BY
子句中命名且在功能上不依赖于它们的未聚合列的查询。 (在 5.7.5 之前,MySQL 不检测功能依赖关系,默认情况下未启用ONLY_FULL_GROUP_BY。有关 5.7.5 之前的行为的说明,请参见MySQL 5.6 参考手册。)
启用ONLY_FULL_GROUP_BY SQL 模式时,MySQL 5.7.5 和更高版本还允许在GROUP BY
子句中未命名的非聚合列,但前提是该列限于单个值,如以下示例所示:
mysql> CREATE TABLE mytable (
-> id INT UNSIGNED NOT NULL PRIMARY KEY,
-> a VARCHAR(10),
-> b INT
-> );
mysql> INSERT INTO mytable
-> VALUES (1, 'abc', 1000),
-> (2, 'abc', 2000),
-> (3, 'def', 4000);
mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a | SUM(b) |
+------+--------+
| abc | 3000 |
+------+--------+
使用ONLY_FULL_GROUP_BY时,也可能在SELECT列 table 中有多个非聚合列。在这种情况下,每个这样的列都必须限制为一个值,并且所有这样的限制条件必须由逻辑AND
联接,如下所示:
mysql> DROP TABLE IF EXISTS mytable;
mysql> CREATE TABLE mytable (
-> id INT UNSIGNED NOT NULL PRIMARY KEY,
-> a VARCHAR(10),
-> b VARCHAR(10),
-> c INT
-> );
mysql> INSERT INTO mytable
-> VALUES (1, 'abc', 'qrs', 1000),
-> (2, 'abc', 'tuv', 2000),
-> (3, 'def', 'qrs', 4000),
-> (4, 'def', 'tuv', 8000),
-> (5, 'abc', 'qrs', 16000),
-> (6, 'def', 'tuv', 32000);
mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------------+
| @@session.sql_mode |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+
mysql> SELECT a, b, SUM(c) FROM mytable
-> WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a | b | SUM(c) |
+------+------+--------+
| abc | qrs | 17000 |
+------+------+--------+
如果禁用了ONLY_FULL_GROUP_BY,则对标准 SQL 使用GROUP BY
的 MySQL 扩展允许选择列 table,HAVING
条件或ORDER BY
列 table 引用未聚合的列,即使这些列在功能上不依赖于GROUP BY
列也是如此。这使 MySQL 接受前面的查询。在这种情况下,服务器可以从每个组中自由选择任何值,因此,除非它们相同,否则选择的值是不确定的,这可能不是您想要的。此外,通过添加ORDER BY
子句不能影响每个组中值的选择。选择值之后,将进行结果集排序,并且ORDER BY
不会影响服务器在每个组中选择哪个值。禁用ONLY_FULL_GROUP_BY主要是有用的,因为您知道由于数据的某些属性,每个未聚合的列中在GROUP BY
中未命名的所有值对于每个组都是相同的。
通过使用ANY_VALUE()引用未聚合的列,您可以在不禁用ONLY_FULL_GROUP_BY的情况下实现相同的效果。
下面的讨论演示了功能依赖性,不存在功能依赖性时 MySQL 产生的错误消息,以及在没有功能依赖性的情况下使 MySQL 接受查询的方法。
启用ONLY_FULL_GROUP_BY时,此查询可能无效,因为在GROUP BY
子句中未命名选择列 table 中未聚合的address
列:
SELECT name, address, MAX(age) FROM t GROUP BY name;
如果name
是t
的主键或唯一的NOT NULL
列,则该查询有效。在这种情况下,MySQL 会认识到所选列在功能上取决于分组列。例如,如果name
是主键,则其值确定address
的值,因为每个组只有一个主键值,因此只有一行。结果,在一个组中address
值的选择中没有随机性,也不需要拒绝查询。
如果name
不是t
的主键或唯一的NOT NULL
列,则查询无效。在这种情况下,无法推断出功能依赖性,并且会发生错误:
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by
如果您知道,对于给定的数据集,每个name
值实际上唯一地确定address
值,则address
实际上在功能上取决于name
。要告诉 MySQL 接受查询,可以使用ANY_VALUE()函数:
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
或者,禁用ONLY_FULL_GROUP_BY。
但是,前面的示例非常简单。特别是,不太可能将您分组在一个主键列上,因为每个组将仅包含一行。有关在更复杂的查询中显示功能依赖关系的其他示例,请参见第 12.20.4 节“功能依赖的检测”。
如果查询具有聚合函数且没有GROUP BY
子句,则在启用了ONLY_FULL_GROUP_BY的选择列 table,HAVING
条件或ORDER BY
列 table 中,不能有未聚合的列:
mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by
如果没有GROUP BY
,则只有一个组,并且不确定为该组选择哪个name
值。如果 mysql 选择的name
值无关紧要,也可以在这里使用ANY_VALUE():
SELECT ANY_VALUE(name), MAX(age) FROM t;
在 MySQL 5.7.5 和更高版本中,ONLY_FULL_GROUP_BY
还影响对使用DISTINCT
和ORDER BY
的查询的处理。考虑具有包含以下行的三列c1
,c2
和c3
的 tablet
的情况:
c1 c2 c3
1 2 A
3 4 B
1 2 C
假设我们执行以下查询,并期望结果按c3
排序:
SELECT DISTINCT c1, c2 FROM t ORDER BY c3;
为了 Order 结果,必须首先消除重复。但是要这样做,我们应该保留第一行还是第三行?此任意选择会影响c3
的保留值,而保留值c3
又会影响 Sequences 并使其具有任意性。为避免此问题,如果任何ORDER BY
table 达式不满足以下条件之一,则将具有DISTINCT
和ORDER BY
的查询拒绝为无效:
-
该 table 达式等于选择列 table 中的一个
-
table 达式引用的并属于查询的所选 table 的所有列都是选择列 table 的元素
标准 SQL 的另一个 MySQL 扩展允许HAVING
子句中的引用引用选择列 table 中的别名 table 达式。例如,以下查询返回name
值,它们仅在 tableorders
中出现一次:
SELECT name, COUNT(name) FROM orders
GROUP BY name
HAVING COUNT(name) = 1;
MySQL 扩展允许在HAVING
子句中为聚合列使用别名:
SELECT name, COUNT(name) AS c FROM orders
GROUP BY name
HAVING c = 1;
Note
在 MySQL 5.7.5 之前,启用ONLY_FULL_GROUP_BY会禁用此扩展,因此要求使用非别名 table 达式编写HAVING
子句。
标准 SQL 仅允许GROUP BY
子句中的列 table 达式,因此这样的语句是无效的,因为FLOOR(value/100)
是非列 table 达式:
SELECT id, FLOOR(value/100)
FROM tbl_name
GROUP BY id, FLOOR(value/100);
MySQL 扩展了标准 SQL,以允许GROUP BY
子句中的非列 table 达式,并认为前面的语句有效。
标准 SQL 也不允许GROUP BY
子句中的别名。 MySQL 扩展了标准 SQL 以允许使用别名,因此另一种编写查询的方法如下:
SELECT id, FLOOR(value/100) AS val
FROM tbl_name
GROUP BY id, val;
别名val
在GROUP BY
子句中被视为列 table 达式。
GROUP BY
子句中存在非列 table 达式时,MySQL 会识别该 table 达式与选择列 table 中的 table 达式之间的相等性。这意味着在启用ONLY_FULL_GROUP_BY SQL 模式的情况下,包含GROUP BY id, FLOOR(value/100)
的查询有效,因为相同的FLOOR()table 达式出现在选择列 table 中。但是,MySQL 不会尝试识别对GROUP BY
非列 table 达式的函数依赖性,因此,即使第三个选定的 table 达式是id
列和GROUP BY
子句中FLOOR()table 达式的简单公式,以下查询在启用ONLY_FULL_GROUP_BY的情况下也是无效的:
SELECT id, FLOOR(value/100), id+FLOOR(value/100)
FROM tbl_name
GROUP BY id, FLOOR(value/100);
解决方法是使用派生 table:
SELECT id, F, id+F
FROM
(SELECT id, FLOOR(value/100) AS F
FROM tbl_name
GROUP BY id, FLOOR(value/100)) AS dt;