MySQL错误-this is incompatible with sql_mode=only_full_group_by解决方案

项目场景：

  有时候，遇到数据库重复数据，需要将数据进行分组，并取出其中一条来展示，这时就需要用到group by语句。
但是，如果mysql是高版本，当执行group by时，select的字段不属于group by的字段的话，sql语句就会报错。报错信息如下：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘数据库名.表名.字段名’

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

错误代码1055与sql_mode = only_full_group_by不兼容

 

原因分析：

  一、原理层面

这个错误发生在mysql 5.7.5 版本及以上版本会出现的问题：mysql 5.7.5版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。
很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。

  二、sql层面

在sql执行时，出现该原因，简单来说就是：由于开启了ONLY_FULL_GROUP_BY的设置，如果select 的字段不在 group by 中，并且select 的字段未使用聚合函数（SUM,AVG,MAX,MIN等）的话，

那么这条sql查询是被mysql认为非法的，会报错误…

 

验证是否此原因：

  1. 查询数据库版本的语句

SELECT VERSION();

  2. 查看sql_mode的语句

select @@GLOBAL.sql_mode;

## 查询出来的值 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

 

解决方案： 

  方案一：通过sql语句暂时性修改sql_mode

    去掉ONLY_FULL_GROUP_BY，重新设置值

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

   上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据库下执行：

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

  

  问题：

  重启mysql数据库服务之后，ONLY_FULL_GROUP_BY还会出现，所以这只是暂时性的。

 

  方案二：通过配置文件永久修改sql_mode

    1、Linux下修改配置文件

      1）登录进入MySQL，使用命令 mysql -u username -p 进行登陆，然后输入密码，输入SQL：

show variables like ‘%sql_mode’;

      2）编辑my.cnf文件
        文件地址一般在：/etc/my.cnf，/etc/mysql/my.cnf 使用vim命令编辑文件，
        找到sql-mode的位置，去掉ONLY_FULL_GROUP_BY
        然后重启MySQL；
        有的my.cnf中可能没有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

    3）修改成功后重启MySQL服务

service mysql restart

    如果还不行，那么只保留STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 即可 追加内容为：

sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

 

    2、window下修改配置文件

      1）找到mysql安装目录，用记事本直接打开my.ini文件

    2）编辑my.cnf文件，在[mysql]标签下追加内容

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

 

    3）重启mysql 服务

  备注：有些提供了sql_mode的值，却导致重启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

这时，只需要将sql_mode 值中 “NO_AUTO_CREATE_USER” 这个属性去掉即可。

 

附录：

STRICT_TRANS_TABLES：
在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制
 
NO_ZERO_IN_DATE：
在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。

NO_ZERO_DATE：
在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告

ERROR_FOR_DIVISION_BY_ZERO：
在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL

NO_AUTO_CREATE_USER：
防止GRANT自动创建新用户，除非还指定了密码。

NO_ENGINE_SUBSTITUTION：
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常