【笔记】MYSQL8新功能CTE（公共表表达式）优雅实现递归查询

前言

MYSQL8出现CTE（公共表表达式），前几天在项目中应用，非常优雅的实现了递归查询，非常有意思，这里做一下笔记，方便以后温故而知新。

CTE（common table expression）：是针对同一个FROM子查询在SQL中出现多次的情况，在创建计划的时候，只对其中一个子查询创建计划，并将结果放到临时表（common table）中，其它的子查询直接使用临时表。

---

提示：本文就不详细解释CTE的含义和应用，仅仅分享一下是怎么使用CTE在项目中的一个应用

一、项目场景

我们项目有一个需求，要做一个跟企业微信和钉钉的组织架构（通讯录）的功能，展示公司下一颗部门树型结构，根据用户点击可进入查看下一结点的结构进行展示部门和员工，并支持搜索用户和部门。

二、数据库表结构设计

1.部门表department

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '部门名称',
  `parent_id` bigint NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
  `delete_flag` int NULL DEFAULT 0 COMMENT '1无效 0有效',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES (1, '我的公司', NULL, '2022-04-23 22:38:49', '2022-04-23 23:03:55', 0);
INSERT INTO `department` VALUES (2, '技术部', 1, '2022-04-23 23:04:00', '2022-04-23 23:04:03', 0);
INSERT INTO `department` VALUES (3, '财务部', 1, '2022-04-23 23:17:19', '2022-04-23 23:17:22', 0);
INSERT INTO `department` VALUES (4, '大数据部', 2, '2022-04-23 23:30:26', '2022-04-23 23:30:28', 0);
INSERT INTO `department` VALUES (5, '商品部', 2, '2022-04-23 23:30:50', '2022-04-23 23:30:51', 0);
INSERT INTO `department` VALUES (6, '工程部', 1, '2022-04-23 23:31:12', '2022-04-23 23:31:14', 0);
INSERT INTO `department` VALUES (7, '测试部', 2, '2022-04-23 23:31:34', '2022-04-23 23:31:36', 0);
INSERT INTO `department` VALUES (8, '大数据研发一部', 4, '2022-04-23 23:55:05', '2022-04-24 22:34:17', 0);
INSERT INTO `department` VALUES (9, '大数据开发一部', 4, '2022-04-24 22:34:25', '2022-04-24 22:34:27', 0);
INSERT INTO `department` VALUES (10, '测试一部', 7, '2022-04-24 22:48:24', '2022-04-24 23:10:59', 0);

SET FOREIGN_KEY_CHECKS = 1;

2.员工表employees

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int NOT NULL DEFAULT 0 COMMENT '年龄',
  `main_department` int NOT NULL DEFAULT 0 COMMENT '主要部门id',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
  `delete_flag` int NULL DEFAULT 0 COMMENT '1无效 0有效',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name_age_position`(`name`, `age`, `main_department`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '员工记录表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, '小张', 22, 2, '2022-04-26 00:45:09', '2022-04-27 00:47:39', 0);
INSERT INTO `employees` VALUES (2, '王总', 24, 1, '2022-04-25 00:45:14', '2022-04-27 00:45:20', 0);
INSERT INTO `employees` VALUES (3, '小亮', 23, 4, '2022-04-13 00:45:21', '2022-04-27 00:47:37', 0);

SET FOREIGN_KEY_CHECKS = 1;

三、递归部门树使用CTE

1.查询部门下的所有节点

WITH RECURSIVE cte AS (
		SELECT d.id, d.name, d.parent_id
		FROM department d
		WHERE id = 1
		UNION ALL
		SELECT c.id, c.name, c.parent_id
		FROM department c, cte
		WHERE c.parent_id = cte.id
	)
SELECT * FROM cte;

执行结果

2.查询部门下所有上级部门

WITH RECURSIVE cte AS (
    SELECT d.id,d.name,d.parent_id FROM department d WHERE id = 4   
    UNION ALL
    SELECT c.id,c.name,c.parent_id FROM department c,cte WHERE c.id = cte.parent_id   
		)
SELECT * FROM cte;

执行结果

---

使用方式

1.应用初始化对部门组织进行缓存初始化，方便后面渲染组织树
2.组织架构变更的时候清除缓存

总结

mysql cte的递归遍历使用临时表存储结果集，依次执行任务完成递归，实现功能方式很多种，欢迎大家一起讨论实现功能多样的组织树