MySQL报错only_full_group_by：原因、排查与SQL改写实战

最近群里一位老同事贴了张报错截图，红彤彤一行英文：this is incompatible with sql_mode=only_full_group_by。这大概是 MySQL 5.7 之后后端同学最常撞见的“老朋友”了。很多人第一反应是“SQL 哪里写错了”，但把 SQL 翻来覆去看，语法也没问题，表结构也没问题，结果愣是跑不过。实际上问题根源不在 SQL 本身，而在 MySQL 的一个运行开关：sql_mode里的ONLY_FULL_GROUP_BY。

这个错误从 MySQL 5.7.5 开始成为默认行为，到了 8.0 依然保留。凡是写过分组查询、GROUP BY后又想顺带取几个其他字段的 SQL，基本上都会命中。尤其是老项目从 5.6 往 5.7 或 8.0 迁移时，原本跑得好好的报表开始成片报错，排查起来非常折磨人。今天我想把它彻底讲明白：这个开关到底做了什么，怎么在不破坏业务逻辑的前提下修复，以及在线上环境里改 SQL 和改配置应该怎么选。无论你是刚接触 MySQL 的新手，还是要处理生产告警的运维同学，这篇都能当一份排查手册来用。

1. 先搞清楚：only_full_group_by 到底管什么

1.1 sql_mode 是一组行为开关

MySQL 的sql_mode不是某个独立功能，而是一组逗号分隔的模式项，用来控制服务器在特定场景下的行为。比如STRICT_TRANS_TABLES控制写入时是否严格校验数据，NO_ZERO_DATE决定日期字段是否允许“0000-00-00”出现，ERROR_FOR_DIVISION_BY_ZERO决定除数为零时是报错还是返回NULL。它们本质上是一群“行为开关”，不同组合会让同一个版本的 MySQL 表现出截然不同的脾气。

ONLY_FULL_GROUP_BY就是其中一个开关，专门管GROUP BY分组查询的合法性问题。它要求：凡是使用了GROUP BY的查询，SELECT后面出现的列，要么是分组依据的那几列，要么是被聚合函数包起来的列，要么与分组列存在函数依赖关系。HAVING和ORDER BY的规则类似。

这个要求源自标准 SQL 的规范。标准之所以这么设计，是为了让查询结果具备确定性和可预测性。如果不加这个限制，某个列既不参与分组、又没被聚合，那它到底取组内哪一条记录的值呢？MySQL 只能“随便挑一条”返回，而这条结果往往是不确定的。

1.2 什么写法最容易触发

看下面这条 SQL，它是典型的报错写法：

SELECT dept_id, name, MAX(salary) FROM employee GROUP BY dept_id;

dept_id是分组列，合法；MAX(salary)是聚合函数，合法；中间那个name既不是分组列，也没有被聚合函数包住，于是直接触发错误。

注意，这里不只是普通列名，表达式同样中招。比如：

SELECT dept_id, LENGTH(name), COUNT(*) FROM employee GROUP BY dept_id;

LENGTH(name)虽然用到了name，但它不是分组列，也不是聚合函数，依然会报错。

还有一个容易忽略的位置是ORDER BY。有些开发者会把非分组列放到排序条件里，比如：

SELECT dept_id, MAX(salary) FROM employee GROUP BY dept_id ORDER BY name;

如果name没有出现在GROUP BY中，也没有被聚合，这个排序条件同样会命中ONLY_FULL_GROUP_BY的检查范围。

1.3 为什么 5.7 之后才频繁出现

在 MySQL 5.6 及更早版本里，ONLY_FULL_GROUP_BY默认是关闭的。也就是说，老版本允许你写“非分组列 + 聚合函数”的混合查询，服务器不报错，只是从该组记录里随机挑一行返回。这个“随机挑”到底挑到谁，取决于执行计划、数据分布、索引命中情况，甚至完全没有规律。同一个查询在测试环境跑一次一个结果，在生产环境跑又是另一个结果，数据稳定性很难保证。

到了 MySQL 5.7.5，官方把ONLY_FULL_GROUP_BY加进了默认配置，于是所有新安装的数据库都默认开启。很多从 5.6 迁移上来的旧项目，原本跑得正常的 SQL 一下全部开始报错。这也是为什么这个错误往往集中出现在“老代码升级新版本”或“新环境部署旧项目”的场合。

2. 治本：改写 SQL，让查询合规

