Mysql 无法分组,遇见XXX SELECT list is not in GROUP BY clause and contains nonaggre的问题
方法一:不重启Mysql 设置,此方法对当前操作的电脑有效;
1. 查看Mysql 版本
我的版本大于mysql 5.7.5 是默认情况下不启用ONLY_FULL_GROUP_BY
2. 打开navcat,
用sql查询:
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
3. 去掉ONLY_FULL_GROUP_BY,重新设置值
set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
注:如果没有效果,多执行一遍上述操作