MySQL错误-this is incompatible with sql_mode=only_full_group_by完美解决方案

最新推荐文章于 2024-03-23 09:32:00 发布
原创 最新推荐文章于 2024-03-23 09:32:00 发布 · 354 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #sql #数据库

项目场景：

公司让在原有sql上增加一个公司名称字段。
但是当执行group by时，select的字段不属于group by的字段的话，sql语句就会报错。报错信息如下：

Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column ‘数据库名.表名.字段名’ which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

问题描述：

1原始sql

SELECT
	(
	SELECT
		t2.exam_name 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS exam_name,
	(
	SELECT
		t2.emp_code 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS emp_code,
	(
	SELECT
		t2.emp_name 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS emp_name,
	(
	SELECT
		t2.org_name 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS org_name,
	(
	SELECT
		t2.reply_total_time 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS reply_total_time,
	(
	SELECT
		t2.reply_total_grade 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS reply_total_grade,
	(
	SELECT
		t2.test_my_nums 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS test_my_nums,
	(
	SELECT
		t2.`status` 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS examStatus,
	t3.pass_line AS passLine
FROM
	t_ks_myreplyinfo t1
	LEFT JOIN t_ks_exam t3 ON t1.exam_id = t3.exam_id 
WHERE
	t1.delete_flag =1
GROUP BY
	t1.exam_id,
	t1.emp_code 
ORDER BY
	t3.exam_name,
	t1.emp_code 
	LIMIT 10

加入子查询字段sql

(
		SELECT
			SUBSTRING_INDEX( SUBSTRING_INDEX( t_xt_org_base_info.org_full_name, "|", 2 ), "|",- 1 ) 
		FROM
			t_xt_org_base_info 
		WHERE
			t_xt_org_base_info.org_code = t1.org_code 
		) companyName

加入后变为

SELECT
	(
	SELECT
		t2.exam_name 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS exam_name,
	(
	SELECT
		t2.emp_code 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS emp_code,
	(
	SELECT
		t2.emp_name 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS emp_name,
	(
	SELECT
		t2.org_name 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS org_name,
	(
	SELECT
		t2.reply_total_time 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS reply_total_time,
	(
	SELECT
		t2.reply_total_grade 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS reply_total_grade,
	(
	SELECT
		t2.test_my_nums 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS test_my_nums,
	(
	SELECT
		t2.`status` 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS examStatus,
	t3.pass_line AS passLine,
(
		SELECT
			SUBSTRING_INDEX( SUBSTRING_INDEX( t_xt_org_base_info.org_full_name, "|", 2 ), "|",- 1 ) 
		FROM
			t_xt_org_base_info 
		WHERE
			t_xt_org_base_info.org_code = t1.org_code 
		) companyName 
FROM
	t_ks_myreplyinfo t1
	LEFT JOIN t_ks_exam t3 ON t1.exam_id = t3.exam_id 
WHERE
	t1.delete_flag =1
GROUP BY
	t1.exam_id,
	t1.emp_code 
ORDER BY
	t3.exam_name,
	t1.emp_code 
	LIMIT 10

执行报错：

> 1055 - Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'qnaq.t1.org_code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

翻译：

“错误代码：1055。SELECT列表的表达式＃1不在GROUP BY子句中，并且包含非聚合列’test.t_iov_help_feedback.ID’，它在功能上不依赖于GROUP BY子句中的列; 这与sql_mode = only_full_group_by不兼容” 

原因分析：

一、原理层面
这个错误发生在mysql 5.7.5 版本及以上版本会出现的问题：
mysql 5.7.5版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。
很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。

二、sql层面
在sql执行时，出现该原因，简单来说就是：
由于开启了ONLY_FULL_GROUP_BY的设置，如果select 的字段不在 group by 中，
并且select 的字段未使用聚合函数（SUM,AVG,MAX,MIN等）的话，那么这条sql查询是被mysql认为非法的，会报错误

解决方案：

解决方案一：使用函数ANY_VALUE()包含报错字段

将上述报错语句改成：

SELECT
	(
	SELECT
		t2.exam_name 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS exam_name,
	(
	SELECT
		t2.emp_code 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS emp_code,
	(
	SELECT
		t2.emp_name 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS emp_name,
	(
	SELECT
		t2.org_name 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS org_name,
	(
	SELECT
		t2.reply_total_time 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS reply_total_time,
	(
	SELECT
		t2.reply_total_grade 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS reply_total_grade,
	(
	SELECT
		t2.test_my_nums 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS test_my_nums,
	(
	SELECT
		t2.`status` 
	FROM
		t_ks_myreplyinfo t2 
	WHERE
		t2.exam_id = t1.exam_id 
		AND t2.emp_code = t1.emp_code 
	ORDER BY
		t2.reply_total_grade DESC 
		LIMIT 1 
	) AS examStatus,
	t3.pass_line AS passLine,
	ANY_VALUE ((
		SELECT
			SUBSTRING_INDEX( SUBSTRING_INDEX( t_xt_org_base_info.org_full_name, "|", 2 ), "|",- 1 ) 
		FROM
			t_xt_org_base_info 
		WHERE
			t_xt_org_base_info.org_code = t1.org_code 
		)) companyName 
FROM
	t_ks_myreplyinfo t1
	LEFT JOIN t_ks_exam t3 ON t1.exam_id = t3.exam_id 
WHERE
	t1.delete_flag =1
GROUP BY
	t1.exam_id,
	t1.emp_code 
ORDER BY
	t3.exam_name,
	t1.emp_code 
	LIMIT 10

 可以看到，结果能正常查询了，根据需要自己改查询字段的别名就行.

ANY_VALUE()函数说明：

MySQL有any_value(field)函数，它主要的作用就是抑制ONLY_FULL_GROUP_BY值被拒绝。
这样sql语句不管是在ONLY_FULL_GROUP_BY模式关闭状态还是在开启模式都可以正常执行，不被mysql拒绝。
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据。
官方有介绍，地址：https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

然后就解决了。 

Zhava_zhang
关注
mysql：MySQL-this is incompatible with sql_mode=only_full_group_by错误解决
马句
03-29 1616
一、背景 之前开发中使用本地服务器，本地是mysql5.6，换到线上服务器的时候出现问题 提示MySQL-this is incompatible with sql_mode=only_full_group_by 查询之后发现服务器上mysql版本是mysql5.7 二、问题说明 在网上查找之后知道了原因 一、原理层面 这个错误发生在mysql 5.7 版本及以上...
mysql报错this is incompatible with sql_mode=only_full_group_by解决方案
洛阳泰山的博客
07-26 4799
MySQL报错"this is incompatible with sql_mode=only_full_group_by"是因为在执行查询时，使用了GROUP BY语句但未对所有非聚合列进行聚合处理。这个错误是由于MySQL的sql_mode设置为only_full_group_by导致的。 在MySQL中，sql_mode是用于控制SQL语句执行行为的一个参数。当sql_mode被设置为only_full_group_by时，表示在使用GROUP BY语句时要求对非聚合列进行聚合操作，即所有出现在SE
MySql报错:this is incompatible with sql_mode=only_full_group_by
weixin_44714492的博客
03-23 797
5.7版本默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。在sql执行时，出现该原因： 简单来说就是：输出的结果是叫target list，就是select后面跟着的字段，还有一个地方group。by字段中同时出现，或者是聚合函数的值的话，那么这条sql查询是被mysql认为非法的，会报错误。
MySQL-this is incompatible with sql_mode=only_full_group_by错误解决方案
Bertram的博客
06-24 1821
出现"this is incompatible with sql_mode=only_full_group_by"问题的解决方案
解决MySQL 5.7.9版本sql_mode=only_full_group_by问题
12-16
this is incompatible with sql_mode=only_full_group_by ``` 这个错误表明，你在SELECT语句中选择了`START_TIME`这一非聚合列，但它既不在GROUP BY子句中，也没有与GROUP BY子句中的任何列有函数依赖关系。在`ONLY...
MySQL-this is incompatible with sql_mode=only_full_group_by如何解决
m0_73299531的博客
04-18 467
由于开启了ONLY_FULL_GROUP_BY，如果select的字段不在group by中，并且select的字段没有使用到聚合函数（SUM、AVG、MAX、MIN等）的话，这条sql就会爆。mysql 5.7.5版本以上默认的配置是 sql_mode=”ONLY_FULL_GROUP_BY”，按照”SQL92标准”。修改mysql的配置 my.cnf ，在my.cnf中把sql-mode中的only_full_group_by去掉。修改完成了之后记得重新启动mysql服务。
mysql下this is incompatible with sql_mode=only_full_group_by解决方案
u012283400的专栏
12-06 9962
本地测试没有问题，部署到客户服务器之后报如下错误：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘testID’ which is not functi
解决MySQL-this is incompatible with sql_mode=only_full_group_by 问题（提供window、Linux、docker解决方法和流程）
热门推荐
kerwin的博客
09-18 1万+
解决MySQL-this is incompatible with sql_mode=only_full_group_by 问题（提供window、Linux、docker解决方法和流程）
this is incompatible with sql_mode=only_full_group_by
香草天空Sky的博客
04-25 1万+
5分钟学会MySQL- "this is incompatible with sql_mode=only_full_group_by"错误解决方案 前言： 一、原理层面 这个错误发生在mysql 5.7 版本及以上版本会出现的问题： mysql 5.7版本默认的sql配置是:sql_mode="ONLY_FULL_GROUP_...
解决mysql问题： this is incompatible with sql_mode=only_full_group_by
qq_46940224的博客
03-23 2513
解决mysql问题： this is incompatible with sql_mode=only_full_group_by
SQL报错this is incompatible with sql_mode=only_full_group_by
棒棒糖的糖不含糖的博客
05-29 3250
(在5.7.5之前，MySQL没有检测到函数依赖，默认情况下也没有启用ONLY_FULL_GROUP_BY。拒绝select列表、HAVING条件或ORDER BY列表引用非聚合列的查询，这些列既不在GROUP BY子句中命名，也不依赖于GROUP BY列的功能(由GROUP BY列唯一确定)。（目的是将ONLY_FULL_GROUP_BY关闭，为了便于展示，我将语句用回车分作两行了，实际添加中，无需如此。但很明显，可以看到，我的 order_id 是包含在查询语句中的，所以这个错误描述很明显是不准确的。
解决方案：MYSQL ERROR 1055: GROUP BY clause； this is incompatible with sql_mode=only_full_group_by
咻咻ing的专栏
02-22 488
在使用MySQL执行Group By查询语句时，报错： ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_tl.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_m
错误：this is incompatible with sql_mode=only_full_group_by
weixin_34320724的博客
06-07 317
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'H5APP_WORKREPORT.c.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatib...
MySQL报错：this is incompatible with sql_mode=only_full_group_by
Wind的博客
06-17 323
风吹起了从前，看着天边似在眼前，万般流连~
SQL报错：this is incompatible with sql_mode=only_full_group_by
夏已微凉、
08-16 4494
一、场景二、分析1、原理层面2、sql层面三、解决办法1、前言：查看sql_mode配置2、命令解决3、修改 my.ini 文件四、sql_mode 部分配置解析1、ONLY_FULL_GROUP_BY2、NO_AUTO_VALUE_ON_ZERO3、STRICT_TRANS_TABLES4、NO_ZERO_IN_DATE5、NO_ZERO_DATE6、ERROR_FOR_DIVISION_BY_ZERO7、NO_AUTO_CREATE_USER8、NO_ENGINE_SUBSTITUTION9、PIPE.
mysql报错“this is incompatible with sql_mode=only_full_group_by” 如何解决
最新发布
08-20
MySQL中出现 `sql_mode=only_full_group_by` 报错，主要是由于 MySQL 的 SQL 模式（sql_mode）中启用了 `ONLY_FULL_GROUP_BY` 规则。该规则要求 `SELECT` 查询中的所有非聚合列都必须出现在 `GROUP BY` 子句中，否则会触发错误。以下是几种解决方法： ### 1. 临时修改 SQL 模式 可以在当前会话或全局范围内临时禁用 `ONLY_FULL_GROUP_BY` 模式，以快速解决问题。例如： ```sql -- 临时移除 ONLY_FULL_GROUP_BY 模式 SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); ``` 此方法适用于需要快速修复问题的场景，但重启 MySQL 后设置会失效。 ### 2. 永久修改 SQL 模式 通过修改 MySQL 的配置文件（如 `my.cnf` 或 `my.ini`），永久移除 `ONLY_FULL_GROUP_BY` 模式。在配置文件中添加以下内容： ```ini [mysqld] sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ``` 保存文件后重启 MySQL 服务，以应用新的 SQL 模式设置。 ### 3. 修改查询语句以符合 `ONLY_FULL_GROUP_BY` 要求 如果希望保留 `ONLY_FULL_GROUP_BY` 模式，可以通过调整 SQL 查询语句来避免报错。确保 `SELECT` 子句中的所有非聚合列都出现在 `GROUP BY` 子句中。例如： ```sql -- 错误示例 SELECT department, name FROM employees GROUP BY department; -- 正确示例 SELECT department, MAX(name) FROM employees GROUP BY department; ``` 在此示例中，`MAX(name)` 是一个聚合函数，用于避免因 `name` 未出现在 `GROUP BY` 子句中而报错。 ### 4. 使用 `ANY_VALUE()` 函数 如果某些列不需要聚合，但希望避免报错，可以使用 `ANY_VALUE()` 函数。例如： ```sql SELECT department, ANY_VALUE(name) FROM employees GROUP BY department; ``` 此方法允许在 `SELECT` 子句中包含未出现在 `GROUP BY` 子句中的非聚合列。 ##
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值