which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

时间: 2023-10-19 18:31:03 浏览: 174

解决MySQL 5.7.9版本sql_mode=only_full_group_by问题

MySQL 5.7.9版本sql_mode=only_full_group_by问题用到GROUP BY 语句查询时com.MySQL.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘col_user_6.a.START_TIME’ which is not functionally dependent on columns in GROUP BY clause; 在MySQL 5.7.9版本中，引入了一个新的默认SQL模式`ONLY_FULL_GROUP_BY`，这个模式旨在增强SQL查询的严谨性，防止在GROUP BY语句中出现未聚合的列，以避免不确定的结果。然而，这也导致了某些在旧版本中能够正常运行的查询在新版本中出现错误。当使用GROUP BY语句时，如果你的SELECT列表中包含不在GROUP BY子句中的非聚合列，且这些列与GROUP BY子句中的列没有函数依赖关系，MySQL会抛出错误，如以下示例所示： ```sql com.MySQL.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘col_user_6.a.START_TIME’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ``` 这个错误表明，你在SELECT语句中选择了`START_TIME`这一非聚合列，但它既不在GROUP BY子句中，也没有与GROUP BY子句中的任何列有函数依赖关系。在`ONLY_FULL_GROUP_BY`模式下，这是不允许的。解决这个问题的方法主要有两个： 1. **修改SQL模式**：你可以通过执行以下SQL语句临时或全局地禁用`ONLY_FULL_GROUP_BY`模式： ```sql -- 临时修改当前会话的SQL模式 SET SESSION sql_mode = ''; -- 或者全局修改SQL模式（需有管理员权限） SET GLOBAL sql_mode = ''; ``` 这将把SQL模式设置为空，从而允许那些在旧版本中可以运行但在新版本中被禁止的查询。 2. **正确编写GROUP BY查询**：而不是禁用`ONLY_FULL_GROUP_BY`，更建议的做法是修正你的SQL查询，确保所有SELECT中的列要么是聚合函数的一部分，要么出现在GROUP BY子句中。例如，如果你希望根据`START_TIME`进行分组，那么你应该这样写： ```sql SELECT a.START_TIME, COUNT(*) FROM col_user_6 a GROUP BY a.START_TIME; ``` 在这个修正后的查询中，`START_TIME`列被包含在了GROUP BY子句中，而`COUNT(*)`是一个聚合函数。了解SQL模式的概念非常重要，因为不同的SQL模式会影响数据库的行为。例如，`STRICT_TRANS_TABLES`模式要求在事务表中插入时数据必须完全有效，`NO_ZERO_IN_DATE`和`NO_ZERO_DATE`模式禁止日期字段包含零值，`ERROR_FOR_DIVISION_BY_ZERO`会在除以零时抛出错误，而非返回NULL，`NO_AUTO_CREATE_USER`阻止通过GRANT语句自动创建用户，`NO_ENGI`（可能是笔误）可能是想指`NO_ENGINE_SUBSTITUTION`，它会阻止MySQL在遇到未安装的存储引擎时自动替换为另一个引擎。 `ONLY_FULL_GROUP_BY`模式是MySQL为了符合SQL标准而引入的，虽然这可能导致一些兼容性问题，但其目的是提高查询的规范性和一致性。因此，理解并适应这种变化对于编写健壮的SQL代码至关重要。如果你的代码需要在多种SQL模式下工作，最好确保它们在`ONLY_FULL_GROUP_BY`模式下也能正常运行。了解如何设置和调整SQL模式是每个数据库管理员和开发者的必备技能。你可以通过`SELECT @@GLOBAL.sql_mode`或`SELECT @@sql_mode`来查看当前的SQL模式设置，并根据需要进行调整。同时，确保在生产环境中谨慎操作，避免对现有应用程序造成影响。通过阅读MySQL文档和相关的技术文章，如“Mysql之SQL Mode用法详解”、“MySQL5.7中的sql_mode默认值带来的坑及解决方法”等，可以深入理解各种SQL模式的作用和应用场景，从而更好地优化和维护你的数据库系统。

"which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" 的意思是在使用GROUP BY语句查询时，SELECT列表中的某些列与GROUP BY子句中的列没有函数依赖关系，这在sql_mode为only_full_group_by时是不兼容的。这个错误通常在MySQL 5.7.9版本中出现，由于sql_mode的设置导致。在sql_mode为only_full_group_by时，MySQL要求在使用GROUP BY语句时，SELECT列表中的非聚合列必须与GROUP BY子句中的列有函数依赖关系。也就是说，SELECT列表中的非聚合列必须在GROUP BY子句中出现或者通过聚合函数进行处理。为了解决这个问题，你可以采取以下几种方法之一: 1. 将sql_mode设置为允许非严格模式，即设置sql_mode为不包含only_full_group_by选项的值。 2. 将SELECT列表中的非聚合列移除，或者将其包含在GROUP BY子句中。 3. 使用聚合函数处理SELECT列表中的非聚合列，例如使用MIN()、MAX()、SUM()等函数。需要注意的是，选择哪种方法取决于你的具体需求和查询语句的逻辑。123 #### 引用[.reference_title] - *1* [神奇的 SQL 之层级 → 为什么 GROUP BY 之后不能直接引用原表中的列](https://blog.csdn.net/weixin_38170853/article/details/102336176)[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_1"}}] [.reference_item style="max-width: 33.333333333333336%"] - *2* [解决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_1"}}] [.reference_item style="max-width: 33.333333333333336%"] - *3* [记一次Group by 查询时的ONLY_FULL_GROUP_BY错误以及后续](https://blog.csdn.net/Abysscarry/article/details/79468411)[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_1"}}] [.reference_item style="max-width: 33.333333333333336%"] [ .reference_list ]

阅读全文

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

相关推荐

MySQL5.7中的sql_mode默认值带来的坑及解决方法

Mysql升级到5.7后遇到的group by查询问题解决

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

RECEIVETIME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

hich is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

of order by clause is not in group by clause and contains nonaggregated column 'information_schema.profiling.seq' which is not functionally dependent on columns in group by clause; this is incompatible with sql_mode=only_full_group_by

1 of order by clause is not in group by clause and contains nonaggregated column 'information_schema.profiling.seq' which is not functionally dependent on columns in group by clause; this is incompatible with sql_mode=only_full_group_by

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

SELECT list is not in GROUP BY clause and contains nonaggregated column 'uvp.u.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by什么意思

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

of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wst_data.goodsstock1_.warningUp' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't1.uid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 中文回答

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

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

1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

1055 - expression #1 of order by clause is not in group by clause and contains nonaggregated column 'information_schema.profiling.seq' which is not functionally dependent on columns in group by clause; this is incompatible with sql_mode=only_full_group_by

最新推荐

绑定halcon显示控件，可实现ROI交互，用于机器视觉领域.zip

PPSSPP-macOS.dmg

session身份认证Demo

黑板风格计算机毕业答辩PPT模板下载

管理建模和仿真的文件

提升点阵式液晶显示屏效率技术

在SoC芯片的射频测试中，ATE设备通常如何执行系统级测试以保证芯片量产的质量和性能一致？

CodeSandbox实现ListView快速创建指南

"互动学习：行动中的多样性与论文攻读经历"

点阵式显示屏常见故障诊断方法