查询基础（SELECT / JOIN / 聚合 / 排序） · 聚合与分组

GROUP BY 后 SELECT 的列必须出现在 GROUP BY 中或聚合函数内吗？为什么 MySQL 默认（sql_mode=ONLY_FULL_GROUP_BY）严格要求？

Do columns in the SELECT list have to appear in the GROUP BY clause or be used in an aggregate

答案语言

考察要点

这道题面试官想看你是否深刻理解 SQL 的聚合查询标准、数据确定性以及 MySQL 的特定实现。核心考察点包括：

SQL 标准与数据一致性：是否明白 GROUP BY 的核心语义是“将多行聚合成一行”，以及为什么 SELECT 一个非聚合、非分组的列会破坏结果的唯一性和确定性。
MySQL sql_mode 的演进：是否了解 ONLY_FULL_GROUP_BY 模式的历史、在 MySQL 5.7 前后的变化，以及为什么它现在是默认设置。
函数依赖（Functional Dependency）：是否掌握这一数据库理论的关键概念，并能解释为什么在满足函数依赖时，现代 MySQL (8.0+) 可以放宽 ONLY_FULL_GROUP_BY 的限制。

核心概念

标准 SQL (SQL-92 标准) 规定，任何出现在 SELECT 列表中的非聚合函数列，都必须出现在 GROUP BY 子句中。MySQL 从 5.7.5 版本开始，默认开启 ONLY_FULL_GROUP_BY 模式，强制遵守此标准。

1. 为什么需要这个规则？—— 结果的确定性

GROUP BY 的作用是将多行数据按分组键聚合成一行。如果 SELECT 了一个既没有被分组，也没有被聚合的列，数据库就不知道应该从组内的多行中选择哪一行的值作为结果。

数据示例： 假设有订单表 orders：

order_id	user_id	user_name	amount
1	101	Alice	100
2	102	Bob	150
3	101	Alice_new	200
4	103	Charlie	50

执行一个不符合规范的 SQL：

sql

 1SELECT user_id, user_name, SUM(amount)
 2FROM orders
 3GROUP BY user_id;

分析： 对于 user_id = 101 这个分组，它包含两行数据。SUM(amount) 的结果是确定的 100 + 200 = 300。但是 user_name 列有 Alice 和 Alice_new 两个值。数据库应该返回哪一个？

MySQL 5.7.5 之前 (默认 ONLY_FULL_GROUP_BY 关闭)：MySQL 会从分组中随机选择一行的 user_name 返回。这导致查询结果是不确定的，同一次查询在不同时机可能返回不同结果，是严重的潜在 BUG。
MySQL 5.7.5 及之后 (默认 ONLY_FULL_GROUP_BY 开启)：MySQL 会直接报错，提示 user_name 列不符合 ONLY_FULL_GROUP_BY 规则。

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.orders.user_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

2. 例外情况：函数依赖 (Functional Dependency)

函数依赖是指：如果列 A 的值可以唯一确定列 B 的值（A → B），那么 B 就函数依赖于 A。最常见的例子是：主键 → 表中的其他所有列。

MySQL 8.0 等现代数据库引擎足够智能，能够检测出函数依赖。如果 SELECT 的列函数依赖于 GROUP BY 的列，那么即使它没有出现在 GROUP BY 子句中，查询也是合法的，因为结果仍然是确定的。

数据示例： 有用户表 users 和订单表 orders：

users 表 (id 为主键):

id	name	email
101	Alice	[email protected]
102	Bob	[email protected]

orders 表:

order_id	user_id	amount
1	101	100
2	102	150
3	101	200

合法查询 (MySQL 8.0+)

sql

 1SELECT
 2    u.id,
 3    u.name, -- name 函数依赖于 id
 4    u.email, -- email 函数依赖于 id
 5    SUM(o.amount)
 6FROM users u
 7JOIN orders o ON u.id = o.user_id
 8GROUP BY u.id; -- 只按主键分组

分析： 因为 GROUP BY u.id，而 u.id 是 users 表的主键。对于任何一个 u.id 的值，u.name 和 u.email 的值都是唯一确定的。因此，MySQL 8.0 允许这个查询，因为它能推断出结果的确定性。

3. 如何“绕过”或正确处理：`ANY_VALUE()` 和聚合函数

如果你明确知道分组内的非聚合列的值都是相同的，或者你不在乎返回哪一个值，可以使用以下方法：

ANY_VALUE(col) (MySQL 5.7+): 明确告诉 MySQL：“我知道这个结果可能不确定，请从分组中随机返回一个 col 的值”。这是向上个版本“不安全”行为的兼容，但现在是显式、可控的。
sql
```
 1SELECT user_id, ANY_VALUE(user_name), SUM(amount)
 2FROM orders
 3GROUP BY user_id;
```

MIN(col) / MAX(col) / GROUP_CONCAT(col): 使用聚合函数将多值处理成单值。

