🌺The Begin🌺点点关注，收藏不迷路🌺

引言

在MySQL升级到5.7及以上版本后，很多开发者会遇到一个经典错误：

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这个错误源于MySQL的ONLY_FULL_GROUP_BY SQL模式，它对GROUP BY语句的合法性进行了更严格的检查。本文将深入剖析这个问题，并提供多种解决方案。

1. 错误现象复现

1.1 准备测试数据

-- 创建测试表
CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10,2),
    join_date DATE
);

-- 插入测试数据
INSERT INTO employee (name, dept_id, salary, join_date) VALUES
('张三', 1, 8000, '2023-01-15'),
('李四', 1, 9000, '2023-02-20'),
('王五', 2, 7500, '2023-03-10'),
('赵六', 2, 8200, '2023-01-05'),
('田七', 3, 9500, '2023-04-12');

1.2 触发错误的SQL

-- 错误的SQL：查询每个部门的员工姓名和最高工资
SELECT 
    dept_id,
    name,  -- 这里会报错！
    MAX(salary) as max_salary
FROM employee
GROUP BY dept_id;

执行上述SQL会得到错误：

Error Code: 1055. 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_mode=only_full_group_by

1.3 为什么报错

2. 理解ONLY_FULL_GROUP_BY

2.1 什么是ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY是MySQL的SQL模式之一，它要求SELECT列表、HAVING条件或ORDER BY列表中的列，要么是分组列（出现在GROUP BY中），要么是聚合函数（如MAX()、MIN()、AVG()、SUM()等）。

2.2 查看当前sql_mode

-- 查看当前会话的sql_mode
SELECT @@SESSION.sql_mode;

-- 查看全局sql_mode
SELECT @@GLOBAL.sql_mode;

-- 更详细的查看方式
SHOW VARIABLES LIKE 'sql_mode';

输出示例：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

2.3 MySQL 5.7+默认sql_mode

MySQL 5.7及以上版本默认开启了ONLY_FULL_GROUP_BY，这是导致该错误的直接原因。

MySQL版本	默认是否包含ONLY_FULL_GROUP_BY
MySQL 5.6及以下	否
MySQL 5.7	是
MySQL 8.0	是

3. 解决方案详解

3.1 方案一：修改配置文件（永久生效）

第一步：编辑MySQL配置文件

# Linux系统
sudo vim /etc/mysql/my.cnf
# 或
sudo vim /etc/my.cnf

# macOS系统
sudo vim /usr/local/mysql/etc/my.cnf

# Windows系统
# 编辑 C:\ProgramData\MySQL\MySQL Server X.X\my.ini

第二步：在[mysqld]下添加或修改sql_mode

[mysqld]
# 移除ONLY_FULL_GROUP_BY的配置
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 或者使用更宽松的配置（不推荐生产环境）
# sql_mode=

完整的配置示例：

[mysqld]
# 基本配置
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

# 字符集配置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# SQL模式配置（移除了ONLY_FULL_GROUP_BY）
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 其他配置...
max_connections = 500

第三步：重启MySQL服务

# Linux systemd
sudo systemctl restart mysql

# Linux sysvinit
sudo service mysql restart

# macOS
brew services restart mysql

# Windows
net stop MySQL84 && net start MySQL84

第四步：验证修改

SELECT @@GLOBAL.sql_mode;
-- 应该不再包含 ONLY_FULL_GROUP_BY

3.2 方案二：当前会话临时修改

