MySQL 分组查询错误信息only_full_group_by 报错问题

小半 • 2024年3月5日下午3:32 • 技术漫谈 • 阅读 286

MySQL 分组查询遇到的问题，遇到多次了，就想着记录一下，例如下列情况，根据部门 id 分组，分组后显示部门 id 和用户名

SELECT dept_id,user_name FROM sys_user GROUP BY dept_id;

在 MySQL5.7 及以上版本中，默认开启了 only_full_group_by 模式，这会导致在进行 group by 聚合操作时，如果 select 中的某些列没有在 group by 从句中出现，就会报错。出现这种情况的原因是因为该模式要求严格按照 SQL 标准进行操作，以保证数据的准确性和完整性。

为解决 ONLY_FULL_GROUP_BY 报错问题，可以通过以下方法：

查看sql_mode设置

在 MySQL 中，可以使用如下命令查看当前的 sql_mode 设置：

SELECT @@sql_mode;

该命令输出的结果中，如果包含有 ONLY_FULL_GROUP_BY ，就说明该模式已经开启了。而对于开发环境中的 MySQL5.6 版本，在执行 group by 聚合操作时，并不会开启该模式，因此在开发过程中并不会出现该问题。

去掉 ONLY_FULL_GROUP_BY 模式

如果要解决 ONLY_FULL_GROUP_BY 报错问题，就需要去掉该模式。可以使用如下命令修改全局 sql_mode：

SET @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

需要注意的是，该命令只对新建的数据库有效，对于已存在的数据库，则需要在对应的数据下执行上述命令。另外，MySQL 的配置文件中可能没有设置 sql_mode 字段，可以手动添加：

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

需要将该字段加入到 [mysqld] 配置内，并重启MySQL服务器，才能生效。

修改MySQL配置文件

如果以上方法没有生效，可以尝试修改 MySQL 的配置文件。在 Linux 系统上，可以在 my.cnf 中查找 sql_mode 字段，如果不存在，可以手动添加：

[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

需要重启 MySQL 服务器才能使修改生效。

同理，Windows 也是一样，在 MySQL 安装目录下编辑 my.ini 文件，没有就添加 sql_mode ，有则修改

总结

ONLY_FULL_GROUP_BY 模式的开启是为了确保数据在 group by 聚合操作时的准确性和完整性。然而，对于一些旧版本的 MySQL 数据库，以及一些开发过程中的 sql 语句，可能会因为该模式的存在而报错。为解决该问题，可以通过修改 sql_mode 设置以及 MySQL 的配置文件等方法来去掉 ONLY_FULL_GROUP_BY 模式。