MySQL ONLY_FULL_GROUP_BY 报错全解析：从原理到正确重构方案

如果你在业务代码里写过这样的查询SELECT dept_id, name, MAX(salary) FROM employee GROUP BY dept_id;在 MySQL 5.6 里也许跑得好好的一到 5.7 或者 8.0啪一下直接给你扔出来一条 ERROR 1055Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated columnemployee.namewhich is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by网上搜一圈大部分答案都是“把 sql_mode 里的 ONLY_FULL_GROUP_BY 去掉”于是你改完配置重启倒是能查出数了可这个数到底对不对没人说得清。这篇文章想把这套机制讲透。不是简单教你怎么关掉报错而是先弄明白 ONLY_FULL_GROUP_BY 为什么会存在、MySQL 到底在保护什么再给出不同场景下的正确解法最后用一个我最近处理过的业务查询做完整复盘。适合被这个报错折磨过的后端开发、数据分析同学也适合刚接触 MySQL、写 GROUP BY 经常莫名踩坑的新手。1. 先复现一把ERROR 1055到底在什么场景下蹦出来1.1 一条“看起来没问题”的SQL是如何撞墙的我们建一张最简单的员工表CREATE TABLE employee ( id INT PRIMARY KEY, dept_id INT, name VARCHAR(64), salary DECIMAL(10,2) ); INSERT INTO employee VALUES (1, 10, 员工A, 8000), (2, 10, 员工B, 12000), (3, 20, 员工C, 15000), (4, 20, 员工D, 11000);然后执行这个“每个部门最高工资”的查询SELECT dept_id, name, MAX(salary) FROM employee GROUP BY dept_id;在默认开启 ONLY_FULL_GROUP_BY 的 MySQL 5.7 环境里会报错。注意错误文本里强调的就是 name 列它既不在 GROUP BY 子句中也不是聚合函数的参数而且不是“功能依赖于 GROUP BY 列”。如果你用的是市面上常见的图形客户端可能还会被一连串红字吓到。很多人第一反应是MySQL 怎么变严格了5.6 的时候明明能跑。这里确实有版本因素后面详细说。但在报错现场我们首先要承认一点MySQL 并不是故意刁难这条查询确实有语义漏洞——每个部门有很多员工分组后每组只剩一行那么 SELECT name到底应该显示哪个员工的名字1.2 从报错信息里能读出的三件事这段报错值得拆开读Expression #2 of SELECT list说明报错发生在 SELECT 列表里的第 2 个表达式也就是 name。which is not functionally dependent on columns in GROUP BY clause该列与 GROUP BY 字段没有“函数依赖”关系。this is incompatible with sql_modeonly_full_group_by原因是当前模式启用了 ONLY_FULL_GROUP_BY。类似的报错还会出现在 HAVING 和 ORDER BY 里。比如SELECT dept_id, MAX(salary) FROM employee GROUP BY dept_id HAVING name 员工A;这里 name 在 HAVING 里被引用但分组后根本不知道是哪一行的 name。再比如SELECT dept_id, MAX(salary) FROM employee GROUP BY dept_id ORDER BY name;ORDER BY name 同样不合法原因一样排序字段不在分组里。报错信息会把 “SELECT list” 换成 “ORDER BY clause”Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ... this is incompatible with sql_modeonly_full_group_by把这三类报错的特征读熟了排查时会快很多不是所有 1055 都来自 SELECT 列表但根因一致——某列在分组后取值不确定。1.3 不是所有非分组列都会被拒绝主键分组的特例这里要纠正一个常见误解很多同学以为 ONLY_FULL_GROUP_BY 下SELECT 列表里除了 GROUP BY 字段和聚合函数之外其他普通列一定报错。其实不是。如果 GROUP BY 的是主键或其他唯一键那么分组后每组其实只有一行因为主键唯一决定整条记录。此时 SELECT 主键以外的列是安全的MySQL 也允许。比如SELECT id, dept_id, name, salary FROM employee GROUP BY id;这条查询可以正常执行。因为 id 是主键给定 iddept_id、name、salary 都能唯一确定。这就是“函数依赖”在起作用。MySQL 5.7 开始已经实现了部分函数依赖检查不只是无脑拒绝。所以说ONLY_FULL_GROUP_BY 真正拒绝的是“列的值在分组内不确定”的情况。理解这一点后面你做方案选择时会清醒很多。2. 模式背后的SQL语义为什么分组后不能随便select其他列2.1 分组之后行已经“变了”GROUP BY 的语义是把多行数据按列去重分组每组输出一行。你可以把一张表想象成一个班级GROUP BY dept_id 相当于按部门把人群分类每个部门最终只派一名代表发言。如果这名代表要说“部门里所有员工的名字”它说不出来因为每个部门的代表只有一个名额但部门里姓名的集合可能是多个值。在 SQL 标准里SELECT 列表能出现的列只有三类GROUP BY 的列、聚合函数、以及能由 GROUP BY 列函数依赖确定的其他列。ONLY_FULL_GROUP_BY 正是为了强制执行这个标准语义。它不是为了增加麻烦而是为了避免一个更严重的问题你不知道数据库会从哪一行取值不同版本、不同索引、不同执行计划下查出来的结果可能不一样。如果关闭这个模式同样的 SQL5.6 里可能取到第一个碰到的 name8.0 里可能取到索引扫描路径下的最后一个 name。产品经理对着数据复盘时根本没法解释这就是最大的坑。2.2 ONLY_FULL_GROUP_BY检查的三个位置这个模式不只检查 SELECT 列表还会检查 HAVING、ORDER BY甚至可以延伸到 UNION、子查询里对别名的引用等场景。日常最常踩的是SELECT 列表选了非分组列HAVING 条件里引用非分组列ORDER BY 排序引用非分组列其中 ORDER BY 的报错信息我刚才已经给过。HAVING 里的报错与之类似但更容易被忽略因为 HAVING 本身通常出现在 GROUP BY 之后大家潜意识里觉得 HAVING 条件应该可以随便写实际不行。一个好的习惯是在 HAVING 中只使用聚合条件或 GROUP BY 字段比如HAVING MAX(salary) 10000。另外有些人会在 GROUP BY 里直接写 SELECT 别名比如SELECT YEAR(created_at) AS y, COUNT(*) FROM orders GROUP BY y;MySQL 对这种写法比较宽容但为了在不同模式和版本下行为一致我建议 GROUP BY 尽量使用底层表达式写成GROUP BY YEAR(created_at)。不要依赖别名排查和改写时能省很多事。2.3 MySQL 5.7/8.0默认开启这事是怎么来的MySQL 5.6 及更早版本sql_mode 默认不包含 ONLY_FULL_GROUP_BY所以很多老开发者的直觉是“GROUP BY 随意查”。从 MySQL 5.7.5 开始ONLY_FULL_GROUP_BY 进入默认 sql_mode。8.0 继续沿用。因此大量 5.6 升 5.7 的项目升级第一天就会在慢查询日志里看到成片的 1055。这也是为什么网上很多经验贴说要“改配置”。因为老项目代码里可能到处是这种不严谨的分组查询业务一时半会儿改不完只能先把模式关掉保证系统能跑。这个操作我在生产环境也见过但我们要清楚它只是“临时兼容”。MySQL 8.0 默认的 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. 现场排查的思路先别急着改sql_mode3.1 确认当前sql_mode的两种方式遇到报错第一步不是改 SQL而是先确认环境到底有没有这个模式。执行SELECT GLOBAL.sql_mode; SELECT SESSION.sql_mode;也可以SHOW VARIABLES LIKE sql_mode;两个命令的区别在于全局变量影响新建立的连接会话变量影响当前连接。如果你在同一个会话里SET SESSION sql_mode临时改过SHOW VARIABLES查到的就是改过的。如果只在配置文件中修改了 my.cnf 还没重启可能还没生效。排查时建议两个都查才能判断是“全局默认就是如此”还是“某次会话被改了”。3.2 判断报错真正原因模式限制还是查询逻辑错误同样一条 1055 报错背后可能是两类问题查询逻辑本身有歧义比如前面说的按 dept_id 分组却 select name数据库确实不知道该返回哪个。查询逻辑其实没有歧义但 MySQL 的检测规则无法识别这种情况相对少见因为 MySQL 对主键等已有函数依赖识别。在实际排查中你先问自己一个问题这个非聚合列在“每组一行”的视野下值是不是唯一的如果唯一就是数据库误报或检测不全面可以考虑把该列加入 GROUP BY反正不会改变分组粒度或使用 ANY_VALUE 表达“这个值实际上唯一”。 如果不唯一就是业务查询逻辑问题必须重构。比如“每个部门最高工资的员工姓名”——部门分组下要的 name 并不是任意员工姓名而是最高工资对应的那个人这无法靠 GROUP BY 直接表达需要换思路。3.3 容易被忽略的几个隐藏触发点除了最基本的 SELECT 列下面这些情况也容易触发写代码时要多留神GROUP BY 中使用表达式SELECT 中使用别名。上面的GROUP BY y例子就是典型。稳妥做法是 GROUP BY 底层表达式不依赖别名。UNION 合并多个分组查询时字段顺序和分组模式不一致。比如第一个 SELECT 是按 dept_id 聚合第二个 SELECT 却多出来一个普通列UNION 合并时可能报 1055。子查询内部的 GROUP BY 字段和外部 SELECT 引用字段混在一起报错位置会指向外层容易误解。我一直觉得写嵌套子查询时最好先在内层把字段范围限定清楚外层只做过滤不要贪图省事直接引用内层未分组的列。DISTINCT 和 GROUP BY 混用时也可能出现类似问题但报错形式可能变为对其他表达式的限制。排查这些场景时我习惯把 SQL 拆成最小复现单元逐步注释字段找到第一个报错字段再逻辑判断。这个方法效率很高超过盯着整条 SQL 发呆。4. 解决这个报错的四种姿势对应不同业务诉求4.1 直接改sql_mode什么情况下可以什么情况下是埋雷关闭或移除 ONLY_FULL_GROUP_BY 确实能让报错消失。比如会话级SET SESSION sql_mode REPLACE(SESSION.sql_mode, ONLY_FULL_GROUP_BY, );或在 my.cnf [mysqld] 配置段中指定sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION然后重启实例。需要提醒的是全局关闭后所有连接都被影响不只会让“问题 SQL”跑通还会让所有新写的错误 SQL 都跑通未来很容易产生随机数据。云厂商的托管实例通常禁止或需要走工单才能修改参数组并不是你想改就能改的。如果只是临时排查一定要用SET SESSION不要动全局查完记得改回来。我见过一个真实案例某报表系统因为一个分组查询报错开发直接把全局模式关了结果另一个统计模块“看起来正常”但同一份数据每天跑出来的金额都不一样排查了两周才发现是 GROUP BY 取任意值导致的。所以这个方案能不用就不用。4.2 ANY_VALUE期望一个“任意值”时的可行解MySQL 5.7.5 引入了 ANY_VALUE 函数语法上可以把一个非分组列包起来告诉优化器“这里返回哪一行的值都可以”SELECT dept_id, ANY_VALUE(name) AS name, MAX(salary) AS max_salary FROM employee GROUP BY dept_id;这比关闭模式温和得多因为它只是“对这一个字段放宽”其他字段仍受检查。但它本质上依然返回任意值不适合有明确业务含义的字段。什么时候适合用比如你只关心每组有没有值、展示一个样例值、或者这个字段在业务上就是同组同值只是 MySQL 不知道。比如订单表按 user_id 分组想顺带看一个下单设备类型虽然业务上同组应该同值但用 ANY_VALUE 并不暴露问题万一数据异常这个字段也可能变成另一个值。更严谨的做法是再加一个 MIN/MAX保证取值确定。如果你发现实际运行中经常需要 ANY_VALUE就好比告诉数据库“别管我”这通常意味着你的查询建模有问题需要回头想想。4.3 把字段塞进GROUP BY很多人的第一反应但要小心语义变化最直接的改法是把报错字段追加到 GROUP BY 后面SELECT dept_id, name, MAX(salary) FROM employee GROUP BY dept_id, name;这样能通过但查询语义已经变了它不再是“每个部门的最高工资”而是“每个部门中每个名字对应的最高工资”。如果部门里恰好有两个同名员工结果会更碎。你原本想返回一行现在可能返回多行业务方拿到数据会懵。不过有一个场景很适合这个方案字段与分组键确实有函数依赖关系时。比如你按订单 ID 分组又想在 SELECT 里带上订单号、用户 ID那把这些列加入 GROUP BY 不会改变分组粒度因为它本来就是一对一的。此时代码自解释不用 ANY_VALUE 也合法。4.4 用子查询/窗口函数重构业务治本方案很多被 1055 拦下的查询本质是想找“每组满足某个条件的记录”这类问题就不适合用 GROUP BY 明细字段硬拼。因为 GROUP BY 的目标是把多行聚合成一行而你要的是“筛选条件”。经典解法有先按组聚合拿到想要的聚合值比如每组最大时间再与原表关联。用关联子查询在每一行上计算组内聚合。MySQL 8.0 可以使用窗口函数如 ROW_NUMBER() 或 RANK()。这些方法不会触发 ONLY_FULL_GROUP_BY而且能精确控制每组的返回记录比如“每个部门工资最高的员工”。代价是 SQL 更长、可能有性能开销。具体怎么选见下一节完整案例。4.5 四种方案怎么选一张表看清边界方案报错是否消失结果是否可信适合场景风险关闭 ONLY_FULL_GROUP_BY是不一定临时排查、老项目兼容过渡影响全局产生随机取值风险最高ANY_VALUE是列值为任意值同组值本身相同或展示无关紧要业务误读、结果不确定追加字段到 GROUP BY是分组粒度变细字段与分组键存在一对一关系返回行数变多语义改变子查询/窗口函数是完全可信需要返回组内明细记录SQL 变长执行计划需关注这张表是我每次给新同学讲 1055 都会贴的。不是所有方案都一样先想清楚业务到底要什么。5. 一个真实业务查询的重构全过程查每位用户最近一笔订单5.1 初始写法与报错假设有张订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT, order_no VARCHAR(32), amount DECIMAL(10,2), created_at DATETIME, KEY idx_user_time (user_id, created_at) );需求查出每个用户最近一笔订单的订单号、金额、时间。很多人的第一版 SQL 是SELECT user_id, order_no, amount, MAX(created_at) AS last_time FROM orders GROUP BY user_id;跑一下果不其然又见 1055。order_no 和 amount 既不在 GROUP BY 里又不是聚合函数。有人会说我要的就是刚才 MAX(created_at) 那一行的 order_no 和 amount 呀。问题恰恰在于 GROUP BY 并不保证“MAX(created_at) 所在那行”的 order_no 能对应过来它只是聚合了时间最大值。这是两种完全不同的计算逻辑。5.2 用ANY_VALUE的“假成功”与隐患如果只是想快速跑通有人会写SELECT user_id, ANY_VALUE(order_no) AS order_no, ANY_VALUE(amount) AS amount, MAX(created_at) AS last_time FROM orders GROUP BY user_id;这句能过但你拿到的 order_no 和 amount很可能不是 last_time 对应的那笔单。假设用户 A 买过 3 个订单金额分别是 100、200、300时间分别为 1、2、3 号。last_time 是 3 号但 ANY_VALUE(order_no) 可能返回 1 号或 2 号的单号。对交易报表来说这是致命的。这个案例我建议所有开发都亲手试一遍用几条数据验证一下 ANY_VALUE 返回什么。你会发现它的取值往往和表的物理顺序、索引扫描顺序有关换一个执行计划结果就变。因此任何对业务有精确要求的字段都不要用 ANY_VALUE 应付。5.3 用关联子查询和窗口函数拿到正确结果正确思路是“先定位每个用户最近时间再取这笔订单的完整信息”。关联子查询版本SELECT o.user_id, o.order_no, o.amount, o.created_at FROM orders o WHERE o.created_at ( SELECT MAX(o2.created_at) FROM orders o2 WHERE o2.user_id o.user_id );这里 WHERE 里的关联子查询对每个外层行都执行一次如果 orders 表很大且没有索引性能会很差。好在有 idx_user_time 索引每个用户的聚合可以走索引范围扫描一般可接受。但上面这个写法还有一个问题如果同一个用户在同一秒下两笔订单created_at 相同会把两笔都查出来。业务上如果要“只取一笔”还得加条件。比如增加 id 最大才取或者直接使用窗口函数。MySQL 8.0 窗口函数版本SELECT user_id, order_no, amount, created_at FROM ( SELECT user_id, order_no, amount, created_at, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at DESC, id DESC ) AS rn FROM orders ) t WHERE rn 1;这个版本逻辑很直白按用户分区按时间倒序、id 倒序排好每个用户取第一条。结果可控扩展性也强。如果用户没有订单则不返回符合需求。5.4 改造后的语句长什么样给出一份可直接用于业务的完整示例SELECT user_id, order_no, amount, created_at FROM ( SELECT user_id, order_no, amount, created_at, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY created_at DESC, id DESC ) AS rn FROM orders WHERE created_at 2024-01-01 -- 示例过滤条件 ) t WHERE rn 1;最终结果里不再有任何非确定性字段也不会触发 ONLY_FULL_GROUP_BY。性能上窗口函数通常需要一次排序但可以通过(user_id, created_at, id)索引尽量优化。真实场景中如果数据量极大还可以先用一个临时表只存储每个用户的最大(created_at, id)再二次关联效果类似。这个案例想表达的核心是1055 报错不是让你把 sql_mode 干掉而是提醒你“这里不能用简单分组表达”。换一种查询建模方式问题自然消失。6. 把这套规则写进日常开发与review时的几条建议6.1 写分组查询前的自检问题我现在每次写带 GROUP BY 的 SQL都会先问自己三件事SELECT 列表里除了分组键和聚合函数还有哪些普通列这些列在每组内是否唯一HAVING 条件里是否用了普通列能不能改成聚合条件如果我想“取每组满足某条件的明细行”为什么不用窗口函数或子查询这三个问题过一遍能挡掉九成 1055。尤其在数据报表和统计接口里宁可多写几步也不要抱着“反正能查出数”的心态。代码 review 时我也会特别盯着 GROUP BY 后面的字段集。如果出现 SELECT 名字段而 GROUP BY 只有少数字段一定要求作者解释字段在组内的唯一性依据。解释不清楚这个查询就不要合入主干。6.2 别把“关掉模式”当默认方案关掉 ONLY_FULL_GROUP_BY不是“配置调优”而是“规则降级”。它会让很多隐藏的孤儿数据、脏数据在未来某个时刻突然变成报表上的一个错数。尤其数据分析或交易系统错一个字段可能就是事故。如果实在因为历史包袱需要临时关至少做到在会话级别关只给需要跑批的脚本用上线前安排排期把相关 SQL 逐条改写成合规写法在监控里加上对 sql_mode 的巡检防止有人全局修改后遗忘我个人的态度是ONLY_FULL_GROUP_BY 是一个免费的 SQL 水平检测器。你写出的查询如果在这套规则下跑不通大概率是查询逻辑本身有歧义而不是规则太严。6.3 版本差异与云端环境注意事项最后聊兼容性。MySQL 5.7 和 8.0 对函数依赖的识别能力有差异某些 5.7 下报错的 SQL在 8.0 某个小版本可能已经能过也可能反过来。所以同一套代码在不同实例上最好不要依赖“之前能跑”来判断对错。如果你用的是云厂商的托管实例默认参数组通常也带 ONLY_FULL_GROUP_BY。想要修改需要走控制台调整参数注意参数的生效方式与重启要求。在写 SQL 时默认就当它存在这样最省事。另外如果团队里有多个 MySQL 版本建议在测试环境统一用模式最严格的 8.0 来验证 SQL。这样能提前暴露问题避免上线后在 5.7 跑通、8.0 炸掉的尴尬。以后你再看 Group By 报错第一反应不该是“怎么把这个限制关掉”而是“我这条查询在分组后那个字段到底确定不确定”。想清楚这一点很多坑就自然绕开了。