mysql树形遍历|极客笔记

mysql树形遍历

在数据库中，有时候我们需要对树形结构的数据进行遍历和操作，比如组织结构、分类目录等。在MySQL数据库中，我们可以利用递归查询和连接查询的方法来实现树形结构的遍历。

创建测试数据表

首先，我们需要创建一个用于测试的数据表。假设我们要创建一个商品分类的树形结构，每个分类包含分类ID和父分类ID，具体表结构如下：

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

INSERT INTO categories VALUES (1, '电子产品', 0);
INSERT INTO categories VALUES (2, '手机', 1);
INSERT INTO categories VALUES (3, '电脑', 1);
INSERT INTO categories VALUES (4, '耳机', 2);
INSERT INTO categories VALUES (5, '笔记本电脑', 3);
INSERT INTO categories VALUES (6, '键盘', 3);
INSERT INTO categories VALUES (7, '苹果手机', 4);

使用递归查询

递归查询一级分类

首先，我们来查询一级分类，即parent_id为0的分类：

WITH RECURSIVE cte AS (
    SELECT id, name, parent_id, 0 as level
    FROM categories
    WHERE parent_id = 0
    UNION ALL
    SELECT c.id, c.name, c.parent_id, level + 1
    FROM categories c
    JOIN cte ON cte.id = c.parent_id
)
SELECT id, name, level
FROM cte;

以上查询使用了递归公共表表达式（CTE）来实现递归查询，首先选择所有parent_id为0的分类，然后依次查询其子分类，直到没有子分类为止。最终得到一级分类的结果：

| id | name        | level |
|----|-------------|-------|
| 1  | 电子产品     | 0     |
| 2  | 手机        | 1     |
| 3  | 电脑        | 1     |

递归查询所有子分类

接下来，我们来查询某个分类下的所有子分类，不限制层级：

WITH RECURSIVE cte AS (
    SELECT id, name, parent_id, 0 as level
    FROM categories
    WHERE id = 1 -- 查询id为1的分类及其所有子分类
    UNION ALL
    SELECT c.id, c.name, c.parent_id, level + 1
    FROM categories c
    JOIN cte ON cte.id = c.parent_id
)
SELECT id, name, level
FROM cte;

以上查询同样使用了递归公共表表达式，查询id为1的分类及其所有子分类：

| id | name        | level |
|----|-------------|-------|
| 1  | 电子产品     | 0     |
| 2  | 手机        | 1     |
| 4  | 耳机        | 2     |
| 7  | 苹果手机    | 3     |
| 3  | 电脑        | 1     |
| 5  | 笔记本电脑  | 2     |
| 6  | 键盘        | 2     |

使用连接查询

除了递归查询外，我们还可以通过连接查询来实现树形结构的遍历。

查询父分类和所有子分类

首先，我们来查询所有分类及其父分类：

SELECT 
    c1.id as child_id,
    c1.name as child_name,
    c2.id as parent_id,
    c2.name as parent_name
FROM categories c1
LEFT JOIN categories c2 ON c1.parent_id = c2.id;

以上查询使用了自连接，通过左连接将分类表自身连接起来，查询每个分类的父分类：

| child_id | child_name    | parent_id | parent_name  |
|----------|---------------|-----------|--------------|
| 1        | 电子产品       | NULL      | NULL         |
| 2        | 手机          | 1         | 电子产品     |
| 3        | 电脑          | 1         | 电子产品     |
| 4        | 耳机          | 2         | 手机         |
| 5        | 笔记本电脑    | 3         | 电脑         |
| 6        | 键盘          | 3         | 电脑         |
| 7        | 苹果手机      | 4         | 耳机         |

查询所有子分类及其所属父分类

接着，我们来查询每个分类及其所有子分类的父分类：

SELECT 
    c1.id as child_id,
    c1.name as child_name,
    c1.parent_id,
    c2.id as parent_id,
    c2.name as parent_name
FROM categories c1
LEFT JOIN categories c2 ON c1.parent_id = c2.id;

以上查询同样使用了自连接，查询每个分类及其所有子分类的父分类：

| child_id | child_name    | parent_id | parent_id | parent_name  |
|----------|---------------|-----------|-----------|--------------|
| 1        | 电子产品       | 0         | NULL      | NULL         |
| 2        | 手机          | 1         | 1         | 电子产品     |
| 4        | 耳机          | 2         | 1         | 手机         |
| 7        | 苹果手机      | 4         | 1         | 耳机         |
| 3        | 电脑          | 1         | 1         | 电子产品     |
| 5        | 笔记本电脑    | 3         | 1         | 电脑         |
| 6        | 键盘          | 3         | 1         | 电脑         |