MySQL 8.0 ERROR 1055：ONLY_FULL_GROUP_BY 原理与修复指南

如果你在 MySQL 8.0 上跑一条带group by的select查询突然看到一个 ERROR 1055提示Expression #N of SELECT list is not in GROUP BY clause ...恭喜你你撞上了 MySQL 从 5.7 开始收紧、到 8.0 默认开满的ONLY_FULL_GROUP_BY模式。这个报错说穿了就一句话你SELECT出来的某些列既没出现在GROUP BY里也没被SUM()、MAX()这类聚合函数包起来数据库不知道该怎么把它们归到每个分组里去。这篇文章写给谁如果你是个后端开发、数据报表开发或者正在维护一个从 5.6 升级到 8.0 的老系统那这份排查思路你应该用得上。我会把报错原理、修复路线、8.0 里新增的坑以及我在实际项目里踩过的真实案例全部摊开讲保证你看完能直接照着操作。1. 报错现场1055错误到底在说什么1.1 一条必现的报错SQL先说一个最典型的场景。假设库里有张员工表employees你想按部门统计人数顺手把部门里某个人的名字也带出来看于是写了这样的 SQLSELECT department_id, employee_name, COUNT(*) FROM employees GROUP BY department_id;这条 SQL 在 MySQL 8.0 里会立刻报错错误信息大概是这样的ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column hr.employees.employee_name which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by很多人第一次看到这个错误会懵因为单看“1055”这个编号完全想不到是 SQL 语法问题。其实这条报错已经把原因说得很清楚了Expression #2说的是SELECT列表里第 2 个表达式也就是employee_name。is not in GROUP BY clause这个字段没有出现在GROUP BY子句里。contains nonaggregated column它是一个“非聚合列”没有被COUNT、SUM、MAX这类聚合函数包住。not functionally dependent on columns in GROUP BY clause它也不是“函数依赖”于分组列这个问题我后面第 3 章单独讲。incompatible with sql_modeonly_full_group_by它跟当前 SQL 模式里的ONLY_FULL_GROUP_BY冲突了。1.2 ONLY_FULL_GROUP_BY 是来约束什么的要理解这个报错得知道ONLY_FULL_GROUP_BY是什么。它是 MySQL 的sql_mode系统变量中的一个选项作用是强制 SQL 遵循标准 SQL 的分组语义。SQL 标准里对分组查询有一条硬性规则如果用了GROUP BY那么SELECT列表里的每一个非聚合列都必须出现在GROUP BY子句中。这条规则的逻辑是分组之后每个组可能对应很多行如果你选出某个“原始列”那到底取哪一行这个值没有唯一确定的意义只是“碰巧”这一组里的某一行的值而已。在 MySQL 5.6 及更早的版本里ONLY_FULL_GROUP_BY默认是不开启的所以这种 SQL 能跑通返回的值是该组里随机某一行的数据。但从 5.7 开始MySQL 默认启用了这个选项到了 8.0 依然默认开启。于是很多从 5.6 升级上来的项目第一次跑到这种 SQL 就直接报错。你可以在命令行里执行下面这条查看当前数据库的 SQL 模式SELECT GLOBAL.sql_mode; SELECT SESSION.sql_mode;在 MySQL 8.0 的默认配置里你会看到ONLY_FULL_GROUP_BY和其他几个选项一起出现在结果里ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION所以这个报错不是你的 MySQL 安装坏了也不是 SQL 写错了字符而是数据库在坚持一套更严格的分组规则。理解了这一点后面所有的修复方案都不难理解。2. 修复路线怎么选改SQL永远比关开关靠谱遇到 1055 报错之后网上搜出来的答案五花八门但归纳起来也就三条路线改 SQL、改会话变量、改全局配置。我的建议很明确能改 SQL 就不要动全局配置。下面逐个说清楚。2.1 路线A把漏网的非聚合列收进聚合函数里如果你的业务只需要“组内任意一个值”那最简单的方式就是用ANY_VALUE()函数把这个列包起来。ANY_VALUE()是 MySQL 5.7 引入的函数代表“我明确知道自己在取任意值请跳过分组检查”。还是刚才那条 SQL改成这样就能过SELECT department_id, ANY_VALUE(employee_name), COUNT(*) FROM employees GROUP BY department_id;它的结果是每个部门一行employee_name取该部门内某个员工的姓名具体是哪一条MySQL 不保证。如果你只是随手看个数据不在意具体是谁那没问题。但如果你明明想取“工资最高的那个人”或者“最近入职的那个人”用ANY_VALUE()就是埋雷因为结果不可控。还有一种常见需求本来就要取某个字段的聚合值比如想查每个部门的最高工资、平均工资、总工资那直接把列包进MAX()、MIN()、AVG()、SUM()里就行。这类修复最干净因为聚合结果是确定的。SELECT department_id, MAX(salary), AVG(salary), SUM(salary) FROM employees GROUP BY department_id;2.2 路线B把非聚合列全部加进 GROUP BY如果这个列本身就是业务维度不是一个“碰巧取出来看看”的值那就应该把它加进GROUP BY。比如改成SELECT department_id, employee_name, COUNT(*) FROM employees GROUP BY department_id, employee_name;这条 SQL 的意义就变了按部门和员工两个维度共同分组统计每个部门每个员工出现的次数。如果你原本就想要“每个部门每个人”的维度组合那这写法完全正确。但这里有个隐患得提醒一下很多人为了消除报错不假思索地把SELECT里的字段全塞进GROUP BY这会把分组粒度越切越细查询结果的行数可能远超预期甚至失去了原先“按部门汇总”的意义。分组字段加多了统计维度就变了最终给出的报表可能跟你想要的不是一回事。所以加字段之前先问自己一句这个字段到底是不是分组的维度如果它只是“组内某条记录的属性”那就别加。2.3 路线C会话级临时修改 sql_mode有些场景下SQL 是别人写的或者系统自动生成的不方便改又急着看数据那你可以在当前会话里临时把ONLY_FULL_GROUP_BY去掉SET SESSION sql_mode (SELECT REPLACE(sql_mode, ONLY_FULL_GROUP_BY, ));执行完之后再跑原来的 SQL就不会报 1055 了。但这个修改只对当前连接有效连接断开就失效了不影响其他连接这是它比全局修改安全的地方。用完想恢复重新设置回来SET SESSION sql_mode (SELECT CONCAT(sql_mode, ,ONLY_FULL_GROUP_BY));或者重新连接一下数据库也会恢复到默认的会话模式。我自己的习惯是这种操作只用于临时排查绝不写进任何自动化脚本里因为它治标不治本而且很容易让开发环境里能跑的 SQL到生产环境就挂掉。2.4 路线D全局关闭 ONLY_FULL_GROUP_BY强烈不建议网上大量教程会让你直接去my.cnf里把sql_mode里的ONLY_FULL_GROUP_BY删掉然后重启 MySQL。比如这样改[mysqld] sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION如果你的 MySQL 是跑在容器里的可能还要在 Dockerfile 或启动命令里加--sql-mode...参数。这个方法确实能彻底消除报错但我劝你谨慎代价有三层所有查询都放行了不规范的 SQL任何团队成员的代码里都可能出现“取分组内随机值”的隐蔽逻辑数据结果不稳定。从 5.7 到 8.0MySQL 官方对模式收紧的态度是明确的关掉开关等于逆着版本趋势走将来升级更麻烦。SQL 标准一直强调确定性关掉ONLY_FULL_GROUP_BY很可能让一个原本可以预测的报表结果某天突然变成另一组值排查成本极高。如果真的因为历史原因必须全局关掉我建议你至少做两件事一是在 release notes 里记录清楚改动原因二是定期用工具扫描慢日志里依赖“非聚合列直取”的 SQL尽早暴露问题。但说实话我处理过那么多项目真正需要全局关闭的场景极少。3. 函数依赖检测为什么有时不报错MySQL自己放行了3.1 函数依赖是什么MySQL 的ONLY_FULL_GROUP_BY并不是死板地要求“所有非聚合列必须出现在 GROUP BY 里”它还留了一个后门叫做“函数依赖”。这是这一章节的核心。什么叫“函数依赖”简单说如果 A 的值确定后B 的值也唯一确定那么 B 就函数依赖于 A。在 MySQL 里最常见的情况就是主键。一张表的主键可以唯一确定一行那么这一行里的所有其他列都函数依赖于主键。所以当你按主键分组时即使SELECT里直接写了其他列MySQL 也认为这个列的值是确定的因为每个主键值对应唯一一行自然对应唯一的列值。举一个实际例子。假设订单表orders的主键是order_id你想查每个订单的金额总和并把订单所属用户一起带出来SELECT order_id, user_id, SUM(amount) AS total FROM orders GROUP BY order_id;这里的user_id没有出现在GROUP BY里也没有被聚合函数包住但 MySQL 不会报错因为order_id是主键user_id函数依赖于order_id。每个分组只有一个用户所以这个值在语义上是确定的。这正是 1.1 节那段报错信息里not functionally dependent这句话的含义。如果 MySQL 检查发现SELECT列确实函数依赖于分组列它就不会报 1055。3.2 主键分组在报表里非常实用函数依赖这个特性在报表场景下特别有用。比如要统计每个用户的第一单时间、最后一单时间、订单总数同时展示用户昵称SELECT user_id, user_nickname, COUNT(*), MIN(created_at), MAX(created_at) FROM orders GROUP BY user_id;假设user_id不是主键但它在users表里是主键而在orders表里只是普通索引那 MySQL 到底认不认这个依赖关键要看orders表自身有没有唯一约束能够确定user_nickname。如果user_nickname是从用户表冗余过来的且orders对user_id没有唯一约束那 MySQL 不会认为user_nickname函数依赖于orders.user_id还是会报错。这里要特别注意函数依赖是基于查询涉及的那张表的约束信息不是基于业务常识。如果你确认“一个用户只有一个昵称”但表里没有唯一约束MySQL 就会以表结构为准。解决办法要么是建唯一约束要么是改用子查询固定映射关系要么就把user_nickname收进ANY_VALUE()告诉 MySQL“这次我确定不需要检查”。3.3 ANY_VALUE 是显式放弃确定性不是修复逻辑ANY_VALUE()经常被当成“万能药”因为它能从语法层面绕开ONLY_FULL_GROUP_BY的检查。但我想再强调一遍它解决的是“能不能查”的问题不解决“查得对不对”的问题。假设你按订单号分组想顺便看一眼该订单属于哪个用户用ANY_VALUE(user_id)没什么问题因为一个订单通常只属于一个用户。但如果你按用户分组想取这个用户的“最近一次登录 IP”用ANY_VALUE(login_ip)就可能返回一个旧 IP因为一个用户有多次登录记录组内有多行取到哪一行是随机的。如果确实要取组内某个“最新”或“最大”的值正确姿势是使用MAX()、MIN()或者在子查询里先排序再取第一行。我这里有个习惯凡是报表要展示的字段必须语义确定拿不准就把结果给业务方确认绝对不允许用不确定的值去填充一个看起来像确定值的字段。4. 同一条group by在8.0里的其他新坑隐式排序、别名、窗口函数除了 1055 报错本身MySQL 8.0 在分组查询里还埋了几个比较容易翻车的点尤其是从 5.7 升级到 8.0 的老项目即使不触发ONLY_FULL_GROUP_BY也可能遇到结果集变了或者语义变了的情况。4.1 GROUP BY 不再帮你排序了这是 8.0 一个非常容易被忽视的行为变化。在 MySQL 5.7 及更早版本里GROUP BY除了分组之外还会隐式地对分组字段做一次排序。也就是说这条 SQLSELECT department_id, COUNT(*) FROM employees GROUP BY department_id;返回结果一般会按department_id升序排列。很多老业务可能并没有写ORDER BY但一直“碰巧”拿到有序结果于是应用层就默认了这种顺序。MySQL 8.0 里这个隐式排序被移除了。同样的 SQL返回的组顺序可能是随机的MySQL 也没有承诺任何顺序。官方文档里写得很清楚不要依赖GROUP BY的隐式排序。所以升级到 8.0 之后凡是需要有序输出的查询一定要显式补上ORDER BY。我遇到过最坑的一次是某个老项目分页接口原来GROUP BY出结果后分页是按返回顺序切片的。升级 8.0 后同一条 SQL 分页结果出现重复和遗漏查了很久才发现是排序丢了加上ORDER BY才恢复。这类问题不会报错但比报错更难排查因为数据表面上“看起来对”实际上顺序已经不稳定了。4.2 SELECT 别名的使用限制MySQL 允许在GROUP BY和HAVING里直接使用SELECT列表里的别名这一点比其他数据库宽松。比如SELECT YEAR(created_at) AS order_year, COUNT(*) FROM orders GROUP BY order_year;这条在 MySQL 里能跑通GROUP BY order_year等价于GROUP BY YEAR(created_at)。放在 8.0 里也一样不算报错。但这里有个坑如果别名跟真实列名冲突MySQL 的行为可能会让人困惑。比如某张表里有一个真实列叫order_year同时你在SELECT里又把YEAR(created_at)起了一个别名order_year此时GROUP BY order_year到底解析的是哪个我建议不要把这个问题的决定权交给数据库版本的解析规则。最稳妥的写法是GROUP BY和SELECT里保持表达式一致直接用原始表达式别依赖别名。也就是写成SELECT YEAR(created_at) AS order_year, COUNT(*) FROM orders GROUP BY YEAR(created_at);这样在任何数据库、任何版本下行为都一致少踩很多坑。4.3 窗口函数和 GROUP BY 的执行顺序MySQL 8.0 引入了窗口函数很多人会把GROUP BY和窗口函数写在一起然后用直觉去理解执行顺序结果就出错。SQL 语句的逻辑执行顺序大致是FROM→WHERE→GROUP BY→HAVING→ 窗口函数 →ORDER BY。也就是说窗口函数是在分组聚合完成之后才进行的。如果你在窗口函数里引用了一个不在分组里的原始列同样会触发 1055 或者得到一个不可预期的结果。比如这条SELECT user_id, SUM(amount) AS total_amount, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS rn FROM orders GROUP BY user_id;这里created_at既没有被聚合也没有出现在GROUP BY里MySQL 8.0 会直接报错因为它无法判断created_at应该取组内的哪一行。有些人觉得“窗口函数应该是最后算的啊为什么不能取原始值”这恰恰是理解偏差窗口函数确实最后算但它作用在“分组后的结果集”上组里可能有多行原始记录未聚合的原始列早就没有位置了。要把这类逻辑写对常规做法是把原始明细和聚合结果分开处理。你可以先用一个子查询做明细层的排序再在外面做聚合SELECT user_id, SUM(amount) AS total_amount, MAX(created_at) AS last_order_time FROM orders GROUP BY user_id;然后再回到订单明细表去关联“最近一单”的订单号。这个套路我在下一章用一个真实案例完整演示。4.4 表达式分组时要把表达式写全8.0 还支持在GROUP BY里使用表达式比如按月份、按日期分组这确实是好功能。但我在项目里经常看到一种写法SELECT里写的是DATE_FORMAT(created_at, %Y-%m)GROUP BY里却写成了DATE(created_at)两个表达式不同MySQL 无法判断它们等价于是结果可能多出很多不必要的分组有些数据库甚至会直接报错。我自己的经验是表达式分组必须保证SELECT和GROUP BY里的表达式完全一致最好把表达式提取出来定义成一个视图或者派生表列这样一眼就能对上。SELECT DATE_FORMAT(created_at, %Y-%m) AS order_month, COUNT(*) FROM orders GROUP BY DATE_FORMAT(created_at, %Y-%m);写全表达式还有一个额外的好处MySQL 8.0 支持函数索引如果你经常按月统计可以给DATE_FORMAT(created_at, %Y-%m)建一个函数索引查询性能会明显改善。5. 一次真实排查过程从报错日志到SQL改写落地前面讲了不少原理这一章我完整还原一次我参与排查的真实场景。那是做一个电商后台的订单用户分析报表需求方给的原始 SQL 大概是这样的SELECT user_id, order_no, SUM(amount) AS total_amount, MAX(created_at) AS last_order_time FROM orders WHERE status paid GROUP BY user_id;一执行1055 报错报错点在第 2 个表达式order_no。这个 SQL 的意图很明显按用户统计已支付订单的总金额和最近下单时间同时想把“最近那单的订单号”一起显示出来。5.1 先明确字段的“角色”我拿到报错后的第一个动作不是改 SQL而是问需求方order_no到底是想要“这个用户任意一个订单号”还是“最近一次下单的订单号”这两个诉求的解法完全不一样。如果是任意订单号用ANY_VALUE(order_no)即可。如果是最近一次下单的订单号不能用ANY_VALUE因为组内订单很多取到的不一定是最后一单。需求方确认要最近一次下单的订单号。于是这条路就不能贪图简单得重新设计查询逻辑。5.2 第一版改法先聚合再关联订单表我第一版改成这样先用子查询按用户聚合得到每个用户的总额和最近下单时间再关联回订单原始表取出最近那单的订单号。SELECT u.user_id, u.total_amount, u.last_order_time, o.order_no FROM ( SELECT user_id, SUM(amount) AS total_amount, MAX(created_at) AS last_order_time FROM orders WHERE status paid GROUP BY user_id ) u LEFT JOIN orders o ON o.user_id u.user_id AND o.created_at u.last_order_time AND o.status paid;这个改写能跑通也没有 1055 报错。但我在测试数据里发现了一个隐患如果一个用户在同一天的同一秒下了两单MAX(created_at)会得到同一个时间值LEFT JOIN条件o.created_at u.last_order_time可能同时匹配到两行导致结果里这个用户出现两行。订单表里这种同一秒并发的概率很低但电商大促期间不是不可能。5.3 第二版改法用窗口函数彻底解决“取最近一单”为了让结果更严谨我后来换了思路先在子查询里用窗口函数ROW_NUMBER()给每个用户的订单按时间倒序标号同时用窗口聚合算出用户总金额然后在外层筛掉非最近一单。SELECT user_id, order_no, total_amount, last_order_time FROM ( SELECT user_id, order_no, amount, created_at, SUM(amount) OVER (PARTITION BY user_id) AS total_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) AS rn FROM orders WHERE status paid ) t WHERE rn 1;细说一下这个写法的思路。内层子查询先把已支付订单明细查出来按user_id分组做窗口聚合SUM(amount)得到每个用户的总金额再用ROW_NUMBER()按下单时间倒序给每个用户的订单编号时间相同时按主键id倒序保证只有一个“第一”。外层WHERE rn 1只保留每个用户最近的一笔订单。这样order_no就是确定的那一单不会再出现重复行。这个方案彻底绕开了GROUP BY的分组语义也就不存在 1055 报错的问题。窗口函数在 MySQL 8.0 里执行效率也不错只要user_id上有索引数据量在百万级以下时响应很快。5.4 这轮排查给我留下的三条经验这轮排查花了大半天主要时间不在改 SQL而在跟需求方确认“任意值”和“最近值”的区别。这里我把经验总结成三条供你参考报错信息里的Expression #N一定要对着SELECT列表逐个数快速定位是哪个字段惹的祸。修复之前先回到业务语义这个字段是分组维度、聚合结果、还是组内单行信息三种角色对应三种方案。ANY_VALUE()只是语法豁免不能帮你拿到“有意义的某一行”凡是确定性的展示需求都用MAX/MIN、聚合子查询或窗口函数来兜底。6. 团队规范与排查清单让group by报错不再反复出现最后这部分没有高深的技术但我觉得比前面所有章节都值钱怎么让 1055 报错在整个团队、整个项目里少发生甚至不发生。6.1 上线前统一 sql_mode 配置很多项目出问题是因为开发环境、测试环境、生产环境的sql_mode不一致。开发环境可能早就被人手动关过ONLY_FULL_GROUP_BYSQL 跑得通一到生产环境就报 1055。我建议团队把数据库配置纳入版本管理用脚本管理而不是手工改服务器配置。每次上线数据库变更时额外执行一条检查SELECT GLOBAL.sql_mode;确保所有环境的sql_mode一致至少都要包含ONLY_FULL_GROUP_BY。让各环境统一用最严格的标准这样开发阶段就能暴露问题而不是拖到生产环境才炸。6.2 排查清单十分钟定位问题如果你现在正在生产环境遇到这个报错可以按这个顺序排查打开报错日志找到 1055 错误找到具体 SQL。数清楚Expression #N对应的是SELECT列表里的哪一个字段。执行SELECT SESSION.sql_mode;确认当前会话开了ONLY_FULL_GROUP_BY。看表结构确认这个字段在业务上应该发挥什么作用如果是组内固定信息检查是否存在主键或唯一索引形成函数依赖能依赖就放心用如果是组内聚合结果用SUM、MAX、MIN等聚合函数包住如果是组内某条记录的属性且确定要任意值用ANY_VALUE如果取最新一条或最大一条用子查询或窗口函数。改完 SQL 后跑一遍相同分组条件的COUNT和明细抽样条数确认结果行数和数据内容都对得上。6.3 让规范落到代码评审里除了技术排查我还想提一个非技术但很关键的点。ONLY_FULL_GROUP_BY报错本质上是 SQL 写法不严谨而这种不严谨靠个人自觉很难根治。我见过不少团队项目的 SQL 规范写在 Wiki 里但没人看。比较有效的做法是把检查嵌入到日常流程里比如代码评审时凡是涉及GROUP BY的 SQL评审人必须检查非聚合列是否都处理了。在测试环境用最严格的sql_mode跑所有数据库变更脚本。如果团队用到 ORM 框架注意框架自动生成的 SQL 也可能触发 1055尤其是 JPA、MyBatis 里的动态 SQL不能因为框架封装了就忽略底层 SQL 的合规性。我个人的习惯是把“group by 必查非聚合列”当成一条潜意识原则写 SQL 的时候只要看到GROUP BY脑子里就自动过一遍筛选清单。这个习惯养成了1055 报错会大幅减少。MySQL 8.0 的这次报错本质上不是 Bug而是数据库在帮我们拦截语义不确定的查询。理解它的规则、知道怎么绕开、也明白什么时候不该绕比单纯“解决了这个报错”更重要。希望这篇文章能让你下次遇到ONLY_FULL_GROUP_BY时不是急着百度而是能自己分析出问题在哪、应该怎么改。