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

当我们在使用mysql中的group by命令时，有时侯会出现this is incompatible with sql_mode=only_full_group_by错误，这是什么原因造成的呢？下面我们来分析分析

1.从原理层面分析

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

2.从sql层面分析

        在sql执行时，出现该原因：就是输出的结果是叫target list，就是select后面跟着的字段;还有一个地方group by column，就是 group by后面跟着的字段。由于开启ONLY_FULL_GROUP_BY的设置，所以如果一个字段没有在target list 和group by字段中同时出现，或者是聚合函数的值的话，那么这条sql查询是被mysql认为非法的，会报错误

查看sql_mode的语句:

mysql> SHOW SESSION VARIABLES;
 
mysql> SHOW GLOBAL VARIABLES;
 
mysql> select @@sql_mode;

可见session和global 的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 说明：
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.

3.解决方案
3.1 方案一

sql语句暂时性修改sql_mode,语句如下：

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

或者

mysql> set 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';
mysql> set session 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数据库服务之后，ONLY_FULL_GROUP_BY还会出现

 3.2 方案二

永久解决方案：

        需修改mysql配置文件，通过手动添加sql_mode的方式强制指定不需要ONLY_FULL_GROUP_BY属性， my.cnf位于etc文件夹下，vim下光标移到最后，语句添加如下：

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=""

重启mysql服务，顺利解决

3.3 当使用云数据库时，解决方法

找到数据库管理，参数设置，修改参数sql_mode中的数据，把ONLY_FULL_GROUP_BY去掉