【背景】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服务命令