-- 仅对当前会话生效，重启后失效
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- 或完全禁用ONLY_FULL_GROUP_BY
SET SESSION sql_mode = sys.list_drop(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY');

3.3 方案三：全局临时修改

-- 对所有新连接生效，但MySQL重启后失效
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

3.4 方案四：修改SQL语句（推荐）

最规范的解决方案是修改SQL语句，使其符合ONLY_FULL_GROUP_BY规则。

方法1：将非聚合列加入GROUP BY

-- 如果想让每个部门+员工组合成为一组
SELECT 
    dept_id,
    name,
    MAX(salary) as max_salary
FROM employee
GROUP BY dept_id, name;  -- 将name也加入分组

方法2：使用ANY_VALUE()函数（MySQL 5.7+）

-- 如果确实想随机获取一个员工名
SELECT 
    dept_id,
    ANY_VALUE(name) as any_employee_name,  -- 任意选择一个员工名
    MAX(salary) as max_salary
FROM employee
GROUP BY dept_id;

方法3：使用子查询获取明确的值

-- 查询每个部门工资最高的员工
SELECT 
    e.dept_id,
    e.name,
    e.salary as max_salary
FROM employee e
INNER JOIN (
    SELECT dept_id, MAX(salary) as max_salary
    FROM employee
    GROUP BY dept_id
) t ON e.dept_id = t.dept_id AND e.salary = t.max_salary;

3.5 方案五：使用聚合函数

-- 如果是要获取分组后的统计信息
SELECT 
    dept_id,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary
FROM employee
GROUP BY dept_id;

4. 各方案对比

方案	优点	缺点	适用场景
修改配置文件	永久生效，一劳永逸	需要重启服务，可能影响生产	开发环境、测试环境
修改SQL语句	最规范，符合SQL标准	需要修改代码，工作量较大	生产环境、新项目开发
使用ANY_VALUE	快速解决问题	结果不确定，可能有歧义	报表查询、非关键业务
会话级修改	无需重启，快速测试	仅当前会话有效	临时排查、数据分析

5. 各主流数据库的行为对比

数据库	对GROUP BY的处理	是否默认严格模式
MySQL 5.7+	默认严格（ONLY_FULL_GROUP_BY）	✅
MySQL 5.6-	宽松	❌
PostgreSQL	严格，必须符合标准	✅
Oracle	严格，必须符合标准	✅
SQL Server	严格，必须符合标准	✅
SQLite	宽松模式	❌

6. 最佳实践建议

6.1 开发环境配置

在开发环境中，为了快速开发，可以暂时禁用ONLY_FULL_GROUP_BY：

# Docker MySQL 启动脚本
docker run -d \
  --name mysql-dev \
  -e MYSQL_ROOT_PASSWORD=root \
  -p 3306:3306 \
  mysql:8.0 \
  --sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

6.2 生产环境建议

在生产环境中，强烈建议保留ONLY_FULL_GROUP_BY，并编写符合标准的SQL：

-- 正确的写法示例
-- 查询每个部门的最新入职员工
SELECT 
    dept_id,
    name,
    join_date
FROM employee e1
WHERE join_date = (
    SELECT MAX(join_date)
    FROM employee e2
    WHERE e2.dept_id = e1.dept_id
);

-- 而不是这样写（错误的）
SELECT 
    dept_id,
    name,
    MAX(join_date) as latest_join_date
FROM employee
GROUP BY dept_id;  -- 这会产生不确定的结果

6.3 代码审查清单

在代码审查时，注意检查以下几点：

• GROUP BY 语句是否包含了所有非聚合列？
• 是否真的需要禁用ONLY_FULL_GROUP_BY？
• ANY_VALUE的使用是否会导致业务逻辑错误？
• 子查询方案是否性能更优？

7. 扩展知识：sql_mode的其他选项

MySQL的sql_mode除了ONLY_FULL_GROUP_BY外，还有其他重要选项：

-- 严格的sql_mode配置（生产环境推荐）
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

选项	说明
STRICT_TRANS_TABLES	启用严格模式，非法数据值会被拒绝
NO_ZERO_IN_DATE	不允许日期中的月份或天为0
NO_ZERO_DATE	不允许’0000-00-00’作为有效日期
ERROR_FOR_DIVISION_BY_ZERO	除零错误时抛出异常
NO_ENGINE_SUBSTITUTION	指定的存储引擎不可用时抛出错误

8. 实战演练

8.1 场景一：销售报表统计

-- 错误的写法
SELECT 
    product_id,
    product_name,  -- 可能报错
    SUM(sales_amount) as total_sales
FROM sales
GROUP BY product_id;

-- 正确的写法
SELECT 
    s.product_id,
    p.product_name,
    SUM(s.sales_amount) as total_sales
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY s.product_id, p.product_name;

8.2 场景二：用户最后登录时间

-- 错误的写法
SELECT 
    user_id,
    username,  -- 可能报错
    MAX(login_time) as last_login
FROM user_login_log
GROUP BY user_id;

-- 正确的写法
SELECT 
    l.user_id,
    u.username,
    l.login_time as last_login
FROM user_login_log l
JOIN users u ON l.user_id = u.user_id
WHERE (l.user_id, l.login_time) IN (
    SELECT user_id, MAX(login_time)
    FROM user_login_log
    GROUP BY user_id
);

总结

ONLY_FULL_GROUP_BY错误是MySQL升级到5.7后的常见问题，本质是对SQL标准的严格遵循。

解决方案	推荐指数	适用环境
修改配置文件	⭐⭐⭐	开发/测试环境
修改SQL语句	⭐⭐⭐⭐⭐	生产环境
ANY_VALUE函数	⭐⭐	特殊场景
会话级修改	⭐	临时排查

最终建议：

• 开发环境：可以临时禁用，提高开发效率
• 生产环境：保留ONLY_FULL_GROUP_BY，编写标准SQL
• 新项目：从一开始就遵循标准写法

记住：ONLY_FULL_GROUP_BY不是MySQL的"bug"，而是帮你写出更严谨SQL的"特性"！

---

🌺The End🌺点点关注，收藏不迷路🌺