月度归档： 2019 年 7 月

发布于

Mysql 8.x [Err] 1055 only_full_group_by 问题记录

查询时报错 [Err] 1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘

SELECT c.* ,COUNT(courseId) as cnum FROM center_coursetime c WHERE isDel=0  GROUP BY schoolId ORDER BY c.createTime DESC

解决方法：

登录mysql 后可以查看sql_mode

show variables like ‘%sql_mode%’;

mysql> 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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

其中ONLY_FULL_GROUP_BY 就是引起问题的关键

在 mysql配置文件中/etc/my.cnf中加入

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

重启服务，问题解决