【项目经验】MySQL中的only_full_group_by模式详解

MySQL中的only_full_group_by模式

一、背景

ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘postscan.verifyDelayLog.auditor’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

以上报错信息为写 SQL 时，使用了 GROUP BY 导致报错，这是因为在 MySQL 5.7 版本中，默认开启了 only_full_group_by 模式，这也导致很多代码在本地跑得好好的，一上线就报错

对于 GROUP BY 聚合操作，如果在 SELECT 中的列，没有在 GROUP BY 中出现，那么这个 SQL 是不合法的，因为列不在 GROUP BY 句中，所以对于设置了这个 mode 的数据库，在使用 GROUP BY 的时候，就要用 MAX(),SUM(),ANT_VALUE() 这种聚合函数，才能完成 GROUP BY 的聚合操作。

二、解决方法

2.1 查看数据库的sql_mode

select @@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

我们去掉 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

但是这样只能解决后面新增的数据库有效，已经存在的数据库无效

还需要在已经存在的每个库中执行：

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';

2.2 修改Mysql的配置文件/etc/my.cnf,添加如下配置

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 5.7 以上的版本中 sql_mode 新加入了 only_full_group_by，导致我们在使用 group by 函数的时候，则会报错

举例说明

SELECT
	* 
FROM
	sys_user_info 
GROUP BY
	sex

解决方法

修改sql语句

SELECT
	any_value ( id ) AS id,
	any_value ( `name` ) AS `name`,
	sex 
FROM
	sys_user_info 
GROUP BY
	sex

对于在 GROUP BY 语句中的字段，可以不用加 any_value，否则需要加上 any_value，但是这种方法对于我们已经开发好的项目，需要大量地去修改 sql 语句，所以很麻烦