SQL92及更早版本不允许选择列表,HAVING
条件或ORDER BY
列表引用在GROUP BY
子句中未命名的非分组列的查询。例如,在标准SQL92中,此查询是非法的,因为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;
要使SQL92中的查询合法,该name
列必须从选择列表中省略或在GROUP BY
子句中命名 。
SQL99后来允许每可选功能,T301等nonaggregates如果它们在功能上是依赖于GROUP BY
列:如果之间存在这样的关系 name
,并custid
,查询是合法的。这是情况,例如,是custid
主要的关键 customers
。
MySQL 5.7.5及以上功能依赖检测功能。如果ONLY_FULL_GROUP_BY
启用了 SQL模式(默认情况下),则MySQL将拒绝对该选项列表,HAVING
条件或 ORDER BY
列表引用的子集中既不指定的GROUP BY
非集合列的查询,也不在功能上依赖于它们。(5.7.5之前,MySQL没有检测到功能依赖关系, ONLY_FULL_GROUP_BY
默认情况下不启用)。有关5.7.5之前的行为的描述,请参见“ MySQL 5.6参考手册”。)
如果ONLY_FULL_GROUP_BY
禁用,则标准SQL使用的MySQL扩展 GROUP BY
允许选择列表, HAVING
条件或ORDER BY
列表引用非集合列,即使列在功能上不依赖于GROUP BY
列。这导致MySQL接受前面的查询。在这种情况下,服务器可以自由选择每个组中的任何值,因此除非它们相同,所选择的值是不确定的,这可能不是您想要的。此外,每个组的值的选择不能通过添加一个ORDER BY
子句来影响。结果集排序发生在选择值之后,ORDER BY
不影响服务器选择的每个组中的哪个值。禁用 ONLY_FULL_GROUP_BY
主要在您知道由于数据的某些属性而导致每个未命名的GROUP BY
每个非分组列中的每个组的值相同时,都是有用的 。
您可以实现相同的效果,而不会 ONLY_FULL_GROUP_BY
通过使用 ANY_VALUE()
来引用非分组列来实现。
以下讨论说明功能依赖性,MySQL功能不依赖时产生的错误消息,以及导致MySQL在没有功能依赖性的情况下接受查询的方法。
该查询可能ONLY_FULL_GROUP_BY
因启用而无效, 因为address
选择列表中的未列出的列在GROUP BY
子句中未命名:
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.19.4节“功能依赖性检测”。
如果查询具有聚合函数和无GROUP BY
子句,则它不能在选择列表,HAVING
条件或 启用ORDER BY
列表中 具有非集合列ONLY_FULL_GROUP_BY
:
-
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
值为组选择。这里也 ANY_VALUE()
可以使用,如果它是无关紧要的,name
MySQL选择哪个值:
SELECT ANY_VALUE(name), MAX(age) FROM t;
在MySQL 5.7.5及更高版本中,ONLY_FULL_GROUP_BY
也会影响使用DISTINCT
和查询的处理 ORDER BY
。考虑表的情况下,t
有三列c1
,c2
以及 c3
包含这些行:
c1 c2 c3
1 2 A
3 4 B
1 2 C
假设我们执行以下查询,期望结果按以下顺序排序c3
:
SELECT DISTINCT c1, c2 FROM t ORDER BY c3;
要订购结果,必须先重复重复。但是要这样做,我们应该保持第一排还是第三行?这个任意选择影响保留值c3
,这反过来又影响排序,也使得它是任意的。为了防止这个问题,如果任何 表达式不满足以下条件中的至少一个,则具有DISTINCT
且ORDER BY
被拒绝的查询 无效ORDER BY
:
-
表达式等于选择列表中的一个
-
表达式引用并属于查询的所选表的所有列都是选择列表的元素
标准SQL的另一个MySQL扩展允许HAVING
子句中的引用在选择列表中的别名表达式。例如,以下查询返回 name
在表中只发生一次的值 orders
:
-
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;
注意
在MySQL 5.7.5之前,启用 ONLY_FULL_GROUP_BY
禁用此扩展,因此需要HAVING
使用未使用表达式写入该子句。
标准SQL只允许GROUP BY
子句中的列表达式,所以这样的语句是无效的,因为它FLOOR(value/100)
是一个非列表达式:
-
SELECT id, FLOOR(value/100)
-
FROM tbl_name
-
GROUP BY id, FLOOR(value/100);
MySQL扩展标准SQL以允许GROUP BY
子句中的非列表达式, 并将上述语句视为有效。
标准SQL也不允许在GROUP BY
子句中使用别名。MySQL扩展标准SQL以允许别名,所以另一种写入查询的方法如下:
-
SELECT id, FLOOR(value/100) AS val
-
FROM tbl_name
-
GROUP BY id, val;
该子句中的别名val
被视为列表达式GROUP BY
。
在GROUP BY
子句中存在非列表达式时,MySQL会识别该表达式与选择列表中的表达式之间的相等性。这意味着ONLY_FULL_GROUP_BY
启用SQL模式时,包含的查询GROUP BY id, FLOOR(value/100)
是有效的,因为FLOOR()
在选择列表中出现相同的 表达式。但是,MySQL不会尝试识别对非GROUP BY
列表达式的功能依赖性,因此ONLY_FULL_GROUP_BY
,即使第三个选定的表达式是子句中的id
列和 FLOOR()
表达式 的简单公式,以下查询无效 GROUP BY
:
-
SELECT id, FLOOR(value/100), id+FLOOR(value/100)
-
FROM tbl_name
-
GROUP BY id, FLOOR(value/100);
解决方法是使用派生表:
-
SELECT id, F, id+F
-
FROM
-
(SELECT id, FLOOR(value/100) AS F
-
FROM tbl_name
-
GROUP BY id, FLOOR(value/100)) AS dt;