mysql版本升级 5.7 only_full_group_by 问题处理

在把MySQL升级到5.7或者更高的版本，一些以前看上去不会出错的group by 操作在这个版本以后就会出现语法报错的情况：

 

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column list which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在这个模式下，我们使用分组查询时，出现在select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。

网上一般的解决方式就是关掉这个模式，即：

 

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

或者改my.ini 配置（如果Mysql 没有这个文件，就把my-default.ini 改成my.ini）
在 [mysqld]和[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’;

看上去这种模式的出现完全是画蛇添足。事实上MySQL更新这种模式不是没有意义的：对于语义限制都比较严谨的多家数据库，如SQLServer、Oracled等数据库都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以从MySQL 5.7版本开始修正了这个语义，就是所说的ONLY_FULL_GROUP_BY语义。
在我遇到这种报错的情况中，最多的就是分组求最值，例如：

 

select id,ename,max(sal) from e group by id;

求各部门最高的工资，其中：id:部门,ename:名字,sal:工资。
或者是这种投机的表示方式：

 

select id,ename,sal from
   ( select * from e order by sal desc ) t group by id;

而之后的版本不再支持了，原因个人认为是消除了子查询的排序作用。而且官方也支持这个判断：（https://bugs.mysql.com/bug.php?id=80131）
如果在不关闭only_full_group_by模式的条件下，是否有速度类似同时又符合规则的办法呢？
答案是存在的：

 

select id ,ename,sal from
           e, (select id ,max(sal) as maxsal from e group by id ) as t  
           where e.id = t.id and e.sal = t.maxsal;

不需要用到构造两表联查，性能也很好。