MySQL5.7.x版本only_full_group_by解决办法

文章中所有操作均是在 MySQL 5.7 版本下进行的

问题展示

在把 MySQL 升级到 5.7 或者更高的版本之后，我这里有个测试的表，如下：

create table t_emp( 
    id   	int(11) AUTO_INCREMENT NOT NULL COMMENT '员工id',
    ename	varchar(50) NULL COMMENT '姓名',
    esal 	varchar(50) NULL COMMENT '工资',
    deptid	int(11) NULL COMMENT '部门id',
    PRIMARY KEY(id)
)；

增加了几条临时数据，执行了一个简单的 group by 语句：

select deptid, ename, max(esal) from t_emp group by deptid;

当执行的时候会报错：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_0.t_emp.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

上面的查询语句其实就是根据部门 id 分组查询统计各个部门工资最高的员工，并显示部门 id，姓名，工资。非常简单的一个 group by 语句，执行的时候报错，大概的意思就是要求：在这个模式下，使用分组查询时，出现在select 字段后面的只能是 group by 后面的分组字段，或使用聚合函数包裹着的字段。

那能不能换种写法？？可以的：

select e.deptid,  e.ename, e.esal 
from 
t_emp e, (select deptid , max(esal) as maxsal from t_emp group by deptid ) t  
where e.deptid = t.deptid and e.esal = t.maxsal;

很明显不会这么写。

问题解决

改变写法其实并不是好的办法，如果项目成型，像这种 group by 语句非常多的时候，改动的也不会少。所以需要解决这个问题：

第一种

使用 any_value() 函数，这个函数对不需要 group by 的字段有效，等同于关闭 only_full_group_by，但是这样难免会遗漏某个字段，而且还得改 SQL 语句，所以不推荐使用。

第二种

暂时性关闭，这个 MySQL 服务重启就失效了。

set @@GLOBAL.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’;

第三种（推荐使用）

找到 MySQL 的配置文件 my.ini（Linux 系统下对应 my.cnf 文件），在 [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 更新这种模式不是没有意义的。对于语义限制都比较严谨的多家数据库，如SQLServer、Oracle等数据库都不支持 select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以从 MySQL 5.7 版本开始修正了这个语义，就是所说的 ONLY_FULL_GROUP_BY 语义。