MySQL8新特性优化父子层级关系数据CTE查询

引言

父子结构数据模型，又称为层次结构数据模型或树形结构，是一种在数据库中表示元素间具有上下级关系的数据组织方式。这种模型广泛应用于多种实际场景

主要包括但不限于：

​

组织架构管理：企业或机构内部的部门与员工关系，其中部门可以包含子部门，形成一个多层级的管理体系。

商品分类：电商平台的商品按照类别和子类别分层，如电子产品下有手机、电脑等子类，手机下又有不同品牌和型号。

文件系统目录：计算机文件系统的目录结构，文件夹可以包含子文件夹，形成树状结构。

内容管理系统：博客、论坛的帖子和评论，或是文章与其所属的多个标签之间的关联。

权限与角色管理：用户权限模型中，角色可以继承上级角色的权限，形成权限层次。

CTE基本概念

CTE，即Common Table Expression，是MySQL 8.0版本及以后引入的一个功能，是在SQL查询中定义的一种临时结果集，并给这个结果集命名，CTE通过WITH关键字引入，可以在SELECT、INSERT、UPDATE、DELETE语句之前定义并可以在同一查询中被多次引用。

CTE不是物理存储的对象，而是在查询执行期间临时存在的逻辑视图。CTE分为递归和非递归两种，其中递归CTE特别适用于处理上述父子结构数据，能够简便地实现树形结构的遍历查询。

​

语法介绍

非递归语法结构

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)

递归语法结构

递归CTE的基本语法结构包括两个部分：初始化查询（非递归部分）和递归成员（递归部分）。

WITH RECURSIVE cte_name AS (
    -- 初始化查询（非递归部分）
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition_for_initial_rows
    
    UNION ALL
    
    -- 递归查询（递归部分）
    SELECT column1, column2, ...
    FROM cte_name
    JOIN another_table ON cte_name.some_column = another_table.related_column
    WHERE recursive_condition
)

在这个结构中，首先定义一个初始化查询来确定递归的起始点，然后通过UNION ALL连接递归查询部分，递归查询部分会引用自身的cte_name来扩展结果集，直到满足停止递归的条件。递归查询必须有一个明确的终止条件，否则会导致无限循环。

​

定义完CTE后，你就可以在紧接着的主要查询中像使用普通表那样使用这个cte_name。

SELECT ...
FROM cte_name
JOIN another_table ...
WHERE more_conditions;

父子结构示例数据

在数据库设计中，设计用于存储父子结构（也称为树形结构）的表时，最常见的方法是使用自引用关系，即在表中包含一个指向其父节点的外键。下面是一个简单的表设计示例：

CREATE TABLE `sys_company` (
  `dept_id` bigint NOT NULL COMMENT '部门id',
  `parent_id` bigint DEFAULT '0' COMMENT '父部门id',
  `dept_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '部门名称',
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='部门表';

表示例数据

INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (100, 0, '沫离科技-广东省');
INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (101, 100, '深圳总公司');
INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (102, 100, '广州总公司');
INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (103, 0, '沫离科技-江西省');
INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (104, 103, '赣州总公司');
INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (105, 101, '福田区分公司');
INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (106, 101, '南山区分公司');
INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (107, 103, '南昌总公司');
INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (108, 103, '九江总公司');
INSERT INTO `sys_company`(`dept_id`, `parent_id`, `dept_name`) VALUES (109, 104, '章贡区分公司');