2.1 先明确业务：你到底要哪一行

遇到报错不要急着关开关，先想想业务需求：在一个分组里，你到底希望看到哪一行？

举个例子，查每个部门的最高工资，你可以用MAX(salary)，但如果你还要同时取出“工资最高那个人”的完整信息，那就是另一个需求。再比如查每个用户最近一次登录记录，聚合成MAX(login_time)并没有太大意义，你要的是这一行完整的登录数据。

把需求问清楚，改写的方向就明确了。不要因为报错就把所有非分组列塞进GROUP BY，那样查询粒度会被拆散，统计结果容易变成多条记录，后续报表会悄悄出错。

2.2 把非分组列交给聚合函数

如果业务上只需要某个字段的统计值，直接把它包进聚合函数就行。常见的做法包括：

SELECT dept_id, MAX(salary) AS max_salary, MIN(salary) AS min_salary, GROUP_CONCAT(name) AS all_names, COUNT(*) AS cnt FROM employee GROUP BY dept_id;

GROUP_CONCAT在“想保留组内多个名字但不想报错”的场景里非常好用，它会把组内所有name用逗号拼成一个字符串返回。缺点是这个字符串长度有限制，默认不超过 1024 字节，字段特别多时可能被截断，需要修改group_concat_max_len参数。

如果业务上只是“想取一个代表值”，比如同一个城市有多家门店，统计时只需要拿出任意一家门店的名称，那么MIN(name)或MAX(name)就足够，虽然结果不一定有你想要的优先顺序，但至少语义是确定的。

2.3 子查询 + JOIN 取组内目标行

当需求是“取出每组里满足某个条件的那一整行记录”，聚合函数就无能为力了。这时候最常见的写法是先分组建聚合，再把结果 JOIN 回原表，定位到具体行。

还是拿员工表举例，目标是查询“每个部门工资最高的员工完整信息”：

SELECT e.id, e.name, e.dept_id, e.salary FROM employee e JOIN ( SELECT dept_id, MAX(salary) AS max_salary FROM employee GROUP BY dept_id ) t ON e.dept_id = t.dept_id AND e.salary = t.max_salary;

这个方案的优点是兼容 MySQL 5.6、5.7、8.0，写法也很直观。缺点是如果同一个部门里有两个员工的工资都等于最高工资，结果会返回多行。这不是写法错误，而是业务规则本身存在“并列第一”的情况。如果只需要一条，可以在原表上加上MIN(e.id)之类的去重条件，或者更进一步用窗口函数来解决。

在实际项目中，我还遇到过“每个用户最新一条操作记录”的需求。这种需求用等值 JOIN 可能会碰上时间字段重复的问题，比如一秒内两条记录，时间完全一样。解决办法是 JOIN 条件里再加上一个唯一键，或者直接用它作为去重条件。

2.4 MySQL 8.0 用窗口函数更干净

如果数据库已经升级到 MySQL 8.0 或 MariaDB 10.2 以上，推荐直接用窗口函数处理“每组取一行”的问题。

