MySQL - 递归树结构

MySQL是一种广泛使用的关系型数据库管理系统，它提供了许多强大的功能和特性来支持各种应用场景。其中之一就是递归树结构的处理。递归树结构是一种常见的数据结构，它在处理具有层次关系的数据时非常有用。本文将介绍MySQL中如何使用递归树结构，并通过一个案例代码来进行说明。

CREATE TABLE organization (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);

WITH RECURSIVE cte AS (
    SELECT id, name, parent_id
    FROM organization
    WHERE id = 1
    UNION ALL
    SELECT o.id, o.name, o.parent_id
    FROM organization o
    INNER JOIN cte ON o.parent_id = cte.id
)
SELECT *
FROM cte;

mysql
CREATE TABLE organization (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT
);
INSERT INTO organization VALUES (1, '公司', NULL);
INSERT INTO organization VALUES (2, '部门1', 1);
INSERT INTO organization VALUES (3, '部门2', 1);
INSERT INTO organization VALUES (4, '部门3', 2);
INSERT INTO organization VALUES (5, '部门4', 2);
INSERT INTO organization VALUES (6, '部门5', 3);
WITH RECURSIVE cte AS (
    SELECT id, name, parent_id
    FROM organization
    WHERE id = 1
    UNION ALL
    SELECT o.id, o.name, o.parent_id
    FROM organization o
    INNER JOIN cte ON o.parent_id = cte.id
)
SELECT *
FROM cte;

+----+-------+-----------+
| id | name  | parent_id |
+----+-------+-----------+
| 1  | 公司  | NULL      |
| 2  | 部门1 | 1         |
| 3  | 部门2 | 1         |
| 4  | 部门3 | 2         |
| 5  | 部门4 | 2         |
| 6  | 部门5 | 3         |
+----+-------+-----------+