mysql5.7以上报错：Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum

一、前言

在写sql时报错信息：

1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘pms.ru.role_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

二、解决方法

select @@global.sql_mode;

有only_full_group_by值说明开启了group by严谨模式，这是一种严谨的SQL模式，规定SELECT、HAVING、ORDER后的非聚合字段必须和GROUP BY后的字段保持完全一致。

临时解决：

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

永久解决：

/etc/my.cnf
在my.cnf 配置文件中的 [mysqld] 下添加一行指令

[mysqld]
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。