解决mysql语法错误：this is incompatible with sql_mode=only_full_group_by

最新推荐文章于 2024-03-23 09:32:00 发布

原创最新推荐文章于 2024-03-23 09:32:00 发布 · 1.5k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sql_mode #ONLY_FULL_GROUP_BY #any_value #mysql5.7 group by #this is incompatible with sql_

mysql 专栏收录该内容

7 篇文章

订阅专栏

本文详细介绍了Mysql5.7版本之后对sql_mode所做的修改，其中ONLY_FULL_GROUP_BY成为默认模式之一。阐述了ONLY_FULL_GROUP_BY模式的作用及原理，并通过实例展示了如何避免因ONLY_FULL_GROUP_BY模式而导致的错误。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Mysql5.7版本之后对sql_mode做了修改，其中 ONLY_FULL_GROUP_BY 成为了默认模式之一。

执行以下命令，可查看sql_mode默认模式：

select @@GLOBAL.sql_mode;
-- 或者
select @@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

像oracle数据库采用的就是开启ONLY_FULL_GROUP_BY 模式。

简单理解ONLY_FULL_GROUP_BY模式，用于限制group by语法，要求select的字段要和group by的字段一致，保证select字段的唯一性。否则会造成搜索引擎不知道该返回哪一条。

比如有一张temp表：

group_id	name	age
1	AA	34
2	BB	43
2	CC	12
3	DD	45

执行SQL：select group_id,name from temp group by group_id

返回结果中group_id为2的数据肯定会变成只有一条，但对应的name字段的值不唯一性，所以会抛出异常信息：

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

解决方案：

方案一：

使用mysql函数 any_value(field) 用于包含非分组字段的出现

比如：select group_id,any_value(name) from temp group by group_id

这样就不用关闭ONLY_FULL_GROUP_BY 模式

方案二：

修改mysql配置文件my.ini (linux系统修改my.cnf)，把 ONLY_FULL_GROUP_BY 从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]中，注意：需要重启mysql服务生效。