mysql查询树形结构数据（mysql查询树状结构）

## MySQL 查询树形结构数据### 简介在关系型数据库中，经常需要存储和查询树形结构数据，例如：组织架构、商品分类、评论回复等。MySQL 并没有直接提供树形结构的存储类型，通常使用

邻接表

或

路径枚举

等方式来表示树形结构，并使用递归查询或其他技巧进行查询。### 1. 邻接表模型#### 1.1 模型介绍邻接表模型是最简单直观的树形结构存储方式，每个节点存储自身的 id 和父节点的 id (parent_id)，根节点的 parent_id 通常为 NULL 或 0。#### 1.2 表结构示例```sql CREATE TABLE category (id INT PRIMARY KEY,name VARCHAR(255),parent_id INT,FOREIGN KEY (parent_id) REFERENCES category(id) ); ```#### 1.3 查询方式

查询指定节点的所有子节点:

可以使用递归查询 (Common Table Expression - CTE) 进行查询。```sql WITH RECURSIVE category_tree AS (SELECT id, name, parent_idFROM categoryWHERE id = 2 -- 指定节点idUNION ALLSELECT c.id, c.name, c.parent_idFROM category cJOIN category_tree ct ON c.parent_id = ct.id ) SELECT

FROM category_tree; ```

查询指定节点的所有父节点:

同样可以使用递归查询，不过需要反向查找父节点。```sql WITH RECURSIVE category_path AS (SELECT id, name, parent_idFROM categoryWHERE id = 5 -- 指定节点idUNION ALLSELECT c.id, c.name, c.parent_idFROM category cJOIN category_path cp ON c.id = cp.parent_id ) SELECT

FROM category_path; ```#### 1.4 优缺点

优点:

简单直观，易于理解和实现。

插入和删除节点操作效率较高。

缺点:

递归查询效率较低，尤其对于层级较深的树。

查询指定节点的所有子节点需要遍历整棵树。### 2. 路径枚举模型#### 2.1 模型介绍路径枚举模型为每个节点存储一个路径字符串，该字符串记录了从根节点到该节点的路径信息，通常使用分隔符 (例如 "/") 分隔路径中的节点 id。#### 2.2 表结构示例```sql CREATE TABLE category (id INT PRIMARY KEY,name VARCHAR(255),path VARCHAR(255) ); ```数据示例：| id | name | path | | -- | ------ | --------- | | 1 | Root | /1/ | | 2 | 电器 | /1/2/ | | 3 | 手机 | /1/2/3/ | | 4 | 电脑 | /1/2/4/ | | 5 | 服装 | /1/5/ |#### 2.3 查询方式

查询指定节点的所有子节点:

使用 `LIKE` 模糊查询即可。```sql SELECT

FROM category WHERE path LIKE '/1/2/%'; -- 查询id为2的节点的所有子节点 ```

查询指定节点的所有父节点:

使用字符串函数截取路径字符串。```sql SELECT

FROM category WHERE SUBSTRING_INDEX('/1/2/3/', '/', 2) = path; -- 查询id为3的节点的所有父节点 ```#### 2.4 优缺点

优点:

查询效率高，不需要递归查询。

可以方便地查询指定节点的所有子节点和父节点。

缺点:

路径字符串存储空间占用较大，尤其对于层级较深的树。

插入和删除节点操作需要更新所有子节点的路径字符串，效率较低。### 3. 总结选择哪种模型取决于具体的业务场景和需求。如果树的层级较浅，且插入和删除操作频繁，可以选择邻接表模型；如果树的层级较深，且查询操作频繁，可以选择路径枚举模型。此外，还可以结合其他技术，例如：嵌套集模型、闭包表模型等来优化树形结构数据的存储和查询效率。

MySQL 查询树形结构数据

简介在关系型数据库中，经常需要存储和查询树形结构数据，例如：组织架构、商品分类、评论回复等。MySQL 并没有直接提供树形结构的存储类型，通常使用 **邻接表** 或 **路径枚举** 等方式来表示树形结构，并使用递归查询或其他技巧进行查询。

1. 邻接表模型

1.1 模型介绍邻接表模型是最简单直观的树形结构存储方式，每个节点存储自身的 id 和父节点的 id (parent_id)，根节点的 parent_id 通常为 NULL 或 0。

1.2 表结构示例```sql CREATE TABLE category (id INT PRIMARY KEY,name VARCHAR(255),parent_id INT,FOREIGN KEY (parent_id) REFERENCES category(id) ); ```

1.3 查询方式* **查询指定节点的所有子节点:** 可以使用递归查询 (Common Table Expression - CTE) 进行查询。```sql WITH RECURSIVE category_tree AS (SELECT id, name, parent_idFROM categoryWHERE id = 2 -- 指定节点idUNION ALLSELECT c.id, c.name, c.parent_idFROM category cJOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree; ```* **查询指定节点的所有父节点:** 同样可以使用递归查询，不过需要反向查找父节点。```sql WITH RECURSIVE category_path AS (SELECT id, name, parent_idFROM categoryWHERE id = 5 -- 指定节点idUNION ALLSELECT c.id, c.name, c.parent_idFROM category cJOIN category_path cp ON c.id = cp.parent_id ) SELECT * FROM category_path; ```

1.4 优缺点**优点:*** 简单直观，易于理解和实现。 * 插入和删除节点操作效率较高。**缺点:*** 递归查询效率较低，尤其对于层级较深的树。 * 查询指定节点的所有子节点需要遍历整棵树。

2. 路径枚举模型

2.1 模型介绍路径枚举模型为每个节点存储一个路径字符串，该字符串记录了从根节点到该节点的路径信息，通常使用分隔符 (例如 "/") 分隔路径中的节点 id。

2.2 表结构示例```sql CREATE TABLE category (id INT PRIMARY KEY,name VARCHAR(255),path VARCHAR(255) ); ```数据示例：| id | name | path | | -- | ------ | --------- | | 1 | Root | /1/ | | 2 | 电器 | /1/2/ | | 3 | 手机 | /1/2/3/ | | 4 | 电脑 | /1/2/4/ | | 5 | 服装 | /1/5/ |

2.3 查询方式* **查询指定节点的所有子节点:** 使用 `LIKE` 模糊查询即可。```sql SELECT * FROM category WHERE path LIKE '/1/2/%'; -- 查询id为2的节点的所有子节点 ```* **查询指定节点的所有父节点:** 使用字符串函数截取路径字符串。```sql SELECT * FROM category WHERE SUBSTRING_INDEX('/1/2/3/', '/', 2) = path; -- 查询id为3的节点的所有父节点 ```

2.4 优缺点**优点:*** 查询效率高，不需要递归查询。 * 可以方便地查询指定节点的所有子节点和父节点。**缺点:*** 路径字符串存储空间占用较大，尤其对于层级较深的树。 * 插入和删除节点操作需要更新所有子节点的路径字符串，效率较低。

3. 总结选择哪种模型取决于具体的业务场景和需求。如果树的层级较浅，且插入和删除操作频繁，可以选择邻接表模型；如果树的层级较深，且查询操作频繁，可以选择路径枚举模型。此外，还可以结合其他技术，例如：嵌套集模型、闭包表模型等来优化树形结构数据的存储和查询效率。