MySQL 树形结构查询的实现

在互联网发展的今天，树状结构的应用越来越广泛，如新闻分类、部门架构、地域关系等等。MySQL作为免费开源的关系数据库管理系统，也支持树形结构的查询，本文就来详细介绍MySQL中树状结构查询的实现。

阅读更多：MySQL 教程

什么是树形结构

树形结构是一种常见的层次结构，它是由节点（node）和边（edge）组成的，每个节点可以有多个子节点，一个节点只能有一个父节点，也就是说，树形结构形成一个具有层次关系的集合。

举个例子，可以将一个公司的部门组织架构看作是一棵树。整个公司的总部为根节点，各个部门为子节点，如人事部、市场部、技术部等。同样的，人事部下面可以有招聘部、培训部等子节点。

MySQL中树形结构的实现

接下来我们将围绕着树形结构的实现，一步步讲解如何在MySQL中实现树状结构。

1. 数据库设计

在MySQL中，要实现树形结构的查询，首先需要考虑如何设计数据库表结构。下面是一个典型的树状结构的表结构：

CREATE TABLE `tree` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

其中， id字段为节点的唯一标识，name字段为节点的名称，parent_id表示父节点的id。根节点的parent_id为0。表中的parent_id就是表中每个节点的“父子关系”之间的桥梁。如下图所示

2. 递归查询

在树状结构中，节点是可以有多个子节点的。因此，如果要查询整个树状结构，必须使用递归查询。下面是MySQL中递归查询的SQL代码：

WITH RECURSIVE cte AS (
 SELECT id,parent_id,name,0 lvl
 FROM   tree
 WHERE  parent_id = 0
 union ALL
 SELECT c.id,c.parent_id,c.name,cte.lvl + 1
 FROM   tree c
 JOIN   cte ON cte.id = c.parent_id
)
SELECT *
FROM   cte
ORDER BY lvl, id;

这部分代码使用了MySQL 8.0以上版本所支持的 WITH关键字和 RECURSIVE关键字，其中：
– cte是我们定义的一个临时表，用来存放查询结果。
– 第一个查询语句是初始查询，查询出根节点的信息，和属性值lvl，作为进行下个查询的记录。
– 第二个查询语句是递归查询语句，通过和cte表做关联查询，获得当前节点的信息，然后在这个基础上递归查询下去。每递归一次，lvl值就加一。
– 最后的SELECT语句是将查询结果按lvl和id进行排序，id就是节点的唯一标识。

可以看到，上面的查询结果就是整个树状结构的节点信息。其中，lvl列表示节点所处的层数，id列是节点的唯一标识，parent_id是它的父节点id，name列是节点的名称。

但是，这样的查询结果显然不够直观，我们需要将查询结果转换为树状结构。

3. 转换为树状结构

接下来，我们需要对查询结果进行处理，将其转换成树状结构，使得每个节点信息都包含在它的父节点信息中。下面是MySQL中将查询结果转换为树状结构的SQL代码：

WITH RECURSIVE cte AS (
    SELECT id,parent_id,name,0 lvl
    FROM   tree
    WHERE  parent_id = 0
    UNION ALL
    SELECT c.id,c.parent_id,c.name,cte.lvl + 1
    FROM   tree c
    JOIN   cte ON cte.id = c.parent_id
)
SELECT 
    id,
    name,
    REPEAT(' ', lvl * 4) AS branch,
    CASE WHEN (SELECT COUNT(*) FROM tree WHERE parent_id = node.id) = 0 THEN '-' ELSE '+' END AS sign
FROM (
    SELECT * FROM cte ORDER BY lvl, id
) AS node;

上面的代码中使用了函数 REPEAT，它可以将字符串连续重复多次。这里用到它的主要是为了输出缩进效果。

从上面的查询结果可以看到，我们已经将查询到的树形结构信息，转化为了可读性强的输出。