[Err] 1055 - Expression this is incompatible with sql_mode=only_full_group_by

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

A ： MySQL 版本：8.0.15

MySQL报错：[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

报错原因：在SQL_mode中开启了 only_full_group_by 模式。

only_full_group_by的作用：使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行

解决方式一：

修改SQL语句，在group by子句中将需要select的列都写上。

解决方式二： （MySQL重启后会失效）

将SQL_mode中的only_full_group_by 删除。

1、查看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

2、修改SQL_mode

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

解决方式三：【推荐】

修改ini配置文件。

在根目录下找到my.ini文件。在[mysqld]下添加以下配置信息：

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

保存ini文件。

重启MySQL服务。
https://www.jianshu.com/p/c09df83c9805