MySql解决GROUP BY出现的问题

已于 2022-04-19 10:53:03 修改
阅读量9.3k 收藏 19
点赞数 7
分类专栏： mysql 文章标签： mysql
于 2022-04-19 10:49:38 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tanqingfu1/article/details/124267435
版权

目录​​​​​​​

1、测试创建表结构和插入数据 

 2、查询数据采用分组报错

​3、报错原因

3.1 原理方面

3.2 查询sql结果

4、查看ONLY_FULL_GROUP_BY 校验规则是否开启

5、解决方式方法

5.1 修改sql语句使其遵守only_full_group_by规则

5.2 通过执行命令去掉校验

5.3 通过修改配置文件my.ini实现 

1、测试创建表结构和插入数据 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for project_node
-- ----------------------------
DROP TABLE IF EXISTS `project_node`;
CREATE TABLE `project_node`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `project_id` bigint(20) NULL DEFAULT NULL COMMENT '项目ID',
  `main_node_type` int(3) NULL DEFAULT NULL COMMENT '主节点类型\r\n0-项目主节点\r\n1-立项审批\r\n2-政府采购\r\n3-项目建设\r\n4-初验管理\r\n5-终验管理',
  `new_node` int(3) NULL DEFAULT NULL COMMENT '当前节点数值',
  `create_user` bigint(20) NULL DEFAULT NULL COMMENT '创建人',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_user` bigint(20) NULL DEFAULT NULL COMMENT '更新人',
  `update_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 192 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '项目流程-节点位置' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (1, 20, 0, 3, 1, '2022-02-16 15:27:57', 763256, '2022-02-18 11:31:06');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (2, 20, 1, 2, 1, '2022-02-16 15:30:48', NULL, '2022-02-17 11:55:07');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (3, 21, 0, 2, 1, '2022-02-16 15:31:09', NULL, '2022-02-16 15:37:08');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (4, 21, 1, 3, 1, '2022-02-16 15:37:18', NULL, '2022-02-16 15:37:20');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (5, 61, 0, 1, 1, '2022-02-21 17:21:53', 763256, '2022-02-21 17:23:16');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (6, 62, 0, 2, 1, '2022-02-21 17:27:19', 763256, '2022-02-23 11:14:27');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (7, 61, 1, 7, 1, '2022-02-21 18:07:58', 1, '2022-02-21 18:09:12');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (8, 62, 1, 7, 1, '2022-02-22 09:46:46', 1, '2022-02-22 16:07:03');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (9, 69, 0, 2, 1, '2022-02-22 09:49:40', 763256, '2022-04-14 14:46:49');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (10, 69, 1, 7, 1, '2022-02-22 09:49:48', 763256, '2022-02-22 10:35:55');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (11, 65, 0, 1, 1, '2022-02-22 10:00:30', 1, '2022-02-22 10:10:55');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (12, 65, 1, 7, 1, '2022-02-22 10:00:41', 1, '2022-02-22 10:19:07');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (13, 66, 0, 1, 1, '2022-02-22 10:02:19', 1, '2022-02-22 10:10:46');