sql

 1-- 获取分组中字典序最小的 user_name
 2SELECT user_id, MIN(user_name), SUM(amount)
 3FROM orders
 4GROUP BY user_id;

常见低分回答

只知其然，不知其所以然：“MySQL 就是这么规定的，SELECT 的列必须放 GROUP BY 里，不然会报错。” —— 没有解释背后的数据确定性原则。
将原因归结为性能问题：“如果不这么做，MySQL 扫描和分组的性能会很差。” —— 这是错误的。ONLY_FULL_GROUP_BY 的首要目的是保证数据正确性，而非性能。
粗暴地将所有 SELECT 列加入 GROUP BY：为了让 SQL 不报错，将 SELECT 的所有非聚合列都加到 GROUP BY 后面。
sql
```
 1-- 错误示范：这改变了查询的聚合粒度，不再是按 user_id 聚合
 2SELECT user_id, user_name, SUM(amount)
 3FROM orders
 4GROUP BY user_id, user_name;
```
这个查询会按 (user_id, user_name) 对进行分组，如果一个用户有多个名字，会被分成多组，完全违背了“按用户统计总金额”的初衷。
不知道 ANY_VALUE()：在需要从分组中取一个代表值时，不知道使用 ANY_VALUE() 这个官方推荐的函数，而是使用 MIN() 或 MAX()，这虽然能解决问题，但可能不完全符合业务意图。
不了解函数依赖：不知道在 MySQL 8.0 中，GROUP BY 主键后，可以直接 SELECT 该表的其他列，这会错失展示自己数据库理论深度的机会。

工程实践与优化

始终保持 ONLY_FULL_GROUP_BY 开启：在生产环境中，强烈建议保持此模式开启。它可以从根源上避免因不确定性查询导致的、难以复现的线上 BUG。
合理使用 ANY_VALUE()：当你通过业务逻辑或数据清洗，能确保某个分组内非聚合列的值是唯一的（例如，在数据仓库的宽表中，按 user_id 分组，user_level 列对于同一个用户总是相同的），可以使用 ANY_VALUE(user_level) 来避免不必要的聚合计算，并简化 SQL。
检查和修改 sql_mode：
- 查看当前会话的 sql_mode：SELECT @@SESSION.sql_mode;
- 查看全局的 sql_mode：SELECT @@GLOBAL.sql_mode;
- 修改 sql_mode（不推荐在生产中移除 ONLY_FULL_GROUP_BY）：
  sql
```
 1-- 仅修改当前会话
 2SET SESSION sql_mode = '...'; -- 填入移除 ONLY_FULL_GROUP_BY 后的模式字符串
```
利用函数依赖简化查询：在写查询时，如果已经按主键 GROUP BY，就可以放心地 SELECT 该表的其他字段，让 SQL 更简洁易读，同时也能获得正确的确定性结果。
执行计划分析：当 GROUP BY 的列增多时，排序或哈希聚合的成本会增加。通过 EXPLAIN 查看执行计划，关注 Using temporary; Using filesort 等信息，评估聚合操作对性能的影响。

常见追问与延伸题目

追问：如果我确认每个组内的非聚合列值都相同，除了用 ANY_VALUE()，还有什么方法能通过 ONLY_FULL_GROUP_BY 校验？
- 回答要点：可以使用 MIN() 和 MAX() 聚合。例如，SELECT user_id, MIN(user_name), SUM(amount)。因为如果组内所有 user_name 都相同，那么 MIN(user_name)、MAX(user_name) 和 ANY_VALUE(user_name) 的结果必然相同。这种方式在所有 SQL 数据库中都兼容，可移植性比 ANY_VALUE() 更好。但 ANY_VALUE() 在 MySQL 中意图更明确，就是“我需要一个值，不在乎是哪个”。
追问：PostgreSQL 或 Oracle 在这方面和 MySQL 有什么区别？
- 回答要点：PostgreSQL、Oracle、SQL Server 等主流数据库历来都严格遵守 SQL 标准，其行为等同于 MySQL 开启 ONLY_FULL_GROUP_BY 模式。可以说，是 MySQL 在早期版本为了“易用性”而偏离了标准，后来才回归主流。PostgreSQL 同样支持函数依赖检测，如果按主键分组，也可以 SELECT 其他列。
追问：禁用 ONLY_FULL_GROUP_BY 会有什么长期的技术债和风险？
- 回答要点：
  - 隐藏的 BUG：查询结果依赖于数据库内部实现、数据物理存储顺序、查询计划等不可控因素，导致偶发性的数据错误，极难排查和复现。
  - 代码不可移植：依赖此特性的 SQL 无法在 PostgreSQL、Oracle 等其他数据库或更高版本的 MySQL 中运行。
  - 团队协作困难：新加入的开发者通常期望 SQL 行为是标准的、确定性的，这种“黑魔法”会增加他们的理解成本和出错概率。
  - 数据分析误导：分析师或业务方基于不确定的查询结果，可能得出错误的结论，从而影响决策。