SELECT id, name, dept_id, salary FROM ( SELECT id, name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn = 1;

这段 SQL 的逻辑是：按dept_id分组，组内按salary倒序编号，编号为 1 的就是该部门工资最高的那一条。如果想让并列第一的记录都出来，把ROW_NUMBER()换成RANK()就行。

窗口函数的可读性比“子查询 + JOIN”更好，执行计划也经常更优，而且彻底绕开了GROUP BY的报错问题。缺点是老版本不支持，升级这件事本身也需要评估成本。

3. 治标：调整 sql_mode，但要看清副作用

3.1 先查看当前模式

在改任何东西之前，先看看数据库当前到底是什么模式。执行下面两条 SQL：

SELECT @@global.sql_mode; SELECT @@session.sql_mode;

@@global.sql_mode是全局配置，影响之后新建的连接；@@session.sql_mode是当前会话生效的配置。多数情况下两者一致，但也不排除有人单独改过当前连接的会话参数。

查出来的结果通常是一长串，类似：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

如果你看到第一个就是ONLY_FULL_GROUP_BY，那报错的原因就坐实了。

3.2 会话级修改

会话级修改只对当前连接生效。比如你在 Navicat、MySQL Workbench 或命令行里排查问题，想快速验证去掉开关之后 SQL 能不能跑：

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

注意，这条命令只影响执行它的那个连接。你开一个新的查询窗口，或者程序重新创建连接，模式会被重置回全局配置。对于临时排查来说很安全，因为它不会污染其他连接，也不会影响生产环境的其他业务。

3.3 全局与配置文件持久化

如果想让所有新连接都生效，可以修改全局配置：

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

但这里有一个非常经典的坑：SET GLOBAL只影响修改之后新建的连接。已经存在的连接池连接，依然保持着修改前的会话模式。也就是说，你明明改了，程序却还在报错，就是因为应用们连接了池中已有的旧连接。

想要持久化，必须把配置写进 my.cnf 或 my.ini 的[mysqld]段，重启 MySQL，再加一句SET GLOBAL临时生效。配置文件示例：

[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

改完配置记得确认两件事：第一，重启 MySQL 后执行SELECT @@global.sql_mode验证；第二，应用连接的连接池要能够重新建立连接，不然旧连接依然停留在老模式下。必要时可以重启应用，或者手动清空数据库连接池。

3.4 ANY_VALUE：局部绕过

有时候我只想临时让某一个查询跑通，不希望动全局配置，也不想重写整条 SQL，ANY_VALUE()是个不错的选择：

SELECT dept_id, ANY_VALUE(name), MAX(salary) FROM employee GROUP BY dept_id;

ANY_VALUE(name)的效果等同于告诉 MySQL：“我知道这个列没有被分组，也没有被聚合，但我接受任意一条记录的值。”这样既绕过了报错，又把影响范围限制在这一条 SQL 上。

不过要清醒一点：ANY_VALUE()返回的是不确定行。如果业务上这个字段必须严格对应到MAX(salary)那条记录，那用它仍然会产生错误数据。它适合“我只想取一个样例值，不关心具体是哪条”的场景，不适合严谨的业务统计。

4. 实操演示：从报错到修复全记录

4.1 先复现一份报错

为了把流程讲透，我模拟一个真实环境。建一张员工表，并插入几条数据：

CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), dept_id INT, salary DECIMAL(10,2) ); INSERT INTO employee (name, dept_id, salary) VALUES ('张三', 1, 8000), ('李四', 1, 9000), ('王五', 2, 7000), ('赵六', 2, 12000), ('钱七', 2, 9500);

现在执行报错 SQL：

SELECT dept_id, name, MAX(salary) FROM employee GROUP BY dept_id;

在默认开启ONLY_FULL_GROUP_BY的 MySQL 5.7/8.0 环境中，得到错误信息：

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

到这里，报错原因就清楚了：name没有出现在GROUP BY里，也不是聚合函数的结果。

4.2 方案一：聚合化改写

如果只是统计每个部门的最高、最低、平均工资，不关心具体是谁，直接改成：

SELECT dept_id, MAX(salary) AS max_salary, MIN(salary) AS min_salary, ROUND(AVG(salary), 2) AS avg_salary, COUNT(*) AS cnt FROM employee GROUP BY dept_id;

结果清晰、语义明确，也不会报错。这是成本最低的修法，也是我最推荐的默认做法。

4.3 方案二：子查询 JOIN 拿完整数据

如果需求是“每个部门工资最高的员工是谁”，用方案一拿不到完整姓名，就得用 JOIN：

SELECT e.id, e.name, e.dept_id, e.salary FROM employee e JOIN ( SELECT dept_id, MAX(salary) AS max_salary FROM employee GROUP BY dept_id ) t ON e.dept_id = t.dept_id AND e.salary = t.max_salary;

执行后输出：

id name dept_id salary 2 李四 1 9000.00 4 赵六 2 12000.00

即使表里插入更多数据，只要“最高工资”规则不变，这个方法都能稳定返回对应记录。要注意的是并列情况：如果部门 1 里再来一个人拿 9000，结果会返回两行。要约束为一条，可以把 JOIN 条件再收紧，或者按MIN(e.id)再过滤一层。

4.4 方案三：临时关闭 only_full_group_by

如果我只是在本地排查问题、临时确认数据，不想大改 SQL，可以执行：

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

然后再跑原来的 SQL：

SELECT dept_id, name, MAX(salary) FROM employee GROUP BY dept_id;

这次能正常返回结果，但会看到name列取的是组内的某一条记录。在我这个测试数据里，部门 1 返回的名字可能是“张三”，也可能是“李四”，取决于执行计划，不保证固定。一旦切换到新会话，这个模式又会失效。

