Mysql树形结构(表内自关联)设计与查询遍历方式

这篇博文来讨论一下在mysql中运用树形结构进行设计，并进行查询的方式

背景

最近在做公司的一个工业相关的项目，其中有一个需求，做一个BOM(Bill of Material 物料清单)的实现。关于BOM这里不做过多解释，大家自行百度，我这里遇到的问题是在描述产品结构时，我这里的产品由不定数量子产品组成，而每一个子产品又有不定数量子产品组成，依次类推，如下图所示:

至此我们可以看出，我们需要一个:
树状结构的库的设计，并且这个树的子节点数量未知

设计数据库

经过上面的分析，下面我们来设计这个数据库，我这里采用表内自关联的方式:

首先我们看一下表结构

mysql> desc product;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| parent_id | bigint(20)   | YES  |     | NULL    |                |
| name      | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

都是一些见名知意的字段，主键，父节点id，产品名。

我们进行一次查询

Mysql 8.0版本之前版本解决方案

1. 纯粹走Mysql方式

• 通过LEFT JOIN 之类的方式，但树的节点数不确定
• 一条一条LEFT JOIN 然后在java代码里循环判断
  PS:网上也有很多解决方案，可以一条语句实现，但都是确定节点数的前提，目前我还没有找到能一条语句不确定节点数的实现方式
  总结看，方式1适用于确定节点的数量确定的情况，但是很多的LEFT JOIN 其实也牺牲了可读性

2. 用java循环+Mysql查询

• 首先写一个迭代的方法，这里不提供具体实现，提供一个思路:
  

Mysql 8.0版本新特性-CTE递归解决方案

WITH cte AS 
( 
    SELECT 
        * 
    FROM 
        product 
    WHERE NAME = '我是一个弟中弟' 
    UNION ALL 
    SELECT 
        * 
    FROM 
        product
    WHERE parent_id = cte.id 
)
SELECT * FROM cte;

这个方法笔者没有尝试，是查阅了一些资料在这里进行的总结
这里需要解释一下CTE :
CTE: 公共表达式
公用表表达式是一个命名的临时结果集，仅在单个SQL语句(例如SELECT，INSERT，UPDATE或DELETE)的执行范围内存在。与派生表类似，CTE不作为对象存储，仅在查询执行期间持续。 与派生表不同，CTE可以是自引用(递归CTE，也可以在同一查询中多次引用。 此外，与派生表相比，CTE提供了更好的可读性和性能。定义CTE后，可以像SELECT，INSERT，UPDATE，DELETE或CREATE VIEW语句中的视图一样使用它。
更多详情:CTE简介

未完待续，后续补充

我的博客