sql查询出现1055 this is incompatible with sql_mode=only_full_group_by

异常如下


查询出现如下异常:

	SELECT t.name,t.mobile FROM t_test t
	WHERE t.code='1001'
	group by t.time


[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘testdb.t.time’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这句话什么意思呢:
一句话:“错误代码:1055与sql_mode = only_full_group_by不兼容”
“错误代码:1055。SELECT列表的表达式#1不在GROUP BY子句中,并且包含非聚合列’testdb.t.time’,它在功能上不依赖于GROUP BY子句中的列; 这与sql_mode = only_full_group_by不兼容”

原因分析


在MySQL5.7.5后,默认开启了ONLY_FULL_GROUP_BY,所以导致了之前的一些SQL无法正常执行,其实,是我们的SQL不规范造成的,因为group by 之后,返回的一些数据是不确定的,所以才会出现这个错误。

解决办法


第一种

1、登录进入MySQL

linux登录的:mysql -u username -p ,然后输入密码,输入SQL:show variables like ‘%sql_mode’;



2、编辑my.cnf文件

文件地址一般在:/etc/my.cnf,/etc/mysql/my.cnf
找到sql-mode的位置,去掉ONLY_FULL_GROUP_BY
然后重启MySQL;
**有的my.cnf中没有sql-mode,需要加入:
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

注意要加入到[mysqld]下面,我就是加入到其他地方,重启后也不生效,具体的如下图:


3、修改成功后重启MySQL服务,service mysql restart,重启好后,再登录mysql,输入SQL:show variables like ‘%sql_mode’; 如果没有ONLY_FULL_GROUP_BY,就说明已经成功了。



第二种_修改SQL

不一定要禁用ONLY_FULL_GROUP_BY 给定这样的情况,根据mysql文档,“如果name不是t的主键或唯一的NOT NULL列,则此查询无效。在这种情况下,不可以推断出功能依赖性并发生错误。
相反,可以使用此ANY_VALUE(‘my_column_name’) my_column_name 引用mysql文档,
“在这种情况下,MySQL忽略每个名称组中的地址值的不确定性并接受查询。” 使用ANY_VALUE()来引用地址。
因为出现这个问题,基本都是因为这个问题造成的,不确定返回字段可以使用ANY_VALUE(column_name)。
错误sql
	SELECT t.name,t.mobile FROM t_test t
	WHERE t.code='1001'
	group by t.store_time

正确sql
	SELECT ANY_VALUE(t.name),ANY_VALUE(t.mobile) FROM t_test t
	WHERE t.code='1001'
	group by t.time