在接手一套跑了三年的老系统时我碰到了职业生涯里最典型的一次 MySQL 报错潮明明在测试环境跑得好好的 SQL一上生产就抛 ERROR 1055 (42000)字面意思是“this is incompatible with sql_modeonly_full_group_by”。当时第一反应是“哪个同事又把 sql_mode 给改了”结果一查生产库用的是 MySQL 5.7 默认配置压根没人动过。问题出在老系统是 MySQL 5.6 时代写的一堆 GROUP BY 语句钻了宽松模式的空子到了 5.7 全暴露了。 这个 ONLY_FULL_GROUP_BY 就是 MySQL 在 5.7 版本开始默认开启的一种 SQL 模式它的核心约束很直接SELECT 出来的非聚合列必须出现在 GROUP BY 子句里否则 SQL 直接报错。刚接触时觉得这模式纯粹是找麻烦但真实项目里被它救过几次之后我只想说一句这东西不是 Bug是 MySQL 在替你的数据准确性把关。 这篇文章我不会讲太多纯理论重点放在三类内容ONLY_FULL_GROUP_BY 到底在卡什么、真实项目里怎么安全地和它共存、以及迁移老系统时怎么把报错 SQL 批量治理掉。无论你是刚接触 MySQL 的新手还是正在为升级 5.7/8.0 头疼的维护者这篇都能给你一套能直接抄的作业。 ## 1. ONLY_FULL_GROUP_BY 到底在卡什么 ### 1.1 一个 SQL 语句为什么会被拒 先看一个最简单的例子。有一张订单表 sql SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;这个语句没问题因为 SELECT 里的 user_id 出现在 GROUP BY 里SUM(amount) 是聚合函数。再看下面这条 SQL SELECT user_id, order_status, SUM(amount) FROM orders GROUP BY user_id;在 MySQL 5.6 及更早版本里这条 SQL 能跑返回的 order_status 是“该分组内的任意一条记录的 order_status”具体是哪一条完全由存储引擎和查询执行计划决定同一个 SQL 在不同数据量下可能返回不同结果。ONLY_FULL_GROUP_BY 开启后这条 SQL 直接报错原因是 order_status 既不在 GROUP BY 中也没有被聚合函数包裹。从数据库语义来看GROUP BY 之后每个分组已经变成了一行如果 SELECT 中出现了“分组内多行取值不一致的列”数据库根本不知道该显示哪一行的值。MySQL 5.6 的宽松模式本质上是让数据库帮你“随便挑一个”值这种不确定性在报表统计、对账场景里是致命的。1.2 主键函数依赖唯一的特例ONLY_FULL_GROUP_BY 不是一刀切。如果一个列在 GROUP BY 中那么以它为前缀的主键列可以直接出现在 SELECT 里这个叫函数依赖functional dependency。举个复合主键的例子CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id) ); SELECT order_id, product_id, SUM(quantity) FROM order_items GROUP BY order_id;按理说 product_id 不在 GROUP BY 里应该报错。但因为 order_id 和 product_id 共同组成主键只要确定了 order_id对应的所有 product_id 都是唯一的直接查询不会产生歧义。MySQL 在 5.7 版本引入的这个依赖检测机制比很多开发者想象中要智能。实际工作中这一规则可以适当利用但别过度依赖。因为它只对主键和唯一键生效普通索引哪怕唯一性再好也不会被识别。我曾经试图把唯一索引当作主键用结果照样报错查了 MySQL 官方文档才发现这条规则的边界。1.3 默认开启背后的版本差异MySQL 5.7 及以上版本sql_mode 默认包含了 ONLY_FULL_GROUP_BY这是官方为了让数据库行为更贴近 SQL 标准做的一个重大调整。MySQL 5.6 时代默认的 sql_mode 是空字符串也就是完全不限制 GROUP BY 行为。这个差异直接影响升级路径。从 5.6 升到 5.7 的典型场景里最常见的兼容性崩溃不是字符集、不是存储引擎就是这种平时压根注意不到的 SQL 写法问题。2016 年前后一波升级潮中网上满屏都是 ERROR 1055 的提问可以说是 MySQL 历史上最大规模的一次 SQL 行为兼容性事故。2. 接手老项目时的迁移踩坑实录2.1 老系统批量翻车的过程去年我接手一个维护了多年的订单统计系统技术栈是 PHP MySQL 5.6库里有两百多张表统计 SQL 大多是拼接出来的其中不少 GROUP BY 语句极其奔放。项目要迁移到 MySQL 5.7我提前在测试库开 ONLY_FULL_GROUP_BY 模拟结果一跑单日报错日志刷了三百多条。典型的问题 SQL 分三类。第一类是 SELECT 了多余的非聚合列比如按用户统计时顺手把用户的手机号也查出来第二类是 GROUP BY 的列非常少SELECT 却查了五六列第三类是 GROUP BY 用了别名或表达式但 SELECT 用了原始列名。每一条放到 5.6 里都不会吭声放到 5.7 就是一顿报错。2.2 迁移前做一次 SQL 健康检查面对这种存量系统我强烈建议先做一次 SQL 审核而不是等到迁移之后被线上报错追着跑。实操思路是这样的先拿生产库的慢查询日志、通用日志或者从应用层收集最近一个月的 SQL 样本放进开启了 ONLY_FULL_GROUP_BY 的测试库跑一遍。跑的时候不要只盯报错还要看执行计划。很多 SQL 在 5.6 里能走索引到了 5.7 加了 SQL_MODE 之后执行计划变了这种隐藏问题比直接报错更坑。具体操作可以建一个专门做兼容性测试的库把生产库导一份最新的数据过去表结构和索引不变然后开启 ONLY_FULL_GROUP_BY用脚本把收集到的 SQL 全部执行一遍。执行完收集报错 SQL按应用模块分组统计影响范围再决定是改 SQL 还是调整配置。2.3 临时兜底手段修改 sql_mode如果业务实在紧急可以临时把 ONLY_FULL_GROUP_BY 从 sql_mode 中移除。针对当前会话SET SESSION sql_mode (SELECT REPLACE(sql_mode, ONLY_FULL_GROUP_BY, ));针对全局配置SET GLOBAL sql_mode (SELECT REPLACE(sql_mode, ONLY_FULL_GROUP_BY, ));这种方法我建议只在过渡期使用千万别当成长期方案。关掉 ONLY_FULL_GROUP_BY 等于重新回到 5.6 的宽松模式数据统计结果不可控的隐患会一直存在。而且很多云数据库产品默认配置里已经写死了 sql_mode你改了全局配置实例重启之后又被拉回去治标不治本。3. 正确治理姿势改写 SQL 的六种方法3.1 最省事的方案该加的全加进 GROUP BY把 SELECT 中非聚合列全部放进 GROUP BY这是最符合 SQL 语义的做法也是优先选用的方案。下面这条有问题的 SQL SELECT user_id, user_name, SUM(amount) FROM orders JOIN users USING(user_id) GROUP BY user_id;改成SELECT user_id, user_name, SUM(amount) FROM orders JOIN users USING(user_id) GROUP BY user_id, user_name;注意一点如果 user_name 和 user_id 在业务上是一对一的加上去之后数据结果不会变化只是多了一个分组维度性能会受一点影响。如果 user_name 对 user_id 是多对多的那这条 SQL 本身就有业务逻辑问题不应该这么写。3.2 语义明确的聚合用 MAX、MIN 包一层很多场景下选出分组里的某个字段只是为了展示并不关心是哪一条记录的值。比如订单按用户分组想看用户最近一次的订单备注那可以把备注用 MAX 包起来SELECT user_id, MAX(IF(order_time (SELECT MAX(order_time) FROM orders o2 WHERE o2.user_id orders.user_id), remark, NULL)) AS last_remark, SUM(amount) FROM orders GROUP BY user_id;这个写法虽然能过 ONLY_FULL_GROUP_BY但性能很差子查询会在每个分组里再扫一遍。如果对性能敏感我一般建议子查询单独先聚合再 JOIN 到主查询效率能高一个量级。3.3 有争议但实用的 ANY_VALUEMySQL 5.7 提供了 ANY_VALUE 函数可以绕过 ONLY_FULL_GROUP_BY 的检查把不确定的取值直接忽略掉SELECT user_id, ANY_VALUE(user_name), SUM(amount) FROM orders JOIN users USING(user_id) GROUP BY user_id;ANY_VALUE 起作用的原理是告诉 MySQL“这部分偏差我知道你随便取个值就行”。它和 5.6 时代的“娶一个值”本质上是同一种行为只是从隐式变成了显式。我实际用下来ANY_VALUE 适合那种“分组后字段值在业务上必然一致”的场景比如通过 user_id 关联的 user_name如果业务上保证 user_id 唯一对应一个 user_name用 ANY_VALUE 是安全的。但如果字段值在分组内确实不一样ANY_VALUE 返回的值到底是谁是没保证的这时用了就是在埋雷。3.4 先排序后聚合五版本时代的替代写法如果业务语义是“取分组内最新的一条记录的某字段”正确写法是用子查询先排序后取分组。MySQL 5.7 之前没有窗口函数可以这样写SELECT o1.user_id, o1.remark, o1.amount FROM orders o1 JOIN ( SELECT user_id, MAX(order_time) AS max_time FROM orders GROUP BY user_id ) o2 ON o1.user_id o2.user_id AND o1.order_time o2.max_time;这个写法在 MySQL 5.6/5.7/8.0 上都能跑而且能吃上索引性能稳定。唯一的坑是如果同一个 user_id 下有完全相同的 order_time这个方法会查出重复行需要在业务上做去重或在 JOIN 条件里再加一个唯一字段。3.5 窗口函数8.0 时代的利器如果已经升级到 MySQL 8.0窗口函数是解决这类问题的最优解SELECT user_id, remark, amount FROM ( SELECT user_id, remark, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE rn 1;这个方案的优势是逻辑清晰、可读性强而且只扫一次表性能通常优于子查询 JOIN 的方式。我在一个三千多万行的订单表上对比过窗口函数方案比 JOIN 方案快了差不多 20%具体数值和索引设计有关但趋势是明确的。3.6 动 SQL 不如动表结构冗余列与生成列如果某个非聚合列经常和分组一起查询且在分组内必然一致可以考虑把该列冗余进事实表然后用 GROUP BY 冗余列的方式规避检查。比如用户姓名可以冗余到订单表ALTER TABLE orders ADD COLUMN user_name VARCHAR(50) NULL; UPDATE orders o JOIN users u ON o.user_id u.user_id SET o.user_name u.user_name;之后所有按用户的统计都可以直接 GROUP BY user_id, user_name不仅满足 ONLY_FULL_GROUP_BY还省掉一次 JOIN查询性能会明显提升。MySQL 5.7 还支持生成列可以配合索引做到自动维护适合更多复杂的冗余场景。4. 常见问题与排查技巧实录4.1 典型报错速查表报错场景典型错误信息解决方案非聚合列没在 GROUP BY 中ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column把该列加入 GROUP BY或用 ANY_VALUE 包裹GROUP BY 中使用了别名ERROR 1055: Expression #1 of SELECT list is not in GROUP BY clause在 GROUP BY 中使用原始列名或 mysql 8.0 支持的别名写法使用 DISTINCT GROUP BY 混用ERROR 1055 或结果集异常去掉 DISTINCT用纯 GROUP BY 或纯 DISTINCT不要混用聚合字段外嵌套表达式ERROR 1055: Expression #1 of ORDER BY clause is not in GROUP BY clause确保 ORDER BY 的列要么聚合、要么在 GROUP BY 中4.2 排查时最容易被忽略的 ORDER BY 坑ONLY_FULL_GROUP_BY 不只会检查 SELECT 列表还会检查 ORDER BY。因为 ORDER BY 也在查询结果集上投影非聚合字段出现在 ORDER BY 里同样会被拒SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ORDER BY user_name;这条 SQL 在 5.6 里能跑在 5.7 里照样报错。哪怕 user_id 和 user_name 一一对应MySQL 也不认这个账必须把 user_name 加进 GROUP BY或者用 ANY_VALUE(user_name) 包一层。4.3 索引失效疑云模式迁移后性能不升反降ONLY_FULL_GROUP_BY 本身不直接导致索引失效但它逼着你改 SQL。很多人图省事把所有非聚合列全塞进 GROUP BY结果明明之前能走索引的语句变成了 filesort。我在实际项目中遇到过这样的案例订单表 GROUP BY user_id 本来走 user_id 索引很顺利为了凑 ONLY_FULL_GROUP_BY 加了一个 remark 字段进 GROUP BY执行计划立刻变了变成了 Using temporary Using filesort。原因很简单GROUP BY 的列越多索引匹配难度越高MySQL 只能建临时表来排序分组。这种情况我会优先考虑如果 remark 字段只是展示用的用 ANY_VALUE 包一层如果业务上要求严格取最新 remark用 3.5 的窗口函数方案。两种方式都比盲目加列靠谱。4.4 排查流程直接定位问题 SQL 的三板斧生产环境报错时别一行一行翻日志。先执行SELECT sql_mode;确认 ONLY_FULL_GROUP_BY 是否真的打开。如果确认开启接下来三步定位第一步用 SHOW ENGINE INNODB STATUS 配合慢查询日志把报错 SQL 完整捞出来第二步把报错 SQL 里的表结构全部查一遍重点看哪些列是主键、哪些是普通索引、哪些是冗余列第三步用 EXPLAIN 查看执行计划确认 ON 条件和 GROUP BY 列是否都能走索引。这套流程我用了很多次基本能在半小时内定位根因。真正的难点往往不在报错本身而在于同一个 SQL 可能被多条业务链路复用改了一个地方会影响另一个功能改 SQL 之前一定要先全局搜索应用代码里的使用位置。4.5 用漏网之鱼检查定期巡检避免二次爆发很多业务迁移上线之后应用发版还在持续产生新 SQL。如果开发环境用的是宽松模式新 SQL 一旦带上不合规写法上线测试又测不出来等到生产才炸。我建议在测试环境直接全局开启 ONLY_FULL_GROUP_BY让开发阶段就暴露问题。再多说一个自动化小技巧在测试数据库上周期执行一次审计查询从 information_schema 中拉出所有含 GROUP BY 的视图定义静态扫描一下视图里的 SELECT 列是否都包含在 GROUP BY 中。视图是重灾区因为视图定义一旦固定很多人根本没意识到它的 sql_mode 在起作用。5. 写在最后踩过这么多坑之后的真心话ONLY_FULL_GROUP_BY 是我见过的最“反人性”但又最“负责任”的数据库特性。说它反人性是因为它逼着你去改那些本来能跑的查询尤其是存量系统一开就是一个大工程说它负责任是因为它把所有不确定性直接挡在数据库外面绝不让一条语义含糊的结果集蒙混过关。我的态度很明确既然 MySQL 从 5.7 开始默认开启这个模式那就说明官方对 SQL 标准的态度是坚持的后续 8.0 版本也一直在往这个方向强化。做新项目时一定要从一开始就默认开启 ONLY_FULL_GROUP_BY 作为开发约束而不是关掉它来给自己省事。做老项目迁移时也不要把“关模式”当第一方案花点时间把 SQL 一条条改干净后面会省十倍的心。最后分享一个我自己的习惯每次写完一条 GROUP BY 查询我都会下意识地自查一遍——SELECT 里的非聚合列是不是都能被“分组内唯一确定”。这个习惯养成之后ONLY_FULL_GROUP_BY 的报错几乎就再也没在你身上出现过。能写出这条模式的精髓你才算真正理解了 MySQL 分组查询的语义边界。