[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated colum

【背景】mysql5.7，用group by 查询时抛出如下异常

【错误原因】：

MySQL 5.7.5和up实现了对功能依赖的检测。如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。(在5.7.5之前，MySQL没有检测到功能依赖项，only_full_group_by在默认情况下是不启用的。

【错误现象】：

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘storage_dev.standard.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

【解决思路】

• 首先查询mysql 相关mode
  • show variables like ‘%sql_mode%’;
  • select version(), @@sql_mode;
• 去掉 sql_mode 的 ONLY_FULL_GROUP_BY

1、设置全局sql_mode

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

2、设置session

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

3、设置session

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

4、再次查询mysql 相关mode

【说明】：或直接查询数据即可
【注】若不起作用可以重启MySQL服务
【命令】：

• service mysqld stop 停止mysql服务命令
• service mysqld start 启动mysql服务命令