INSERT INTO `project_node`(`id`, `project_id`, `main_node_type`, `new_node`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (14, 66, 1, 7, 1, '2022-02-22 10:02:31', 1, '2022-02-22 10:10:32');

 2、查询数据采用分组报错

MySql从5.7版本开始默认开启only_full_group_by规则，规则核心原则如下，没有遵循原则的sql会被认为是不合法的sql。

1、order by后面的列必须是在select后面存在的。

2、 select、having或order by后面存在的非聚合列必须全部在group by中存在。

3、报错原因

3.1 原理方面

        这个错误一般发生在mysql 5.7以及 5.7以上的版本中，其原因是mysql的默认配置中,sql_mode="ONLY_FULL_GROUP_BY" 这个配置严格执行了 'SQL92标准',所以很高网站维护人员在升级mysql版本时，都会修改 sql_mode 的配置，使其能兼容。

3.2 查询sql结果

        从sql层面来说，输出的结果思做target list，就是 select 后面跟着的字段，还有一个地方是 group by column,就是 group 后面跟着的字段，由于开启了 ONLY_FULL_GROUP_BY 的设置，如果没有字段在 target list 以及 group by 字段中同时出现，那么 sql 就认为此条语句是不合法的，就会抛出错误。

4、查看ONLY_FULL_GROUP_BY 校验规则是否开启

-- 查看ONLY_FULL_GROUP_BY 校验规则是否开启
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;


-- 第一个sql语句的结果
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

-- 第二个sql语句的结果
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


 可以从查询的数据来看是存在ONLY_FULL_GROUP_BY这个配置的

5、解决方式方法

5.1 修改sql语句使其遵守only_full_group_by规则

 -- 通过any_value(字段名称) 可以获取分组之后的字段数据

SELECT
	project_id,
	any_value(main_node_type) as main_node_type
FROM
	`project_node` 
GROUP BY
	project_id;

5.2 通过执行命令去掉校验

-- 关闭only_full_group_by的规则校验
set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

这种方式重启mysql服务就会失效。

5.3 通过修改配置文件my.ini实现 

在 [mysqld] 下面添加代码：
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

重启mysql服务。

注意：

1、不同的系统，mysql 的配置文件名以及路径不同

2、Mac或Linux文件  /etc/my.cnf

3、windows 在数据库安装目录下的 my.ini

码奴生来只知道前进~
关注
  • 7
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
mysql 8.0高版本 sql_mode=only_full_group_by 解决方法
RayfunC的博客
02-02 3648
运行 sql 时报错 Expression #2 of SELECT list is not in GROUP BY clause........ which is not functionally dependent on columns in GROUP BY clause;this is incompatible with sql_mode=only_full_group_by 1、报错原因 这个错误一般发生在mysql 5.7以及 5.7以上的版本中，其原因是mysql的默认配置中,sql_m
mysql 出现in GROUP BY clause； this is incompatible with sql_mode=only_full_group_by问题
一个抽风的女Ren的博客
07-07 1448
部署项目使用到了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】5.7以上的mysql使用group by 报错 (pymysql.err.OperationalError) (1055, “Expression #1 of SELECT list
weixin_40293999的博客
02-29 587
事实上MySQL更新这种模式不是没有意义的：对于语义限制都比较严谨的多家数据库，如SQLServer、Oracled等数据库都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以从MySQL 5.7版本开始修正了这个语义，就是所说的ONLY_FULL_GROUP_BY语义。在把MySQL升级到5.7或者更高的版本，一些以前看上去不会出错的group by 操作在这个版本以后就会出现语法报错的情况。还有就是改mysql.ini文件，我没有这么做。
mysql高版本(8.0+)group_by报错的处理方法
weixin_42202992的博客
08-22 2867
mysql高版本(8.0+)group_by报错的处理方法
解决MySQL进行group by 字段返回大量异常结果
最新发布
蒾酒的博客
05-05 292
操作，MySQL 会尽可能地对浮点数进行分组。但是，由于浮点数在计算机中的表示方式可能会导致精度问题，因此可能会出现意外的结果。这样一看该列存储数据值只有1，2，3为啥会group by出那么多数据呢。返回的数据非常多，这不合理，经过仔细排查发现了出现该问题的原因。在 MySQL 中，如果你使用浮点型字段进行。原来是该列的数据类型设置成了浮点型。把字段类型改成整形或者字符串。
高版本Mysql使用group by 分组报错
qq_43486404的博客
09-07 4913
高版本mysql使用group by 报错 #1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql group by 报错
u014576358的博客
11-08 1025
​MySQL5.7.5​后​only_full_group_by​成为​sql_mode​的默认选项之一,导致一些sql语句失效,比如​group by​进行分组查询。在命令行输入命令查看​sql_model​配置:select @@sql_mode;如果查询结果有​ONLY_FULL_GROUP_BY,​ 即为报错的原因。查看sql_mode配置。
MySQL8.x group_by报错的4种解决方法
csdn_aspnet的专栏
02-17 8076
然后通过表连接 on d.emp_no = s.emp_no 使这两张表结合，通过on a.salary = b.salary使薪资统一，即这两张表连接后的表的薪资为每个部门的最高员工薪资。如果代码中含有group by聚合操作，那么select中的列，除了使用聚合函数之外的，如max()、min()等，都必须出现在group by中。但是，当我们再一次重新启动数据库时，可能会恢复原样，还是会出现ONLY_FULL_GROUP_BY的报错，这就需要我们再一次修改数据库配置。那有没有可以永久生效的办法呢？
MySQL进行group-by查询报错的解决方案
m0_49808352的博客
04-09 583
MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。使用MySQL进行嵌套查询的时候遇见了错误。2）查询mysql 相关mode。1）进入mysql。
MySql报错- 解决MySQL非聚合列未包含在GROUP BY子句报错问题
zhuimengchizixinwx的博客
10-09 280
测试环境 win10 MySQL 5.7 问题描述： 执行类似mysql查询， mysql> select goods_id,goods_name from group by cat_id; 报错，如下： 服务器内部错误 (1055, “Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘case_table.id’ which is not functionally dep
MySQL优化GROUP BY方案
09-10
满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如果有)。在某些情况中，MySQL能够做得更好，即通过索引访问而不用创建...
mysql不支持group by的解决方法小结
09-08
MySQL中的`GROUP BY`语句用于对数据进行分组，并且通常与聚合函数（如`COUNT`, `SUM`, `AVG`, `MAX`, `MIN`）一起使用，以计算每个分组的汇总信息。然而，在MySQL 5.7.x及更高版本中，默认启用了一个名为`ONLY_FULL_...
Mysql利用group by分组排序
09-09
总之，Mysql的`GROUP BY`分组排序功能为我们提供了一种有效的方式来处理复杂的数据排序和选择问题，尤其是在需要找到每个分组的“最值”时。理解并掌握这一特性，对于优化SQL查询和提升数据库操作效率至关重要。希望...
Mysql升级到5.7后遇到的group by查询问题解决
09-09
主要给大家介绍了关于Mysql升级到5.7后遇到的group by查询问题的解决方法，文中通过示例代码介绍的非常详细，对同样遇到这个问题的朋友们具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧。
MYSQL GROUP BY用法详解
09-09
这个例子展示了如何利用`GROUP BY`有效地管理和清理数据库中的数据，同时也展示了MySQL在实际问题解决中的灵活性。不过，需要注意的是，在进行删除操作时务必谨慎，确保备份数据，防止意外丢失重要信息。
Mysql中，使用group by报错
a14654的博客
12-03 804
使用Mysql的group by报错 原文：https://www.cnblogs.com/chancy/p/10026097.html sql语句如下： SELECT table_id, description FROM bos_table_description GROUP BY table_id 报错信息： SELECT list is not in GROUP BY clause and contains nonaggregated column ‘cy_form.bos_table_d
mysql语句使用group by报错问题解决方案
weixin_40444253的博客
02-23 1506
mysql语句使用group by报错问题解决方案
mysql count group by_解决mysql group by和count同时使用的问题
06-08
您好！针对您的问题，我可以给出以下解决方案： 在MySQL中，可以使用GROUP BY和COUNT函数一起使用来对结果进行分组计数。例如，可以按照某个列分组，然后计算每个分组中的行数。 具体的语句是： ``` SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name; ``` 其中，`column_name`是您想要按照其进行分组的列名，`table_name`是您要查询的表名。这个语句将返回每个不同的`column_name`值以及每个分组中的行数。 如果您想要进一步筛选结果，可以在查询中添加WHERE子句。例如： ``` SELECT column_name, COUNT(*) FROM table_name WHERE some_column = 'some_value' GROUP BY column_name; ``` 这个查询将只返回`some_column`列中值为`'some_value'`的行，并按照`column_name`列进行分组计数。 希望这个解决方案能够帮助到您！如果您有其他问题，请随时提出。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

码奴生来只知道前进~

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值