最近群里一位老同事贴了张报错截图红彤彤一行英文this is incompatible with sql_modeonly_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 先查看当前模式在改任何东西之前先看看数据库当前到底是什么模式。执行下面两条 SQLSELECT 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_modeSTRICT_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局部绕过有时候我只想临时让某一个查询跑通不希望动全局配置也不想重写整条 SQLANY_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);现在执行报错 SQLSELECT 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_modeonly_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 拿完整数据如果需求是“每个部门工资最高的员工是谁”用方案一拿不到完整姓名就得用 JOINSELECT 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;然后再跑原来的 SQLSELECT dept_id, name, MAX(salary) FROM employee GROUP BY dept_id;这次能正常返回结果但会看到name列取的是组内的某一条记录。在我这个测试数据里部门 1 返回的名字可能是“张三”也可能是“李四”取决于执行计划不保证固定。一旦切换到新会话这个模式又会失效。所以我的建议是临时排查可以用线上长期运行绝对不要依赖这个状态。SQL 重写才是干净彻底的手段。5. 线上方案取舍与常见坑5.1 改 SQL 和改配置怎么选这里给出一份我在实际项目中总结的取舍标准方案影响范围风险适用场景重写 SQL单条 SQL低几乎所有场景首推ANY_VALUE单条 SQL中非敏感字段的临时统计会话级修改 sql_mode当前连接低排查问题、临时验证全局修改 sql_mode所有新连接高大量旧 SQL 短期无法改造修改配置文件重启后全部连接很高必须结合 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 的面试题里频繁出现背后考察的其实是开发者对分组语义的理解。把原理弄透了无论换到哪个版本、哪种数据库都不会再被这种问题绊住。