前段时间重新安装了下MySQL，升级到了8.0，发现执行Group By会报错：
1 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_tl.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
报错原因是：
错误1055 (42000):SELECT list的表达式#1不在GROUP BY子句中，它包含非聚合列’ database_tll .emp。id’在功能上不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容
问题就比较显而易见了：是因为sql_mode=only_full_group_by 引起的。然后查看本地的sql_mode
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 SELECT @@GLOBAL.sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@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 | +-----------------------------------------------------------------------------------------------------------------------+ SELECT @@SESSION.sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@SESSION.sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+
然后我们针对这个sql_mode进行修改：
1 2 3 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';
只是删除了 ONLY_FULL_GROUP_BY。
