【mysql】解决GROUP BY出现的问题

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