MySQL5.7.5及以上版本在进行group by查询时,查询的所有列除了来自于聚合函数(sum、avg、max等)的结果外,其他列都要求在group by字段里,否则会报 [Err] 1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated....
类似的错误。这是因为在MySQL5.7之后,sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态,此模式要求分组查询时的列除聚合函数外必须包含在group by字段里。对此MySQL也提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝。
1、ONLY_FULL_GROUP_BY模式介绍
从 MySQL 5.7.5 开始,默认 SQL 模式包括 ONLY_FULL_GROUP_BY(在 5.7.5 之前,MySQL 不检测功能依赖,ONLY_FULL_GROUP_BY默认不启用。)ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在此模式下,target list中的值要么是来自于聚合函数(sum、avg、max等)的结果,要么是来自于group by list中的表达式的值。
-
MySQL 5.7.5 及更高版本实现了功能依赖检测。如果SQL模式启用了ONLY_FULL_GROUP_BY(默认情况下),则 MySQL 拒绝在列表、HAVING条件或 ORDER BY列表中引用非聚合列的查询,这些列既不在GROUP BY子句中命名,也不在功能上依赖于它们。
-
如果ONLY_FULL_GROUP_BY禁用,则标准 SQL 使用的 MySQL 扩展 GROUP BY允许选择列表、 HAVING条件或ORDER BY列表引用非聚合列,即使这些列在功能上不依赖于GROUP BY列。
针对上述情况,若我们查询的值不包含在GROUP BY中,可以使用ANY_VALUE()函数,该函数的作用是返回一个不确定的 expr 值。你可以把它作为一个聚合函数或分析函数来使用。
-
使用 ANY_VALUE 优化包含 GROUP BY 子句的查询。ANY_VALUE 返回一个组中表达式的值。它被优化为返回第一个值。
-
它确保不会对任何输入行进行比较, 也消除了将每一列指定为 GROUP BY 子句的一部分的必要性 。因为 ANY_VALUE 不比较值,所以它在 GROUP BY 查询中比 MIN 或 MAX 更快地 返回一个值。
2、ANY_VALUE示例讲解
有一个地区表,需要查询所有的省份,具体数据如下:
mysql> select * from area;
+----+----------------+----------------+-----------+-----------+
| id | proviance_code | proviance_name | city_code | city_name |
+----+----------------+----------------+-----------+-----------+
| 1 | 1 | 河南 | 1 | 商丘 |
| 2 | 1 | 河南 | 2 | 南阳 |
| 3 | 1 | 河南 | 3 | 开封 |
| 7 | 2 | 杭州 | 1 | 余杭区 |
| 8 | 2 | 杭州 | 2 | 拱墅区 |
| 9 | 2 | 杭州 | 3 | 下城区 |
+----+----------------+----------------+-----------+-----------+
6 rows in set (0.00 sec)
针对这个问题,我们有多种解决方案。
2.1 方案一:使用DISTINCT函数
SELECT DISTINCT(proviance_code),proviance_name from area;
2.2 方案二:使用group by函数结合any_value函数
1. mysql5.7.5及以后的版本
在mysql5.7.5及以后的版本中,因为SQL 模式默认开启了 ONLY_FULL_GROUP_BY,我们可以使用 group by
函数结合 any_value
函数的方式。即没有参与分组或排序的字段, 使用ANY_VALUE()函数包括。
- any_value:MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝,any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。
SELECT proviance_code, any_value(proviance_name) from area GROUP BY proviance_code;
2. mysql5.7以及之前的版本
mysql5.7以及之前的版本中还可以使用以下sql:
SELECT proviance_code, proviance_name from area GROUP BY proviance_code;
注意:该语句在mysql5.7版本之后会报错,具体错误如下:
SELECT proviance_code,proviance_name from area GROUP BY proviance_code 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'area.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 时间: 0.016s
- 发生错误的原因是proviance_name是非聚合列。
- 这是因为在MySQL5.7之后,sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在此模式下,target list中的值要么是来自于聚合函数(sum、avg、max等)的结果,要么是来自于group by list中的表达式的值。
- 若想在mysql5.7版本之后使用上述语句,可以通过修改sql_mode的方式来解决。即关闭ONLY_FULL_GROUP_BY模式。
3、GROUP BY和ANY_VALUE
ANY_VALUE函数会返回一个不确定的 expr 值,你可以把它作为一个聚合函数或分析函数来使用,优化包含 GROUP BY 子句的查询。ANY_VALUE函数确保不会对任何输入行进行比较,也消除了将每一列指定为 GROUP BY 子句的一部分的必要性。因为 ANY_VALUE 不比较值,所以它在 GROUP BY 查询中比 MIN 或 MAX 更快地 返回一个值。
1. 使用ANY_VALUE的语句
SELECT c.cust_id , ANY_VALUE ( cust_last_name ), SUM ( amount_sold )
FROM customers c , sales s
WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id ;
2. 不使用ANY_VALUE的语句
上述语句若没有使用 ANY_VALUE 时,则SQL 语句必须要将每一列指定为 GROUP BY 子句,即需要将上例中的 cust_last_name 也指定到 GROUP BY 子句,示例如下:
SELECT c.cust_id , cust_last_name , SUM ( amount_sold )
FROM customers c , sales s
WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id , cust_last_name ;
4、GROUP BY拓展
MySQL 5.7.5 及更高版本允许在启用 ONLY_FULL_GROUP_BY 模式时,GROUP BY时未在子句中命名的非聚合列 ONLY_FULL_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 |
+------+--------+
5、总结
-
MySQL5.7之后,sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。
-
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在此模式下,target list中的值要么是来自于聚合函数(sum、avg、max等)的结果,要么是来自于group by list中的表达式的值
-
MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝
-
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据
评论区