所以我的建议是：临时排查可以用，线上长期运行绝对不要依赖这个状态。SQL 重写才是干净彻底的手段。

5. 线上方案取舍与常见坑

5.1 改 SQL 和改配置怎么选

这里给出一份我在实际项目中总结的取舍标准：

线上环境里，我见过不少团队为了一行 SQL 直接改全局配置，甚至把整个sql_mode清空。这种操作的短期效果立竿见影，但长期埋下的隐患很多。ONLY_FULL_GROUP_BY一旦关闭，之前报错的 SQL 会“静默”返回不确定值。如果是财务统计、库存盘点这类业务，数据错得一塌糊涂可能短时间内发现不了。

真的遇到大量旧 SQL 需要快速救火时，我建议先全局去掉ONLY_FULL_GROUP_BY止血，同时登记所有报错 SQL，在下一步迭代里逐条重写，最后重新开启开关。这个“先止血、后治病”的节奏，比一次性追求完美更可控。

5.2 改了 sql_mode 还是报错的排查

经常有人来问：“我明明执行了SET GLOBAL sql_mode=...，为什么程序还是报这个错？”大多数情况出在连接池上。

连接池里的连接是复用的。你在数据库客户端执行SET GLOBAL后，连接池里已有的连接还保留着旧的SESSION sql_mode。程序拿到的还是老配置，自然继续报错。解决方式有三种：重启应用让连接池重建；在连接初始化 SQL 中显式设置sql_mode；或者在数据库代理层做统一配置。

另一个容易忽略的问题是主从环境。很多架构是主写从读，报错可能只出现在从库上，因为从库的sql_mode和主库不一致。排查时要同时检查所有节点，而不是只看主库。复制线程中断时，报错甚至会指向一些历史遗留的 SQL，看起来毫无规律。

还有一个比较隐蔽的场景：视图。视图在创建时会记录当时的sql_mode等环境信息。如果你建视图的时候ONLY_FULL_GROUP_BY是关闭的，后来重新开启，再去执行这个视图可能会报错；反过来，关闭了模式但视图内部 SQL 不合法，同样可能出现类似问题。处理办法是ALTER VIEW重建视图，或重新创建相关存储过程。

5.3 分组查询的性能调优笔记

既然聊到了GROUP BY，顺便说一句性能。很多人把报错当成性能问题来排查，实际上是走了弯路。ONLY_FULL_GROUP_BY本身不直接影响性能，它只是做合法性校验，真正影响分组查询性能的是执行计划里的排序和临时表。

GROUP BY通常需要把数据按分组列排序或建立哈希表。如果分组列没有合适的索引，MySQL 可能使用文件排序，数据量大时性能会明显下降。建议在分组列和常用的WHERE条件列上建立联合索引。比如上面的员工表，如果经常按dept_id分组统计，那么(dept_id)或(dept_id, salary)这样的索引会有帮助。

使用“子查询 + JOIN”写法时，要特别注意子查询里分组列上的索引。子查询先算出一个小的结果集，再 JOIN 回原表，这时候原表关联列没有索引的话，JOIN 性能会很难看。实践中建议对关联列、过滤列统一加索引，并通过EXPLAIN确认没有出现大范围的Using temporary和Using filesort。

另外，窗口函数虽然写法优雅，在 MySQL 8.0 里也做了不少优化，但遇到超大表时，PARTITION BY的列同样需要索引支撑。不要因为换了个写法就忽略执行计划分析，EXPLAIN永远是分组查询优化里最需要看的东西。

结尾：一点个人经验

我在实际工作中处理这个报错，一般遵循三个步骤：先复现，再确认业务语义，最后才动手改。复现可以把问题锁死在一个最小的 SQL 上；确认业务语义能帮你判断到底该聚合字段、取整行数据还是接受任意值；动手改的时候，优先 SQL 层修复，其次才是会话级调整，最后才考虑全局配置。

另外有个小技巧：如果你拿到的是一大段复杂 SQL，不要整段去看，先把GROUP BY后面的列和SELECT后面的非聚合列单独摘出来，逐列问一遍“它到底要表达什么”。大部分时候，报错原因在半分钟内就能定位。

这个错误还会在 MySQL 8.0 的面试题里频繁出现，背后考察的其实是开发者对分组语义的理解。把原理弄透了，无论换到哪个版本、哪种数据库，都不会再被这种问题绊住。