sql运行报错:
[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.h.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因:
select 语句中 test.h.id
没有被包含在GROUP BY子句中,并且也不是一个聚合列(如 COUNT()
, SUM()
, AVG()
等)。这违反了 ONLY_FULL_GROUP_BY
的要求。
ONLY_FULL_GROUP_BY
模式要求在使用GROUP BY
语句时,SELECT列表、HAVING条件或ORDER BY列表中的每个列都必须是聚合函数的一部分,或者必须出现在GROUP BY子句中。
解决方案:
方案一、修改查询以包括所有非聚合列在GROUP BY子句中
例如:SELECT h.id, count(*) FROM test h GROUP BY h.id;
需要在结果中包含 test.h.id
,应该将其添加到GROUP BY子句中。但这通常只在希望按 id
分组时才有意义。
方案二、使用聚合函数
例如:SELECT MAX(h.id) AS max_id, COUNT(*) FROM test h GROUP BY other_column;
如果不需要按 id
分组,但想在结果中包含 id
相关的统计信息,可以使用聚合函数(如 MIN()
, MAX()
)来选择 id
,但这可能不是想要的结果,因为它会返回每组中的最小或最大 id
。
方案三、禁用 ONLY_FULL_GROUP_BY
如果不想修改查询,也可以考虑禁用 ONLY_FULL_GROUP_BY
SQL模式。这可以通过设置全局或会话级别的SQL模式来完成。但需要注意,这可能会隐藏潜在的数据不一致或逻辑错误。
-- 禁用 ONLY_FULL_GROUP_BY(会话级别,仅影响当前会话,其他会话及新建会话不受影响)
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
-- 或者禁用(全局级别,需要谨慎使用,影响新建会话,已存在会话不受影响)
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
如果重启Mysql服务发现ONLY_FULL_GROUP_BY还是存在的,需要修改配置文件,在window系统中是 my.ini,删除ONLY_FULL_GROUP_BY,重启mysql服务即可。
MySQL的配置文件位置可能因安装方式和操作系统而异。常见的配置文件位置包括:
- 在Linux系统中,配置文件通常位于
/etc/mysql/my.cnf
、/etc/mysql/mysql.conf.d/mysqld.cnf
或/etc/my.cnf
。- 在Windows系统中,配置文件可能位于MySQL安装目录下的
my.ini
。
以上三种方案各有优缺,综合考虑选择最佳方案
- 如果需要按
id
分组并获取每个组的统计数据,第一种方案是最合适的。 - 如果正在尝试进行复杂的查询并且确实需要禁用
ONLY_FULL_GROUP_BY
,确保了解这可能对结果产生的影响,并考虑在查询中显式地处理可能的逻辑问题。 - 在大多数情况下,修改查询以符合
ONLY_FULL_GROUP_BY
的要求是最好的做法,因为它有助于保持数据的一致性和准确性。
判断是否设置了ONLY_FULL_GROUP_BY
要判断是否设置了ONLY_FULL_GROUP_BY
SQL模式,可以通过查询MySQL的系统变量sql_mode
来实现。sql_mode
变量包含了当前MySQL服务器启用的所有SQL模式,可以通过查找这个变量中是否包含ONLY_FULL_GROUP_BY
来做出判断。
1、MySQL返回当前会话的sql_mode设置。
返回结果:是一个由逗号分隔的字符串,列出了所有启用的SQL模式,根据结果查看是否存在 only_full_group_by
SELECT @@sql_mode;
2、直接判断当前会话ONLY_FULL_GROUP_BY是否已设置,可以使用FIND_IN_SET函数(如果MySQL版本支持)或者LOCATE函数,或者直接用LIKE操作符来搜索这个模式
SELECT FIND_IN_SET('ONLY_FULL_GROUP_BY', @@sql_mode) > 0 AS is_only_full_group_by_enabled;
SELECT LOCATE('ONLY_FULL_GROUP_BY', @@sql_mode) > 0 AS is_only_full_group_by_enabled;
SELECT @@sql_mode LIKE '%ONLY_FULL_GROUP_BY%' AS is_only_full_group_by_enabled;
3、检查全局sql_mode
设置
SELECT @@global.sql_mode LIKE '%ONLY_FULL_GROUP_BY%' AS is_global_only_full_group_by_enabled;
在MySQL 5.7及更高版本中,ONLY_FULL_GROUP_BY
被默认启用,这有助于提高查询结果的逻辑一致性和准确性。