MySQL ONLY_FULL_GROUP_BY关闭后果：短期便利背后的长期隐患

-- 非标准查询（MySQL 5.7及关闭ONLY_FULL_GROUP_BY时允许）
SELECT department, name, MAX(salary) 
FROM employees 
GROUP BY department;

-- 快速"修复"报错
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- 关闭模式下"可用"的查询
SELECT 
    customer_id,
    order_date,  -- 未聚合，非确定性
    SUM(amount) as total
FROM orders
GROUP BY customer_id;

-- 表结构
CREATE TABLE sales (
    id INT PRIMARY KEY,
    region VARCHAR(20),
    salesperson VARCHAR(50),
    amount DECIMAL(10,2)
);

-- 数据
INSERT INTO sales VALUES
(1, 'North', 'Alice', 100),
(2, 'North', 'Bob', 200),
(3, 'South', 'Charlie', 150);

SELECT region, salesperson, SUM(amount)
FROM sales
GROUP BY region;

-- 错误的收入归属查询
SELECT 
    DATE(created_at) as report_date,
    user_id,  -- 未聚合！
    SUM(amount) as daily_revenue
FROM transactions
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at);

Master: 执行 GROUP BY 查询，随机选择 row A
         ↓
         记录到binlog: 相同的SQL语句
         ↓
Slave:  重放相同SQL，可能选择 row B
         ↓
         数据不一致！

-- 主库执行结果
+--------+-------------+-------+
| region | salesperson | total |
+--------+-------------+-------+
| North  | Alice       | 300   |  -- 随机选了Alice
| South  | Charlie     | 150   |
+--------+-------------+-------+

-- 从库重放结果
+--------+-------------+-------+
| region | salesperson | total |
+--------+-------------+-------+
| North  | Bob         | 300   |  -- 随机选了Bob！
| South  | Charlie     | 150   |
+--------+-------------+-------+

-- 关闭模式下，优化器无法使用某些索引策略
EXPLAIN SELECT department, name, MAX(salary)
FROM employees
GROUP BY department;

-- 依赖隐式排序的脆弱代码
SELECT category, product_name, MAX(price)
FROM products
GROUP BY category
LIMIT 10;  -- 假设返回"每个类别最贵的10个"

当前：MySQL 5.7 + 关闭ONLY_FULL_GROUP_BY
        ↓
升级：MySQL 8.0（更严格）
        ↓
问题：大量查询需要重写
        ↓
决策：再次"临时"关闭或降级
        ↓
结果：永远无法升级，安全漏洞累积

-- 明确告知MySQL"我不关心这个值"
SELECT 
    department,
    ANY_VALUE(name) as any_employee_name,
    MAX(salary) as max_salary
FROM employees
GROUP BY department;

-- 标准SQL写法
SELECT 
    department,
    name,
    MAX(salary) as max_salary
FROM employees
GROUP BY department, name;

-- 获取每个部门薪资最高的员工
WITH ranked AS (
    SELECT 
        department,
        name,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rnk
    FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 1;

阶段1：监控（1-2周）
   └─ 开启general_log，捕获所有GROUP BY查询
   
阶段2：分类（1周）
   ├─ 类别A：需要重写的查询（有业务逻辑错误）
   ├─ 类别B：使用ANY_VALUE()标记（确实不需要确定性）
   └─ 类别C：使用窗口函数重构（复杂场景）

阶段3：灰度（2-4周）
   └─ 测试环境验证 → 生产小流量 → 全量
   
阶段4：固化
   └─ 启用ONLY_FULL_GROUP_BY，监控错误日志

# 示例：Python脚本扫描SQL文件中的问题查询
import re
import sqlparse

def check_group_by_issues(sql_content):
    issues = []
    statements = sqlparse.parse(sql_content)
    
    for stmt in statements:
        if 'GROUP BY' in str(stmt).upper():
            # 简单启发式检查（实际应使用AST解析）
            select_cols = re.findall(r'SELECT(.*?)FROM', str(stmt), re.IGNORECASE)
            group_cols = re.findall(r'GROUP BY(.*?)(?:ORDER|LIMIT|$)', str(stmt), re.IGNORECASE)
            
            if select_cols and group_cols:
                select_items = [c.strip() for c in select_cols[0].split(',')]
                group_items = [c.strip() for c in group_cols[0].split(',')]
                
                # 检查是否有非聚合列不在GROUP BY中
                for item in select_items:
                    if not any(agg in item.upper() for agg in ['SUM(', 'COUNT(', 'MAX(', 'MIN(', 'AVG(']):
                        if item not in group_items:
                            issues.append({
                                'sql': str(stmt)[:100],
                                'issue': f'Column "{item}" may cause indeterminate result'
                            })
    return issues

遇到ONLY_FULL_GROUP_BY报错
        │
        ▼
    是否生产环境？
    ├─ 是 ──▶ 绝对不能关闭，必须重写查询
    │
    └─ 否（开发/测试）
            │
            ▼
        是否有明确迁移计划？
        ├─ 是 ──▶ 可以临时关闭，但需TODO标记
        │
        └─ 否 ──▶ 不要关闭，现在就开始重构

-- 仅当前会话生效，不污染全局配置
SET SESSION sql_mode = (
    SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')
);

-- 在应用配置中明确标记
# application.yml
# TODO: [MYSQL-UPGRADE-2024] Remove this workaround after query refactoring
spring.datasource.url: jdbc:mysql://localhost:3306/db?sessionVariables=sql_mode=...