Mysql报错处理

一、mysql5.7使用group by语句：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated

报错信息

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated 
column 'company.emp.ename' which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

原因分析

mysql 5.7以后默认启用sql_mode=only_full_group_by模式特性
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，
也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

解决方法

方法一：登录Mysql

--1.查看模式中是否有only_full_group_by
select @@global.sql_mode;
--2.删除only_full_group_by
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';

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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

方法二：修改mysql的配置文件 /etc/my.cnf 或my.ini ，在尾部添加以下内容，重新启动 mysql 即可

#Linux 用户
find / -name my.cnf

vi /etc/my.cnf

service restart mysqld

# 配置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

endl