MySQL 报错：1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘model.group_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

作者：

在

Post Views: 267

1、MySQL 报错： 1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘model.group_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by。如图1

MySQL 报错：1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'model.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by。 — 图1

SQL

SELECT * FROM ((SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')) UNION ALL ( SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_pre_pub_log` ON `cpa_task_group`.`id` = `cpa_pre_pub_log`.`task_group_id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_pre_pub_log`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c') )) `model` GROUP BY `id`


SELECT * FROM ((SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')) UNION ALL ( SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_pre_pub_log` ON `cpa_task_group`.`id` = `cpa_pre_pub_log`.`task_group_id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_pre_pub_log`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c') )) `model` GROUP BY `id`

2、执行第 1 个 SQL 子句，未报错，查询结果为空。如图2

SQL

SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')


SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')

3、执行第 2 个 SQL 子句，未报错，查询结果为空。如图3

SQL

SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')


SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')

4、分别对比 2 个 SQL 子句的查询结果的字段。发现结果是完全一样的。

Plaintext

id	group_id	uuid	source	source_article_id	status	pubed_at	is_deleted	created_at	updated_at	deleted_at
id	group_id	uuid	source	source_article_id	status	pubed_at	is_deleted	created_at	updated_at	deleted_at


id	group_id	uuid	source	source_article_id	status	pubed_at	is_deleted	created_at	updated_at	deleted_at
id	group_id	uuid	source	source_article_id	status	pubed_at	is_deleted	created_at	updated_at	deleted_at

5、调整 SQL 语句，明确声明每一个字段。即查询字段与分组字段要完全一致。就不会报错。如图4

SQL

SELECT id,group_id FROM ((SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')) UNION ALL ( SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_pre_pub_log` ON `cpa_task_group`.`id` = `cpa_pre_pub_log`.`task_group_id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_pre_pub_log`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c') )) `model` GROUP BY `id`,group_id


SELECT id,group_id FROM ((SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')) UNION ALL ( SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_pre_pub_log` ON `cpa_task_group`.`id` = `cpa_pre_pub_log`.`task_group_id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_pre_pub_log`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c') )) `model` GROUP BY `id`,group_id

6、参考网址：https://stackoverflow.com/questions/40662899/sqlstate42000-syntax-error-or-access-violation-1055-expression-2 。修改 my.ini 文件。因为，修改 SQL 语句的成本过高。不再报错。如图5

参考网址：https://stackoverflow.com/questions/40662899/sqlstate42000-syntax-error-or-access-violation-1055-expression-2 。修改 my.ini 文件。因为，修改 SQL 语句的成本过高。不再报错。 — 图5

INI

# 当前的配置
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 修改后的配置，添加：STRICT_ALL_TABLES
sql_mode=STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


# 当前的配置
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 修改后的配置，添加：STRICT_ALL_TABLES
sql_mode=STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

需要长期技术维护或远程问题排查？

我是拥有 15+ 年经验的 PHP / Go 后端工程师，长期关注已有系统维护、Bug 修复、性能优化、服务器排查、WordPress 网站维护和小功能迭代。

如果你的项目遇到以下情况，可以先从一次小问题排查开始合作：

✅ PHP / Laravel / Yii2 老项目无人维护
✅ Go / Gin 后端接口需要排查或优化
✅ WordPress 网站访问慢、报错或插件冲突
✅ Nginx / MySQL / Redis / Linux 服务器异常
✅ CDN / Cloudflare / DNS / HTTPS 配置问题
✅ 需要长期远程技术支持或兼职维护

更多介绍请查看：关于我 & 合作

微信：13980074657
邮箱：shuijingwanwq@gmail.com
Telegram：@shuijingwan
GitHub：https://github.com/shuijingwan

GROUP BY SELECT list contains nonaggregated column clause sql_mode MySQL only_full_group_by functionally dependent Expression my.ini STRICT_ALL_TABLES 1055

发表回复取消回复

这个站点使用 Akismet 来减少垃圾评论。了解你的评论数据如何被处理。

MySQL 报错：1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘model.group_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

评论

发表回复 取消回复

更多文章

WordPress 网站根目录清理实战：从误粘贴生成的 0 字节文件，到历史脚本、验证文件与旧安装包逐项排查

WordPress 7.0 + PHP 8.5 服务器配置全面审计：PHP-FPM、OPcache、Redis、RDS 与 WordPress 调优实战

WordPress 动态首页从 19 秒降到 1 秒以内：Post Views Counter 热门文章查询性能问题排查与 MU Plugin 优化实战

WordPress 升级 PHP 8.5 后的插件兼容性排查：该修的修，该停的停，该等上游的等

发表回复取消回复