MySQL分组查询报错1055：only_full_group_by限制与解决

1. 这个报错到底在说什么1.1 一次典型的报错现场就拿最常见的场景来说假设有一张员工表你想按部门分组统计每个部门的最高工资同时还想看看这个最高工资是哪个员工拿的于是写出了下面这串 SQLSELECT dept_name, emp_name, MAX(salary) FROM employee GROUP BY dept_name;运行之后 MySQL 直接甩给你一句很长很吓人的话Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column employee.emp_name which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by完整的报错一般就长这样通常还带着错误码 1055。很多新手看到这串英文直接懵了实际上翻成大白话就是你 SELECT 出来的列里有一个列既没有出现在 GROUP BY 里面也没有被聚合函数包起来这在当前 sql_mode 的规则下是不允许的。1.2 报错的完整含义拆解我把这个错误拆开给你看Expression #2 of SELECT list说的是你 SELECT 列表里的第 2 个表达式也就是 emp_name 这一列但错误信息里列的角度是从 1 开始的所以别数懵了。is not in GROUP BY clause这一列没出现在 GROUP BY 里。contains nonaggregated column它还是一个非聚合列不是 SUM、MAX、COUNT 这类聚合函数作用后的结果。not functionally dependent on columns in GROUP BY clause它跟分组的依据列之间不存在函数依赖关系。换句话说按 dept_name 分组之后同一个组里可能存在多个不同的 emp_nameMySQL 不知道该选哪一个展示给你。incompatible with sql_modeonly_full_group_by它不符合当前启用的 only_full_group_by 规则。所以整件事的核心就一句话分组查询的结果集里除了分组列和聚合列你不能随便塞别的列除非你明确告诉 MySQL这个列在组内其实只有一个值也就是所谓的函数依赖。2. 为什么 MySQL 要管得这么宽2.1 SQL 标准里的分组语义很多人觉得 MySQL 是在刁难自己其实不是。这是 SQL 标准里早就定好的规矩只是在 MySQL 5.7 之前一直没有严格执行而已。在 SQL 标准中GROUP BY 的含义非常严格你按某些列分组那么 SELECT 列表里就只能出现这些分组列或者是对其他列做聚合计算的结果。因为分组之后一组数据被压成了一行那一组里其他未被指定的列可能有多个不同的值你让数据库返回哪一个数据库没有义务替你选也没有保证的标准。你去看 MySQL 5.6 及更老的版本默认 sql_mode 里根本没有 only_full_group_by所以上面那种查询是能跑通的。那时候 MySQL 是睁一只眼闭一只眼它自己内部选一个值返回给你完全不告诉你选的是哪个。这种宽松的行为其实埋了很多坑。2.2 不守规矩的代价不知道结果的脏数据我见过一个真实的线上事故。某个报表系统用了老版本的 MySQL开发人员写了一个按 app_id 分组的统计查询SELECT 里带了一堆乱七八糟的字段其中有一个备注字段 remark。在本地测试、测试环境验证都没问题。结果上了生产因为数据量和数据分布不一样同样的 SQL 返回的 remark 完全不是预期的值报表直接给业务方展示了一堆莫名其妙的备注。这就是当年宽松模式的典型问题返回哪个值取决于 MySQL 的存储引擎怎么扫描数据、是否走索引、执行计划的先后顺序这些都可能变。同一个查询今天跑出来是这个值明天换个执行计划就变成另一个值你根本没有确定性可言。MySQL 5.7.5 开始把 only_full_group_by 默认开启本质上就是逼着开发者写出语义更严谨的 SQL你要求返回什么就明确写清楚别让数据库帮你猜。3. 三种解法从治本到治标3.1 治本方案改写 SQL 让它符合规范遇到这个错误第一反应应该是检查自己的 SQL 写得对不对而不是想办法关掉约束。还是用最开始的例子SELECT dept_name, emp_name, MAX(salary) FROM employee GROUP BY dept_name;如果你只是想看每个部门的最高工资顺带知道拿这个工资的员工名字那标准的写法一般有三种第一种把需要展示的列也加进 GROUP BYSELECT dept_name, emp_name, MAX(salary) FROM employee GROUP BY dept_name, emp_name;注意这种写法语义已经变了。它不再表示每个部门的最高工资对应的员工而是每个人在各自部门内的最高工资实际上因为每个人只有一条工资记录就退化成了每个人的工资。如果你想表达的统计口径是每个部门里工资最高的那条员工记录这个写法是不对的。第二种先查出每个部门的最高工资再关联回原表SELECT e.dept_name, e.emp_name, e.salary FROM employee e JOIN ( SELECT dept_name, MAX(salary) AS max_salary FROM employee GROUP BY dept_name ) t ON e.dept_name t.dept_name AND e.salary t.max_salary;这才是原汁原味的部门最高工资对应的员工信息。子查询先分组算出每个部门的最大工资再通过关联把对应的员工记录捞出来。第三种用窗口函数这是 8.0 之后的推荐做法SELECT dept_name, emp_name, salary FROM ( SELECT dept_name, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn 1;窗口函数的好处是写法直观而且能处理同一部门有多人并列最高工资的场景把 ROW_NUMBER 换成 RANK 或者 DENSE_RANK 就能得到不同需求的结果集。3.2 折中方案用 ANY_VALUE() 明确表达随便取一个有些时候确实存在这样一个字段它在同一个分组内虽然有多个记录但值其实都是一样的。比如按订单号分组每一行都带用户 ID因为一个订单就是同一个用户下的单所以用户 ID 在这个分组内必然相同。这种场景下把字段加进 GROUP BY 虽然也能解决问题但会让分组条件变多语义上有点多余还可能影响性能。MySQL 5.7 提供了 ANY_VALUE() 函数专门解决这个需求SELECT order_id, ANY_VALUE(user_id), MAX(order_amount) FROM orders GROUP BY order_id;ANY_VALUE() 的意思就是我不想关心这个字段在组内的每一个具体值反正它们都一样你随便挑一个给我。如果组内确实存在不同的值MySQL 会取最小的那个值返回。这里有一个容易踩的坑如果你在一个组内值不一致的字段上用 ANY_VALUE()得到的结果是随机的、不保证稳定的那等于又回到老版本那种非确定的行为需要你自己确认字段在组内是否真的值一致。3.3 治标方案修改 sql_mode 关闭校验附完整命令如果报错出现在一个你完全无法控制的第三方系统里SQL 都是写死的程序里也没法做改动那你确实只能从数据库层来解决。最常见的做法就是把 only_full_group_by 从 sql_mode 中移除。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 之后的 sql_mode 长这样STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION注意千万别直接 SET sql_mode 把整个 sql_mode 清空。STRICT_TRANS_TABLES 之类的选项还肩负着很多其他数据安全职责清空会让你的数据库陷入更危险的状态以后写入非法数据都没人拦你。4. 修改 sql_mode 的完整实操4.1 查看当前 sql_mode先别急着改动手之前一定要看清楚现状。用下面这两条命令分别查看全局和当前会话的 sql_modeSELECT GLOBAL.sql_mode; SELECT SESSION.sql_mode;也可以直接查询系统变量表SELECT sql_mode;正常情况下这两处的值是相同的。如果不一样说明有人已经把会话级别单独改过排查问题时要分清楚。我建议在安装完 MySQL 之后先把默认 sql_mode 完整记录下来后面每次改动都有据可查这是我在生产环境踩了几次坑之后养成的习惯。4.2 临时修改当前会话立刻生效如果你只是想当前这个客户端连接里允许这种 SQL临时改一下就行SET SESSION sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;这条命令只对当前会话生效客户端断开重连之后就会恢复原样。优点是完全不动全局配置很安全缺点是只要连接一断就失效程序里的连接池或者跑批任务如果换了连接照样报错。4.3 永久修改配置文件加 mysqld 参数想让整个 MySQL 实例彻底接受这种宽松写法需要改配置文件。Linux 下常见路径是 /etc/my.cnf 或 /etc/mysql/my.cnfWindows 下是安装目录里的 my.ini。在 [mysqld] 字段下面加上[mysqld] sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION然后重启 MySQL 服务systemctl restart mysqld重启之后执行 SELECT GLOBAL.sql_mode; 确认 only_full_group_by 已经不在列表里了。这里有两个极易踩的坑提醒你一是配置文件里的 [mysqld] 千万别拼错我见过有人写成 [mysql]那是客户端 client 的配置段加载了也不会生效二是 MySQL 8.0 对配置文件的目录和文件名有严格的权限要求如果 my.cnf 权限是 777 或者属主错误MySQL 会直接忽略这个文件启动也不会报错让你查半天。4.4 Docker 部署的 MySQL 怎么改现在很多人用 Docker 跑 MySQL配置文件修改方式和物理机不太一样。我建议用挂载的方式把宿主机上的配置文件映射进容器docker run -d \ --name mysql8 \ -e MYSQL_ROOT_PASSWORD你的密码 \ -v /opt/mysql/conf/my.cnf:/etc/mysql/my.cnf \ -v /opt/mysql/data:/var/lib/mysql \ -p 3306:3306 \ mysql:8.0关键点在于两个目录映射宿主机的 /opt/mysql/conf/my.cnf 会覆盖容器内的默认配置。容器启动顺序第一次初始化数据目录时就会读取这个配置文件所以如果你要改 sql_mode建议在初始化之前就把配置挂好免得初始化之后产生一堆数据再反悔。如果容器已经在跑了改完宿主机配置文件后执行 docker restart mysql8 即可。需要提醒的是容器内的 MySQL 初始化脚本可能对自定义配置文件做额外的处理实测下来只要配置的属主是 mysql 用户重启都会正常加载。4.5 修改后的验证清单改完配置重启之后不要急着直接跑业务我习惯按下面的顺序做一遍验证执行 SELECT GLOBAL.sql_mode; 确认目标配置已加载。用 root 建一个临时测试库跑一条曾经报错的分组 SQL确认不再报 1055。执行 SHOW VARIABLES LIKE sql_mode; 确认会话级别的值也正常。用业务账号而不是 root 账号再测一遍避免权限差异掩盖问题。5. 实战中的常见问题与避坑心得5.1 改了配置不生效的三种情况我在运维和排查过程中遇到明明改了却没用的情况主要就是三种。第一种是配置文件位置不对。MySQL 启动时会按固定顺序扫描多个配置文件路径你可以用下面命令查看实际生效的配置文件mysqld --verbose --help | grep -A 1 my.cnf这个命令在我的服务器上输出的一长串路径里实际只读取排列在前面的几个。如果你改的文件不在这个列表里那当然怎么改都不生效。第二种是改错了配置段。sql_mode 只能定义在 mysqld 这一段写在别处会被忽略。第三种是只改了会话或者只改了全局连接串里如果写了 init-connect 之类的初始化语句每次新连接建立时会话变量还会被初始化语句覆盖掉这种情况排查起来最隐蔽。我自己就曾经在一个连接池框架里花了大半天最后才发现是框架配置里固化了一行 SET sql_mode每次连接都执行把全局配置给覆盖了。5.2 5.7 和 8.0 的差异MySQL 5.7 和 8.0 在 sql_mode 的默认值上大致相同但修改思路上有一个很大的不同MySQL 8.0 引入了持久化系统变量的功能你可以直接通过 SQL 修改并持久化而不需要改配置文件重启SET PERSIST sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;这条命令的效果等价于修改配置文件并重启修改会写入数据目录下的 mysqld-auto.cnf 文件里MySQL 下次启动时自动加载。使用 SET PERSIST 之后不需要手动重启当前实例立即生效。不过我要说句实在话如果你本来就在管理配置文件还是优先改配置文件一切都在掌控之中SET PERSIST 适合那些不方便重启实例的场景但多了个 mysqld-auto.cnf 隐藏文件团队协作时容易遗漏不熟悉的人看了配置文件总觉得逻辑对不上。5.3 一个真实案例从报错到优化的一次实践最后分享一个我亲手处理过的案例算是把前面内容串起来。有一个统计报表库业务方反馈某条查询一直报 1055。我打开慢查询日志定位到一段非常典型的SELECT 一堆非聚合列 GROUP BY 一列的语句整个 SQL 是从一个老系统迁移过来的作者早就离职了。当时我的处理分三步走。第一步确认业务语义报表上按 app_id 分组展示 PV 数据和最新一条订单金额。第二步核对字段是否在组内唯一查询是事实表同一个 app_id 下有多条订单所以最新一条订单金额根本不能用 MAX(order_amount) 表达而是需要先按 order_time 排序取最新。第三步我把 SQL 改成了窗口函数 子查询只对应用内需要的那几个字段做计算其他多余字段全砍掉。改完之后不仅 1055 没了整体查询时间还从 3.1 秒降到了 0.8 秒。原因是原来的写法分组之后还要对所有非聚合字段做一次隐式的文件排序改成窗口函数后执行计划反而更清晰了。这件事让我印象很深很多人一看到报错就想去改 sql_mode但实际上 SQL 本身的问题解决了性能通常也会跟着提升。5.4 我的几条实操心得根据我的经验给你几条可以直接用的建议新写的代码一律按标准来别依赖关闭 only_full_group_by。你在开发环境关了校验上了生产忘记关线上直接炸。排查报错先看执行计划确认分组字段上的索引情况有时候报错的根源藏在子查询里外层只是被连累。如果你必须在生产环境关闭 only_full_group_by一定走变更流程、写清楚影响面、留好回滚方案并且在测试环境完整回归一遍所有涉及分组查询的功能。遇到老系统改不动 SQL 的情况优先考虑用 ANY_VALUE() 这类语义明确的函数去兼容而不是把所有查询的校验都干掉这是风险最小的过渡方案。老实说这个报错本身并不复杂真正复杂的是它背后暴露出的那种当初随手一写后来埋雷无数的坏味道。每一次报错都是在提醒你SQL 的语义要写清楚返回的结果要可预期。把这条记住了你以后遇到 1055 的第一反应就不是改配置而是去审视自己的查询逻辑。