Mysql5.7+的ONLY_FULL_GROUP_BY严格模式使用示例-分组需GROUP BY所有非聚合字段

一、严格模式的核心内涵

1. 模式组成

MySQL 5.7+默认启用的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

2. 设计目的

数据一致性：防止非确定性的查询结果

开发规范：强制编写符合SQL标准的语句

迁移兼容：向ANSI SQL标准靠拢

二、ONLY_FULL_GROUP_BY 规则详解

1. 核心约束（三维度验证）

2. 特殊场景处理

派生表：子查询中的GROUP BY不影响外层

JOIN操作：多表关联时需考虑所有表的字段

视图定义：视图创建时即检查模式合规性

 注：

博客：
https://blog.youkuaiyun.com/badao_liumang_qizhi

三、典型场景示例库

场景1：基础分组查询

-- 错误写法（缺少dept_name分组）
SELECT
    employee_id,
    dept_name,
    AVG(salary)
FROM employees
GROUP BY employee_id;
 
-- 正确写法
SELECT
    employee_id,
    dept_name,
    AVG(salary)
FROM employees
GROUP BY employee_id, dept_name;

场景2：多表关联查询

-- 错误写法（缺少orders.order_date ）
SELECT
    c.customer_name,
    o.order_date,
    COUNT(*) as order_count
FROM customers c
JOIN orders o ON c.id  = o.customer_id
GROUP BY c.customer_name;
 
-- 正确写法
SELECT
    c.customer_name,
    o.order_date,
    COUNT(*) as order_count
FROM customers c
JOIN orders o ON c.id  = o.customer_id 
GROUP BY c.customer_name,  o.order_date;

场景3：函数依赖例外

-- 合法写法（name函数依赖于主键id）
SELECT
    id,
    name,
    MAX(login_time)
FROM users
GROUP BY id;  -- name字段自动关联

四、严格模式管理方案

1. 模式状态检查

-- 查看当前模式
SELECT @@sql_mode;
 
-- 会话级设置
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
 
-- 全局设置（需重启）
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';

2. 生产环境建议

新项目：保持严格模式启用

旧系统迁移：分阶段实施

graph LR
  A[测试环境关闭严格模式] --> B[代码改造]
  B --> C[预发布环境验证]
  C --> D[生产环境逐步启用]

五、业务使用示例

  <select id="pageAlarmEvent" resultMap="PageResultMap">
    select
    ae.id,
    ae.event_name,
    ae.rule_name,
    ae.monitoring,
    ae.warehouse_id,
    ae.level,
    ar.time_interval,
    GROUP_CONCAT(an.title ORDER BY aen.id SEPARATOR ', ') AS notificationIdList,
    ae.notification_time,
    ae.start_time,
    ae.end_time,
    ae.is_next,
    ae.event_status,
    ae.update_user_id,
    ae.update_time
    FROM
    alarm_event ae
    LEFT JOIN
    alarm_rule ar ON ae.rule_id = ar.id
    LEFT JOIN
    alarm_event_notification aen ON ae.id = aen.event_id and aen.is_delete = 0
    LEFT JOIN
    alarm_notification an ON aen.notification_id = an.id
    <where>
      ae.is_delete = 0
      AND ae.company_id=#{companyId}
      <!-- 添加筛选条件 -->
      <if test="queryParam.warehouseId != null and queryParam.warehouseId != ''">
        AND ae.warehouse_id  = #{queryParam.warehouseId}
      </if>
      <if test="queryParam.tankId != null">
        AND ae.tank_id  = #{queryParam.tankId}
      </if>
      <if test="queryParam.level != null">
        AND ae.level  = #{queryParam.level}
      </if>
      <if test="queryParam.eventName != null and queryParam.eventName != ''">
        AND ae.event_name  LIKE CONCAT('%', #{queryParam.eventName}, '%')
      </if>
      <if test="queryParam.eventStatus != null">
        AND ae.event_status  = #{queryParam.eventStatus}
      </if>
    </where>
    GROUP BY
    ae.id,
    ae.event_name,
    ae.rule_name,
    ae.monitoring,
    ae.level,
    ae.warehouse_id,
    ar.time_interval,
    ae.notification_time,
    ae.start_time,
    ae.end_time,
    ae.is_next,
    ae.event_status,
    ae.update_user_id,
    ae.update_time
    <!-- 添加排序条件 -->
    ORDER BY ae.update_time  DESC
  </select>