1. ONLY_FULL_GROUP_BY是什么从一次深夜故障说起先说一个几乎每个用过MySQL 5.7/8.0的工程师都经历过的场景本地开发库跑得好好的SQL一到线上就挂报错信息长长一串开头是ERROR 1055 (42000)中间有一句is not in GROUP BY clause结尾永远跟着那句this is incompatible with sql_modeonly_full_group_by。我第一次遇到这个报错是在接手一个老项目的时候。当时我还在想这SQL逻辑明明没毛病怎么换个环境就不能跑了后来才意识到问题根本不在SQL本身而在MySQL的sql_mode设置上。旧环境的MySQL是5.6默认没有开启ONLY_FULL_GROUP_BY模式新环境是5.7这个模式默认打开。同一个SQL两种环境两种命运。那ONLY_FULL_GROUP_BY到底是个什么东西用最简单的说法它是一条MySQL对GROUP BY语法的检查规则。规则要求SELECT列表中的每个非聚合列都必须出现在GROUP BY子句中或者它得能由GROUP BY中的列唯一确定。一旦违反MySQL直接拒绝执行这条SQL返回1055错误。很多刚接触这个报错的人会觉得很恼火觉得是MySQL故意刁难。但等你看懂了这条规则背后的设计逻辑就会明白它其实是在帮你帮你的查询结果变得确定、可预测而不是每次运行都靠运气取数。这篇文章我会把ONLY_FULL_GROUP_BY相关的所有细节都拆开讲。包括它为什么存在、报错信息翻译成人话是什么、在什么情况下会触发、有哪些合法和解法、改配置和改SQL各自有什么代价以及我踩过的坑和总结的排查套路。无论你的身份是后端研发、数据分析还是DBA看完都能直接上手处理。1.1 sql_mode是什么为什么MySQL要搞一套这个sql_mode是MySQL里的一个系统变量它控制MySQL执行SQL时遵守哪些规则、严格到什么程度。你可以把它理解成MySQL的性格开关有的模式开启后MySQL会比较宽容允许你写一些不严谨的SQL有的模式开启后MySQL会变得严格不符合标准的写法直接报错。ONLY_FULL_GROUP_BY只是sql_mode里众多选项中的一个。其他常用的还包括STRICT_TRANS_TABLES事务表严格模式、NO_ZERO_DATE禁止零日期、ERROR_FOR_DIVISION_BY_ZERO除数为零报错等等。查看当前MySQL的sql_mode非常容易一条SQL就能搞定SELECT GLOBAL.sql_mode; SELECT SESSION.sql_mode;在MySQL 8.0的默认安装下你大概率会看到这一长串ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION这里面第一个就是主角。MySQL 5.7.5之后的版本官方把ONLY_FULL_GROUP_BY加进了默认配置。也就是说只要你是5.7.5以上的新装实例不用做任何设置这个模式天然就是开着的。1.2 这个模式到底限制了什么我见过不少人对ONLY_FULL_GROUP_BY的理解只停留在报错了就关掉它这其实很危险。要真正解决问题你得先搞清楚它限制的到底是什么。假设有一张员工表employee结构简化一下---------------------------------------- | id | name | department | salary | ----------------------------------------现在你想按部门统计人数顺便看看部门里某个员工的姓名写了这样一条SQLSELECT department, name, COUNT(*) FROM employee GROUP BY department;在开启ONLY_FULL_GROUP_BY的情况下这条SQL会直接报错。原因很直观你按department分组每个组里可能有几十个员工那SELECT出来的name到底该显示谁的名字是张三、李四还是王五没开这个模式之前MySQL会随手给你挑一个出来。而开了之后MySQL直接告诉你这个查询的语义不明确我不执行。规则细说就是两条出现在SELECT列表中的列如果没被聚合函数包裹就必须出现在GROUP BY子句中或者这个列得满足 函数依赖functional dependency条件也就是它能够由 GROUP BY 中的列唯一确定。这两句话是全文的核心后面所有解决方案都是从这两条规则推导出来的。理解了它你就看懂了1055报错的一半本质。1.3 为什么MySQL要逼你写规范SQL可能你会问以前那种宽松模式用得好好的为什么MySQL非要改这里有个容易被忽视的问题不开启ONLY_FULL_GROUP_BY查询结果是不确定的。MySQL官方文档里明确写过在宽松模式下如果SELECT列表里有非聚合列且不出现在GROUP BY中MySQL会随机从分组里取一条记录作为结果。这个随机是真正的随机它取决于存储引擎怎么读数据、走没走索引、数据在磁盘上的物理顺序甚至跟你查询那一刻的并发状况都可能有关系。放到业务场景里这就非常危险了。假设你写了一条统计订单金额的SQL同样一条SQL昨天跑出来是1000今天跑出来是950两边业务对不上账。这种不确定性在生产上是灾难。所以MySQL 5.7之后把ONLY_FULL_GROUP_BY设为默认本质上是向SQL标准看齐让分组查询的结果变得可预测。Oracle、SQL Server、PostgreSQL这些数据库早就强制这条规则了MySQL算是补了课。2. 报错现场还原1055那条错误信息该怎么读在进入解决方案之前我们先把报错现场完整还原一遍。很多新手看到1055时最懵的不是报错本身而是那串英文到底在说什么。2.1 一步一步触发这个报错按上面的employee表执行SELECT department, name, COUNT(*) FROM employee GROUP BY department;MySQL会返回ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column test.employee.name which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by我们来逐段拆解这段英文Expression #2 of SELECT list说的是SELECT列表里的第几个表达式有问题。这里department是第1个name是第2个所以报错指向第2个字段。is not in GROUP BY clause这个字段没出现在GROUP BY里。contains nonaggregated column test.employee.nametest.employee.name是一个没有聚合的非聚合列。which is not functionally dependent on columns in GROUP BY clause它也不满足函数依赖条件换句话说它没法由department唯一确定。incompatible with sql_modeonly_full_group_by这种写法与当前开启的ONLY_FULL_GROUP_BY模式不兼容。看懂这段报错后排查方向就非常清晰了。哪个字段出现在错误信息里要么把它加进GROUP BY要么用聚合函数包住它要么让分组条件能唯一确定它。2.2 一个生活化的类比帮你彻底理解我经常用班级分组的例子来解释这个问题。假设你是一个老师让全班同学按身高分组一米五组、一米六组、一米七组。现在你要统计每组有多少人COUNT(*)同时你还想在表格里打印一个组员姓名列。你想想这一米七组里可能有十几个同学你让MySQL也就是你的小助手在表格里填姓名它该填谁的名字它不知道你具体想要哪个只好随便填一个。ONLY_FULL_GROUP_BY的规则翻译过来就是要么你明确告诉小助手我就想打印这个组里叫某某个人的名字其他无关要么你就别说姓名这种模糊需求。这就是为什么非聚合列要么进GROUP BY、要么被聚合函数包住的原因。分组之后能确定的只有分组键本身以及基于分组键计算出的统计数据。不在分组键里的普通字段分组后是不可确定的。2.3 一个必须知道的例外函数依赖我刚才反复提到函数依赖这个词这里必须展开讲一下因为它让一部分看起来会报错的SQL其实是合法的。MySQL官方文档中的定义比较啰嗦我用人话说如果GROUP BY里包含某张表的主键那么这张表上的其他列就可以直接写进SELECT列表因为主键能唯一确定一行记录其他列的值也就跟着确定了。举个实际例子SELECT id, name, salary, COUNT(*) FROM employee GROUP BY id;这里id是employee表的主键。虽然name和salary都没出现在GROUP BY子句中也没有被聚合函数包裹但这条SQL在ONLY_FULL_GROUP_BY模式下是可以跑的不报错。因为id唯一确定了一行记录自然也就唯一确定了这一行里的name和salary。这个特性在实际业务中很有用。比如一张订单明细表主键是order_id你想按订单分组统计商品行数同时还想展示订单的客户名称和下单时间就可以直接写SELECT order_id, customer_name, order_time, COUNT(*) AS item_count FROM order_detail GROUP BY order_id;只要customer_name和order_time在order_detail表里并且order_id是该表主键这条SQL就是合法的不用把customer_name和order_time都塞进GROUP BY里。这既保持了分组粒度又让SQL干净。需要注意的是MySQL对函数依赖的识别能力有限。跨表的JOIN场景、表达式计算后的列、多列主键只写了一部分等情况下MySQL不一定认。最稳妥的做法还是非聚合列全部进GROUP BY或者用聚合函数包一层。3. 五种解法改SQL、改配置、用ANY_VALUE到底怎么选看懂了报错接下来就是最实在的部分——怎么处理。我按推荐程度从高到低把五种方案全部列出来每一种都附上适用场景和注意事项。3.1 优先方案改写SQL把非聚合列纳入GROUP BY这是最正规、最不影响全局的方案也是我强烈建议的首选。还是那个按部门统计的SQL你最直接的做法就是让SELECT里的非聚合列全部进入GROUP BYSELECT department, name, COUNT(*) FROM employee GROUP BY department, name;但这里我要提醒一个关键问题把name加进GROUP BY之后分组粒度变了。原来按department分组一个部门是一组现在按department, name分组变成了同一个部门且同一个姓名才是一组。如果部门里有两个同名员工统计结果就和原来不一样了。所以我常说遇到1055报错最忌讳的就是为了凑语法把SELECT里的字段一个不落全塞进GROUP BY。那样改完SQL是能跑了但统计结果可能已经不是你要的了。分组粒度变了业务语义就变了这是比报错更隐蔽的坑。正确的思路是想清楚你每个非聚合列到底想干什么。如果这个列的值在分组内本来就是统一的比如你按department分组而department本身已经决定了department_name那直接把这两个字段都写进GROUP BY即可SELECT department, department_name, COUNT(*) FROM employee GROUP BY department, department_name;这样分组粒度没变通过检查结果也正确。3.2 用聚合函数包裹MAX、MIN还是ANY_VALUE第二种思路是对非聚合列用一个聚合函数包起来让这个字段从无法确定变成可以确定。最常见的写法有三种-- 取分组内任意一个姓名 SELECT department, ANY_VALUE(name), COUNT(*) FROM employee GROUP BY department; -- 取分组内姓名最大值 SELECT department, MAX(name), COUNT(*) FROM employee GROUP BY department; -- 取分组内姓名最小值 SELECT department, MIN(name), COUNT(*) FROM employee GROUP BY department;如果业务上确实不关心分组内取到哪条数据ANY_VALUE()语义上最诚实。它直接告诉MySQL这个字段分组后随便给我一个值就行我就要个样本。MySQL 5.7.5版本开始提供ANY_VALUE()专门解决这类需求。但我要泼一盆冷水用聚合函数包裹不等于业务逻辑正确。如果一个分组内name的值有多个你取到的只是其中一个最终呈现的结果有一定随机性。在报表场景里如果对方问这个姓名为什么显示A没显示B你得能解释清楚自己用的是ANY_VALUE而不是假装这个值有一种必然性。所以我建议ANY_VALUE()只用在两类场景一是明确知道分组内该字段值都一样只是为了满足语法要求二是做数据探查时临时看一眼样本。正式报表和核心统计逻辑尽量别用。3.3 借助主键和函数依赖不动GROUP BY列前文讲了函数依赖这个方案就是它的实践。当你的GROUP BY列包含某张表的主键时同一张表的其他字段可以直接SELECT完全不需要额外处理SELECT order_id, customer_name, order_time, SUM(amount) FROM order_detail GROUP BY order_id;这条SQL里customer_name和order_time依赖主键order_id分组后依然能唯一确定所以合法。这个方案的最大优势是它不会破坏分组粒度也不用写MAX这种语义歪曲的聚合函数SQL表达的业务含义最准确。缺点是MySQL对函数依赖的判断不够聪明。比如你把主键字段做了运算再分组SELECT order_id 1, customer_name, ... GROUP BY order_id 1;同样能按表达式分组但MySQL未必认为customer_name函数依赖于order_id 1这个表达式报错很常见。遇到这种情况要么把表达式也变成物理列要么老老实实用聚合函数。3.4 万能兜底关闭ONLY_FULL_GROUP_BY但不建议无脑关如果你的SQL存量很大、一时改不完或者有历史遗留的老系统需要兼容临时关闭这个模式是很多团队的选择。关闭的方式有三种效果、影响范围、持久性都不一样。第一种会话级关闭只影响当前连接。SET SESSION sql_mode (SELECT REPLACE(sql_mode, ONLY_FULL_GROUP_BY, ));这里用了MySQL的REPLACE函数把当前会话sql_mode值里的ONLY_FULL_GROUP_BY去掉然后重新设置。比手敲一整串模式名靠谱得多不容易漏项。这种方式适合你在一个客户端工具里自己调试关了当下这条连接就生效连接断开再重连又恢复原样。应用连接的运维人员可以在连接初始化时执行一次但每个连接都要执行。第二种全局级关闭影响整个实例。SET GLOBAL sql_mode (SELECT REPLACE(sql_mode, ONLY_FULL_GROUP_BY, ));这个命令对所有新连接生效已有的连接不变。但它是内存级的修改MySQL重启后还是会回到配置文件里的值。临时压测、快速验证时用一下可以别指望它持久。第三种修改配置文件永久生效。这是生产环境最常见的做法。找到MySQL的配置文件Linux下通常是/etc/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnfWindows下是my.ini在[mysqld]段下加一行[mysqld] sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION注意原本默认的ONLY_FULL_GROUP_BY被我拿掉其他模式保留。改完重启MySQL服务systemctl restart mysqldMySQL 8.0还有一个更优雅的持久化方案不用改文件用SET PERSISTSET PERSIST sql_mode (SELECT REPLACE(sql_mode, ONLY_FULL_GROUP_BY, ));这个命令会把设置写到数据目录下的mysqld-auto.cnf文件里重启依然生效。它比手工改my.cnf安全一些因为避免了改错配置文件格式、写错路径等问题。8.0以上的实例我建议优先用它。但我要认真强调一点关闭ONLY_FULL_GROUP_BY只是让报错消失不是让SQL变得正确。关闭之后那些语义模糊的查询结果又回到了随机取一行的状态。你只是把问题从报错变成了数据不确定。如果这条SQL被用于报表统计、对账、下游任务抽取数据一致性风险会由底层的确定性带到业务表面。这也是为什么大厂的核心链路基本都坚持开启这个模式强制开发写出语义明确的SQL。3.5 存量SQL排查实操一夜之间爆出几十条1055怎么办有些团队在从5.6升5.7或者从宽松模式切换到严格模式时会突然发现线上有大量报错。逐个改SQL效率太低这里给你一套排查流程。第一步打开通用日志收集哪些SQL在报1055错误。可以通过MySQL配置临时开启通用日志并输出到表SET GLOBAL log_output TABLE; SET GLOBAL general_log ON;跑一段时间后在mysql.general_log表里查询SELECT * FROM mysql.general_log WHERE command_typeQuery AND argument LIKE SELECT%GROUP BY%;找出来包含GROUP BY的语句再人工甄别。第二步针对每条有问题的SQL按上一节提到的三种SQL改写方案逐一处理。这个阶段别急宁可一条一条来也别为了省事直接去改全局配置。第三步全部改完后把general_log关掉SET GLOBAL general_log OFF;这种先收集、再分析、后改写的方式相比一刀切关模式要稳妥得多既解决了报错也保住了结果的确定性。4. 错误写法与正确写法对照你在生产环境里最可能踩的几个坑很多1055报错不只是语法问题背后是业务理解不到位。这一节我整理了生产环境最常见的一批错误写法以及对应的正确改法。对照看你的SQL大概率能对号入座。4.1 场景一按部门统计人数还要带部门名这是一个最典型的非查询字段难题。-- 错误写法 SELECT dept_id, dept_name, COUNT(*) FROM employee GROUP BY dept_id; -- 正确写法一把全部非聚合列纳入GROUP BY SELECT dept_id, dept_name, COUNT(*) FROM employee GROUP BY dept_id, dept_name; -- 正确写法二部门名由部门ID唯一确定MySQL只要能识别函数依赖也能通过 -- 但要保证 dept_id 是主键且 MySQL 能识别否则还是用方案一更稳为什么我推荐第一种因为对于部门这种实体dept_id本身就唯一决定了dept_name两个字段加进GROUP BY不会改变分组粒度。这里的业务逻辑是安全的。4.2 场景二按部门统计薪资总额还想看员工姓名这个场景比上一个更微妙语义本身就有点矛盾-- 错误写法按部门分组却要展示员工姓名 SELECT dept_id, name, SUM(salary) FROM employee GROUP BY dept_id; -- 这里的 name 到底想要什么部门里的谁没人知道正确的做法是先明确业务意图。如果只是想看部门里任意一个员工的样本用ANY_VALUE(name)如果想看收入最高的员工姓名用MAX(name)显然不合格得借助子查询或窗口函数。总之必须先在业务层面想清楚我到底要什么再决定SQL怎么写顺序不能反。4.3 场景三把主键以外的列全塞进GROUP BY导致结果错乱这是最隐蔽、最容易被忽视的坑。有同事遇到1055报错图省事把SELECT里的非聚合列全都加进GROUP BY。SQL是跑通了但统计结果从500变成了800因为分组粒度被切碎了。举个例子-- 假设要统计每个客户的订单总量 -- 错误修正方式把订单状态也塞进去 SELECT customer_id, order_status, COUNT(*) FROM orders GROUP BY customer_id, order_status;这条SQL变成按客户和订单状态分组一个客户如果没有特殊状态他的订单会被拆成多行。这在报表上就是彻底的错误。正确写法应该是SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;如果确实需要同时看不同状态的分布那就用条件聚合SELECT customer_id, COUNT(*) AS total_orders, SUM(CASE WHEN order_statusPAID THEN 1 ELSE 0 END) AS paid_orders FROM orders GROUP BY customer_id;这才是统计每个客户的订单总量同时看已支付订单数的正确语义。改SQL之前先问清楚自己这条查询最终要回答的问题是什么。4.4 场景四SELECT * GROUP BY 的写法SELECT *在GROUP BY查询里基本等于给自己挖坑。因为*展开后包含了表的所有列大部分非聚合列都不在GROUP BY中也不满足函数依赖报错几乎是必然的。-- 会报错 SELECT * FROM orders GROUP BY customer_id; -- 应该明确列出需要的字段并且处理好非聚合列 SELECT customer_id, MAX(order_time) AS latest_order_time, COUNT(*) AS order_count FROM orders GROUP BY customer_id;在ONLY_FULL_GROUP_BY模式下SELECT *加上GROUP BY的写法就该从团队规范里禁掉。这个模式等于帮你强制把关让你习惯性地明确列出字段。4.5 GROUP BY多个字段时的报错逻辑GROUP BY多个字段时规则同样成立SELECT列表里的非聚合列必须全部出现在GROUP BY子句中。-- 合法 SELECT department, grade, COUNT(*) FROM employee GROUP BY department, grade; -- 不合法grade 没进 GROUP BY SELECT department, grade, COUNT(*) FROM employee GROUP BY department;这里要特别提醒的是GROUP BY的字段顺序不影响合法性但会影响结果的排序倾向。比如GROUP BY department, grade和GROUP BY grade, department分组的逻辑是一样的都是按两个字段的组合分组但结果的展示顺序不同。别为了看起来有序而在GROUP BY里加不需要的字段那样会导致分组粒度一起变。5. 常见问题速查与避坑记录最后这部分是实战中最多被问到的几个问题整理成一个速查表再补几个我自己的踩坑心得。问题原因与处理建议开发环境不报错生产环境报1055最常见的是MySQL版本不一致。5.6默认不开启ONLY_FULL_GROUP_BY5.7.5和8.0默认开启。建议统一各环境的版本和sql_mode配置从根上消除环境差异。修改了my.cnf后重启依然报错检查改的是不是[mysqld]段很多新手错改到[mysql]段或[client]段确认文件路径正确、MySQL加载了这个文件用SELECT sql_mode验证实际生效值8.0还要确认没有mysqld-auto.cnf把配置覆盖掉。只改了GLOBAL为什么业务连接还是报错SET GLOBAL只影响之后新建的连接已存在的连接保持旧设置。应用连接池里的连接如果不释放会一直用旧配置。这种场景要么重启应用要么在应用层连接初始化时统一执行会话级设置。能不能只针对某个数据库或某个用户关闭sql_mode是实例级或会话级变量不支持按库、按用户做不同配置。要做精细化控制只能在应用端给特定连接设置会话级sql_mode。5.6老库升级到5.7/8.0如何平滑过渡先在测试实例开启ONLY_FULL_GROUP_BY用general_log收集报错SQL按第三节的流程全部改写再把生产环境切换。不要在同一天既升级版本又大改SQL风险太集中。用GROUP BY 1,2这种按位置分组可以吗可以但同样受ONLY_FULL_GROUP_BY约束。它只是语法糖引用的是SELECT列表中的第1、2个字段检查规则不变。关闭ONLY_FULL_GROUP_BY后查询速度会更快吗基本不会。这个模式只是做语法合法性检查不参与执行计划优化。关闭它换来的不报错并不等于跑得快。5.1 我的几个独家心得心得一遇到1055报错先别急着改配置先看SQL的业务语义。这是我踩过最大的坑。早年有个报表任务我图快直接关了ONLY_FULL_GROUP_BY结果报表里某个统计字段每天的值都不稳定。后来查了半天才发现就是那个随机取一条的列在作祟。从那以后我处理1055报错的第一步永远是分析SQL而不是动配置。心得二修改sql_mode前一定要把当前值完整备份。我见过不少工程师在弄sql_mode时手一抖把其他模式误删了。特别是把STRICT_TRANS_TABLES也删掉的情况下线上可能会出现更加诡异的数据写入问题。改之前先记录一下当前值改错了能快速还原。用REPLACE函数去删一个模式比手动敲一长串安全得多。心得三主从复制环境要特别注意配置一致性。如果你只改了主库的sql_mode从库还是严格要求那从库在复制某些SQL时可能出现执行失败导致主从同步中断。两边都用同一套配置文件、同步验证才能避免这种问题。心得四ORM框架环境下排查范围要扩大。用MyBatis、Hibernate这类框架时报错的完整SQL经常不直接出现在应用日志里需要打开框架的SQL日志才能看到。排查1055时先看应用的SQL日志定位到具体哪条语句再回到MySQL层面分析效率会高很多。心得五新项目一开始就开启严格模式成本最低。如果项目还在起步阶段建议直接保持MySQL默认的sql_mode团队规范里写明所有GROUP BY查询非聚合字段要么进GROUP BY要么走聚合函数要么明确按主键分组。这样从源头上根治后面不会积累一波难以处理的存量SQL。写在最后从MySQL 5.7开始ONLY_FULL_GROUP_BY从一个可选项变成了默认行为本质上是在强制开发者写出语义更加明确的SQL。很多新技术、新约束刚出现时都很让人恼火但用久了你会发现它其实是把一部分不可预测的风险拦截在了执行之前。我个人在实际操作中的体会是这个模式不是你的敌人而是你的第一位SQL评审人。它用报错的方式逼你停下来想一想——你的分组查询到底想表达什么。想清楚了SQL不仅合法结果也会更可靠。最后再分享一个小技巧如果你在排查一条复杂SQL时不确定哪些字段会触发1055可以先执行一遍EXPLAIN看看执行计划再对照SELECT列表逐一确认非聚合列。大多数时候报错信息里的Expression #N会直接告诉你问题的位置顺着它去改就行。别被那一长串英文唬住它其实比很多开发同学想象的友好得多。