MySQL错误提示:sql_mode=only_full_group_by完美解决方案

MySQL错误提示：sql_mode=only_full_group_by 是在 MySQL 5.7 版本中引入的一个新特性。当开启该模式时，如果使用了GROUP BY语句但是SELECT语句中的列名没有在GROUP BY中出现，或者在SELECT语句中使用了聚合函数，但是列名并不在GROUP BY语句中，则会抛出“1055 error - 'XXXX' isn't in GROUP BY”的错误提示。这也导致了很多老版本MySQL数据库运行原有的SQL脚本时报错的问题。

下面介绍 “MySQL错误提示:sql_mode=only_full_group_by”的完美解决方案：

一、查看当前的sql_mode设置

首先，我们需要查看当前MySQL数据库的SQL_MODE设置。在MySQL客户端中输入以下命令：

SELECT @@SESSION.sql_mode;

会返回如下的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

这就是当前MySQL数据库的SQL_MODE设置，其中包含了 “ONLY_FULL_GROUP_BY” 模式。

二、修改MySQL配置文件

在MySQL的配置文件中修改sql_mode即可解决该问题。

首先，编辑MySQL的配置文件my.cnf或my.ini（Windows中）。在Ubuntu服务器中可以使用以下命令查找my.cnf的位置：

sudo find / -name "my.cnf"

然后，找到配置文件中的 [mysqld] 模块下的sql_mode参数，并将其修改为：

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服务，操作完毕。

例如，在CentOS服务器中，MySQL的配置文件为 /etc/my.cnf，则编辑该文件：

sudo vi /etc/my.cnf

将如下的代码：

[mysqld]
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"

修改为：

[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服务：

sudo systemctl restart mysqld

三、示例说明

示例1：新项目开发问题

当开发一个新项目时，使用了groupby语句以及聚合函数，为了查询按照时间统计结果，我们先使用groupby按照时间分组但是并不需要对时间字段再做聚合操作，此时依据 sql_mode=only_full_group_by。会收到如下错误提示：

1055 error - 'Date' isn't in GROUP BY

解决方案如下：

1. 通过执行 SELECT @@SESSION.sql_mode; 命令查看SQL模式。
2. 在 MySQL 配置文件 my.cnf 或 my.ini 里（具体路径取决于你的环境和MySQL的安装位置）查找 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. 重启 MySQL 服务器，如 systemctl restart mysqld.service

示例2：老项目运行问题

有一个老项目的部分SQL语句不符合SQL Mode的要求，执行SQL语句会抛出 “1055 error - 'XXXX' isn't in GROUP BY”的错误提示，而且为了维护方便并不想修改SQL语句，可以采用更改MySQL配置文件的方法来解决问题。我们可以在MySQL配置文件中增加下面两行的 SQL_MODE，来适应老项目运行所需 SQL_MODE 的模式：

[mysqld]
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

将 "only_full_group_by" 加回 sql_mode 中；这样在 MySQL 安装服务之后就能自动设置 SQL_MODE 的值。这样，老项目的SQL语句就可以正常运行，而开发新项目时由于也将 only_full_group_by 加回来，就避免出现 sql_mode=only_full_group_by 的错误提示。