1055 - Expression #1 of SELECT list is not in GROUP BY...sql_mode=only_full_group_by

于 2023-12-22 10:09:27 发布
阅读量468 收藏 6
点赞数 9
文章标签： 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_75184535/article/details/135146453
版权
文章讲述了作者在使用MySQL8时遇到GROUPBY语句错误，通过设置sqlmode为STRICTTRANSTABLES,NOENGINE_SUBSTITUTION并关闭重连解决了问题。

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

mysql数据库版本问题(mysql8报的错，mysql5.7就没问题)：

select * from adopt group by animal_id;

以上语句报错：

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xmanimalmanager.adopt.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sqlmode=onlyfullgroupby

,在navicat新建查询中输入以下语句:

SET GLOBAL sqlmode='STRICTTRANSTABLES,NOENGINE_SUBSTITUTION';

navicat连接右击→关闭连接，再重新连接，再输入sql语句就成功了

魏时尚早
关注
出现User 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated 解决方法
码农研究僧的博客
04-24 2798
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'easy-admin.students.age' 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 SELECT list is not in GROUP BY clause and contains nonaggregated 【已解决】
JavaPub
08-30 924
然后重新运行语句，就可以正常查询了。在MySQL下输入如下语句。
Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
李利科
02-01 3627
执行以下sql报错： SELECT name,sex,location FROM tp_music.singer group by sex; 报错信息： Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘tp_music.singer.name’ which is not functionally dependent on columns i
mysql错误-1055 - Expression #1 of SELECT list is not in GROUP BY clause 解决方案
liqinglonguo的博客
06-15 7867
这个错误会发生在mysql 5.7 版本及以上版本mysql 5.7版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。找到sql_mode，将其中的ONLY_FULL_GROUP_BY属性去掉，如果没有sql_mode配置，就在 [mysqld]下面追加一下即可。由于开启了ONLY_FULL_GROUP_BY的设置，如果select 的字段不在 group by 中，only_full_group_by不兼容”
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause...
pansanday的专栏
07-11 1万+
问题描述: 在mysql客户端执行delete删除语句时，报出了以下错误： [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not func
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘se
m0_61615803的博客
04-11 7349
模式，即在 GROUP BY 语句中的 SELECT 列表中，只能包含分组或聚合函数，不能包含其他列。而你的查询语句中出现了一个列。列加入 GROUP BY 列表中，这样 MySQL 就能知道如何处理这个列了。这个查询将按学生 ID 分组，并计算每个学生的平均分。这样 MySQL 就不会报出上述错误了。，它既没有被分组也没有被聚合，因此 MySQL 报出了这个错误。这个错误是由于 MySQL 的新版本中默认开启了。Navicat Premium 16 版本。
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cre
weixin_43085797的博客
04-14 1万+
sql语句 SELECT * FROM t_company_change_log WHERE company_id='257949626441666560' AND rec_status='N' GROUP BY change_item 这样会在某些版本的数据库中运行出错 原因是 5.7.5之后版本默认启用了only_full_group_by SQL模式，所以MySQL会不识别选择列表...
MySQL异常:[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nona…
12-14
在MySQL中，错误提示 "[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column…” 指的是在执行包含`GROUP BY`子句的查询时，你试图对未在`GROUP BY`子句中...
解决Mysql：1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
qq_42926722的博客
10-25 5327
解决Mysql：1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
MySql遇到的问题--group by报错“1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains..”
Dbao_miao的博客
03-20 1855
学习group by遇到的问题表，id为主键 表，id为主键 ±—±---------±----------±--------------------±-----------+ | id | LastName | FirstName | Address | City | ±—±---------±----------±--------------------±-----------+ | 1 | Adams | John | Oxford Street
mysql报错 ＞ 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
weixin_44013783的博客
08-05 1万+
数据库查询时报错 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘dkia_quality_db.b.id’ 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 'userinfo.
fansili的博客
11-29 8万+
安装了mysql5.7，用group by 查询时抛出如下异常：Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.t_long.user_name' which is not functionally dependent on columns in GR
报错 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
loveliness_peri的博客
03-01 3万+
错误描述 [Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'community.tab_order_goods.goods_name' which is not functionally dependent on columns in GR...
mysql遇见Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题
热门推荐
N-SOKACH的博客
09-19 18万+
报错如下： Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘sss.month_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompat
报错，＞ 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
泗水六年的博客
10-29 2323
错误1： mysql： select * ,count(*)as count from app_cash_trans_log group by app_cash_trans_log.tran_code having count(*)>=(select pla_config.cfg_value from pla_config where pla_config.cfg_name='tranMaxContinuity') order by app_cash_trans_log.id desc limit 5
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
Rudon滨海渔村的博客
06-12 1万+
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'DBname.table.id' which is not functionally dependent on columns in GROUP BY clause; this i
MySQL 报错[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregat
小洪帽i的博客
10-12 6090
报错：使用 group by 导致错误 [Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myphp.xiaofei.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_gr
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause ... 的错误
free377096858的博客
06-14 1万+
在mysql中使用 group by 出现的问题 ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ynn.goods.goods_id' which is not functionally dependent on colu...
1055 - Expression #1 of SELECT list is not in GROUP BY clause
最新发布
04-02
<think>嗯，用户遇到了MySQL的错误1055，需要解决。让我仔细看看这个问题。根据用户提供的引用内容，错误信息是“Expression #1 of SELECT list is not in GROUP BY clause...”，这通常和sql_mode中的only_full_group_by设置有关。 首先，我得回忆一下MySQL的GROUP BY处理方式。在MySQL 5.7及以上版本，默认启用了only_full_group_by模式，这会强制要求SELECT列表中的非聚合列必须出现在GROUP BY子句中，或者在功能上依赖于GROUP BY的列。如果用户的查询不符合这个要求，就会报这个错误。 用户提到他们的环境是MySQL 8.0，所以问题应该和这个模式有关。解决方案通常有两种：修改查询语句使其符合only_full_group_by的要求，或者调整sql_mode关闭这个选项。但关闭only_full_group_by可能带来数据不一致的风险，所以建议优先优化查询。 接下来，我需要考虑如何具体指导用户操作。比如，如何检查当前的sql_mode设置，如何修改配置文件，或者如何改写SQL查询。用户提供的引用中也提到了Windows下的解决办法，可能涉及到修改my.ini文件。此外，用户可能不太熟悉如何正确使用聚合函数或调整GROUP BY子句，需要给出示例。 还需要注意，用户可能没有正确理解错误的原因，所以需要解释清楚为什么会出现这个错误，以及不同解决方法的优缺点。例如，使用ANY_VALUE()函数或者添加必要的列到GROUP BY中，这些方法的具体应用场景。 最后，要确保回答结构清晰，分步骤说明解决方案，并提供相关问题帮助用户深入理解。同时，引用用户提供的参考资料来支持解答的正确性，比如引用中的解决方法。</think>### MySQL Error 1055 解决方案 #### 问题原因 MySQL 5.7及以上版本默认启用了严格模式 `sql_mode=only_full_group_by`，要求 `SELECT` 列表中的非聚合列必须满足以下条件之一： 1. 出现在 `GROUP BY` 子句中 2. 在功能上依赖于 `GROUP BY` 的列[^2][^4] 若查询未满足此要求，则会触发错误： $$ \text{ERROR 1055: Expression #1 of SELECT list is not in GROUP BY clause} $$ --- #### 解决方案 ##### 方法1：修改查询语句（推荐） **优化SQL以满足严格模式** 假设原查询为： ```sql SELECT column1, column2, aggregate_func(column3) FROM table GROUP BY column1; ``` 需调整为以下两种形式之一： 1. **添加缺失的列到GROUP BY** ```sql SELECT column1, column2, aggregate_func(column3) FROM table GROUP BY column1, column2; -- 明确指定所有非聚合列 ``` 2. **使用聚合函数包裹非聚合列** ```sql SELECT column1, ANY_VALUE(column2), aggregate_func(column3) FROM table GROUP BY column1; -- 使用ANY_VALUE()忽略冗余值[^3] ``` ##### 方法2：调整SQL模式（慎用） **临时禁用only_full_group_by** ```sql SET SESSION sql_mode = &#39;STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION&#39;; ``` **永久修改配置**（需重启MySQL） 1. 打开MySQL配置文件（如 `my.ini` 或 `my.cnf`） 2. 在 `[mysqld]` 部分添加： ```ini sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ``` 3. 重启MySQL服务 --- #### 验证当前SQL模式 ```sql SELECT @@sql_mode; -- 查看当前模式 ``` --- #### 方法对比 | 方案 | 优点 | 缺点 | |------|------|------| | 优化查询 | 符合SQL标准，数据准确性高 | 需理解业务逻辑和列依赖关系 | | 禁用严格模式 | 快速修复旧代码 | 可能导致数据不一致[^1] | ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值