only_full_group_by模式如何关闭？

Question

在使用MySQL 5.7及以上版本时，启用`only_full_group_by`模式后，执行包含`GROUP BY`但未完全遵循SQL标准的查询会报错。许多开发者在迁移旧项目或执行非严格分组查询时，常遇到“Expression #1 of SELECT list is not in GROUP BY clause”错误。如何安全地关闭`only_full_group_by`模式成为常见问题。需通过修改`sql_mode`系统变量，去除该模式选项，并持久化配置到`my.cnf`文件中，避免服务重启后失效。但需注意，关闭此模式可能引发数据逻辑不一致风险，建议优先优化SQL语义而非直接关闭。

Answer 1

一、问题背景与现象分析

在MySQL 5.7及以上版本中，sql_mode默认启用了ONLY_FULL_GROUP_BY模式。该模式遵循SQL99标准，要求SELECT列表中的所有非聚合字段必须出现在GROUP BY子句中。当开发者执行类似以下查询时：

SELECT user_id, username, MAX(login_time) 
FROM user_logins 
GROUP BY user_id;

尽管user_id是分组依据，但username未被包含在GROUP BY中，MySQL将抛出错误：

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.user_logins.username' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

此类问题在迁移旧系统（如从MySQL 5.6升级）时尤为常见，因旧版本默认未启用此严格模式。

二、技术原理：ONLY_FULL_GROUP_BY 的作用机制

ONLY_FULL_GROUP_BY是sql_mode中的一个关键标志位，其核心目的是防止歧义性聚合查询。例如，在上述例子中，若同一user_id对应多个不同的username值，数据库无法确定应返回哪一个，从而导致结果不可预测。

MySQL 5.7引入该模式作为默认行为，提升了数据一致性保障，但也提高了SQL编写的规范性要求。

可通过如下命令查看当前会话的sql_mode：

SELECT @@sql_mode;

典型输出包含：

'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

三、解决方案路径对比

方案	操作复杂度	持久性	风险等级	适用场景
修改全局sql_mode（临时）	低	否（重启失效）	中	调试/紧急修复
修改my.cnf配置文件	中	是	高	生产环境长期调整
重写SQL以符合标准	高	永久	低	推荐做法
使用ANY_VALUE()函数绕过限制	中	是	中	兼容遗留代码

四、关闭 ONLY_FULL_GROUP_BY 的具体步骤

1. 步骤一：查看当前 sql_mode 配置

   SELECT @@global.sql_mode;

2. 步骤二：临时关闭（仅当前会话）

   SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3. 步骤三：永久关闭需编辑 my.cnf 文件 找到MySQL配置文件（通常位于/etc/my.cnf或/etc/mysql/my.cnf），在[mysqld]段落下添加：

   [mysqld]
   sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

4. 步骤四：重启MySQL服务

   sudo systemctl restart mysql

5. 步骤五：验证配置生效

   SELECT @@global.sql_mode;

   确认输出中不再包含ONLY_FULL_GROUP_BY。

五、替代方案与最佳实践建议

虽然关闭ONLY_FULL_GROUP_BY可快速解决报错问题，但从架构稳健性和可维护性角度出发，更推荐采用语义清晰的SQL重构策略。以下是几种替代方法：

• 显式声明所有非聚合字段：

  SELECT user_id, username, MAX(login_time)
  FROM user_logins
  GROUP BY user_id, username;

• 利用函数表达功能依赖关系： 若确信username在逻辑上由user_id决定，可使用ANY_VALUE()：

  SELECT user_id, ANY_VALUE(username), MAX(login_time)
  FROM user_logins
  GROUP BY user_id;

• 通过子查询或窗口函数提升表达能力： 使用ROW_NUMBER()获取最新登录记录：

  SELECT user_id, username, login_time
  FROM (
      SELECT user_id, username, login_time,
             ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) as rn
      FROM user_logins
  ) t WHERE rn = 1;

六、潜在风险与架构影响分析

关闭ONLY_FULL_GROUP_BY可能导致以下问题：

• 数据不确定性：同一分组下多个值随机返回，造成前后两次查询结果不一致。
• 业务逻辑错误：前端展示用户信息时可能显示错误的昵称或邮箱。
• 调试困难：问题不易复现，尤其在分布式或多实例环境中。
• 技术债累积：阻碍后续向更高版本MySQL（如8.0+）迁移。

此外，现代ORM框架（如Hibernate、MyBatis Plus）生成的SQL也可能受此模式影响，需结合应用层进行联动评估。

七、自动化检测与治理流程图

为系统化应对此类问题，建议建立SQL审查机制。以下为推荐的处理流程：

graph TD
    A[发现 GROUP BY 报错] --> B{是否为遗留系统?}
    B -- 是 --> C[短期: 关闭 ONLY_FULL_GROUP_BY 并标记待优化]
    B -- 否 --> D[重构SQL: 添加缺失字段或使用 ANY_VALUE]
    C --> E[制定迁移计划]
    D --> F[提交代码并通过SQL Review]
    E --> G[定期扫描并替换脆弱SQL]
    G --> H[最终目标: 重新启用 ONLY_FULL_GROUP_BY]
    H --> I[纳入CI/CD静态检查]
    

八、企业级治理策略建议

对于拥有多个微服务和数据库实例的大型组织，建议采取分级治理策略：

• 开发环境：保持ONLY_FULL_GROUP_BY开启，强制开发者编写合规SQL。
• 测试环境：同步生产配置，用于提前暴露问题。
• 生产环境：可根据业务稳定性需求阶段性放宽，但需配合监控告警。
• SQL审计平台：集成performance_schema或第三方工具（如SkyWalking、Arthas）捕获异常查询。
• 自动化脚本：编写Python脚本批量分析慢查询日志中的GROUP BY违规语句。

同时，可在应用启动时通过连接参数自动设置会话级sql_mode，实现细粒度控制：

jdbc:mysql://localhost:3306/db?sessionVariables=sql_mode='STRICT_TRANS_TABLES'