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

最新推荐文章于 2024-09-24 06:15:57 发布
最新推荐文章于 2024-09-24 06:15:57 发布
阅读量307 收藏
点赞数
文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Zhava_zhang/article/details/130249604
版权

项目场景：

公司让在原有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-this is incompatible with sql_mode=only_full_group_by错误解决方案
Bertram的博客
06-24 1194
出现"this is incompatible with sql_mode=only_full_group_by"问题的解决方案
mysql 出现in GROUP BY clause； this is incompatible with sql_mode=only_full_group_by问题
一个抽风的女Ren的博客
07-07 1559
部署项目使用到了mysql 5.7.x版本时出现以下问题 Caused by: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT type is not in GROUP BY clause and contains nonaggregated column ‘Tuser_type’ which is not functionally dependent on columns in GROUP BY clause; this is inco
解决MySQL this is incompatible with sql_mode=only_full_group_by 问题例子解析
最新发布
IT老农民的博客
09-24 600
的要求，因为这是最安全的做法，可以避免潜在的数据一致性问题。因此，在禁用这个模式之前，最好先评估一下它对你的查询逻辑和结果的影响。子句中明确指定，或者是聚合函数的一部分。子句中，要么作为聚合函数的一部分。可能会让你的查询结果变得不确定，因为MySQL将不再强制要求。函数可以用来选择每个分组的任意值，这在某种程度上可以绕过。，你可以将更改永久化到MySQL的配置文件中。，这是因为在MySQL 8.0及以上版本中，或者，你可以修改MySQL的配置文件（例如。子句中的列，可以使用聚合函数，如。
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解决方案
u012283400的专栏
12-06 9701
本地测试没有问题，部署到客户服务器之后报如下错误：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 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...
this is incompatible with sql_mode=only_full_group_by解决方案
热门推荐
weixin_43818327的博客
07-04 1万+
mysql查询时group by 报错：this is incompatible with sql_mode=only_full_group_by解决方案
解决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解决方法和流程）
MySql报错:this is incompatible with sql_mode=only_full_group_by
weixin_44714492的博客
03-23 625
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
qq_46940224的博客
03-23 963
解决mysql问题： this is incompatible with sql_mode=only_full_group_by
SQL报错this is incompatible with sql_mode=only_full_group_by
棒棒糖的糖不含糖的博客
05-29 3011
(在5.7.5之前，MySQL没有检测到函数依赖，默认情况下也没有启用ONLY_FULL_GROUP_BY。拒绝select列表、HAVING条件或ORDER BY列表引用非聚合列的查询，这些列既不在GROUP BY子句中命名，也不依赖于GROUP BY列的功能(由GROUP BY列唯一确定)。（目的是将ONLY_FULL_GROUP_BY关闭，为了便于展示，我将语句用回车分作两行了，实际添加中，无需如此。但很明显，可以看到，我的 order_id 是包含在查询语句中的，所以这个错误描述很明显是不准确的。
错误：this is incompatible with sql_mode=only_full_group_by
weixin_34320724的博客
06-07 226
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 ERROR 1055: GROUP BY clause； this is incompatible with sql_mode=only_full_group_by
咻咻ing的专栏
02-22 393
在使用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
MySQL报错：this is incompatible with sql_mode=only_full_group_by
Wind的博客
06-17 240
风吹起了从前，看着天边似在眼前，万般流连~
SQL报错：this is incompatible with sql_mode=only_full_group_by
夏已微凉、
08-16 4170
一、场景二、分析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.
this is incompatible with sql_mode=only_full_group_bymysql
08-19
这个错误是因为在MySQL数据库中，开启了sql_mode=only_full_group_by的设置时，如果select的字段不在group by中，并且没有使用聚合函数（如SUM、AVG、MAX、MIN等），那么该查询就被认为是非法的，会报出这个错误。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* [MySQL错误-this is incompatible with sql_mode=only_full_group_by完美解决方案](https://blog.csdn.net/u012660464/article/details/113977173)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *3* [解决MySQL 5.7.9版本sql_mode=only_full_group_by问题](https://download.csdn.net/download/weixin_38697063/13699570)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值