Mysql:SELECT list is not in GROUP BY clause

操作系统：windows

mysql版本：5.7

详细报错：

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.table_name.item_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

查询默认配置：

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

 

分析：

由于数据库设置了sql_mode=only_full_group_by，表示：

对于group by聚合操作,如果在select中的列没有在group by中出现,那么这个SQL是不合法的并报错

解决：

拷贝默认配置里除了ONLY_FULL_GROUP_BY的配置

拷贝到mysql路径下my.cnf或者My.ini里，不存在新建

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

最后，重启mysql服务