mysql 5.7之后的使用group by查询参数出现的sql_mode=only_full_group_by错误问题

  首先我在项目中遇到了这个问题，在自己编写代码的时候本机测试没有问题，我使用 group by 查询

       sql : SELECT * FROM ts_product GROUP BY type_id;

      当我写完功能并发到测试服务器之后，问题出现了。

[SQL]SELECT * FROM ts_product GROUP BY type_id;
[Err] 1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘mall.ts_product.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

此处报错了，查询了相关资料说5.7之后mysql自动开启了 only_full_group_by  但是我查询了自己的mysql数据库版本和测试服务器数据库版本分别是5.7.18-log 和 5.7.15-log 一脸懵逼。我想可能是win版本和luinx版本不同吧。

言归正传：ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode，通过这个sql_mode来提供SQL语句GROUP BY合法性的检查，在MySQL的sql_mode是非ONLY_FULL_GROUP_BY语义时。一条select语句，MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式，这个表达式的值可能在经过group by操作后变成undefined。也就是说只能返回count(*)等参数了。如何解决：此处有两种方法：一种就是直接修改数据的sql_mode:(此方法本人未做校验)；

本机使用SELECT @@sql_mode 查询后得出的结果：STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

测试服务器使用SELECT @@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也就是开启了group_by的校验，在5.7以下版本可以使用的group_by就失灵了：

执行SET GLOBAL sql_mode = ”; 把sql_mode 改成非only_full_group_by模式。验证是否生效 SELECT @@GLOBAL.sql_mode 或 SELECT @@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’;

 

第二种方法：除去group_by 以外的参数加上any_value(*) 如：SELECT any_value(`name`) FROM ts_product GROUP BY type_id;

 

如此就可以返回group_by之后的参数，但是返回的参数名变成了any_value(字段名)如此在项目中就无法使用对象来接参数除非修改对象字段名，那么为了接到参数只能再修改回原表的字段名： SELECT any_value(`name`)as `name` FROM ts_product GROUP BY type_id;

 

 

如此就解决了问题。