mysql - 传递闭包表重组

Question

我如何能够从当前结构中检索完整树，或重构当前表结构以允许优化的递归查询？

问题

如果没有迭代，无法从基础组件中检索完整的组件树。

单个组件可以有未定义数量的连接(深度)。

组件没有父属性，因为每个组件可以与多个组件相关。

无法递归更新组件的受影响属性值。例如，如果组件的价格发生变化，则所有相关组件的价格都会更新。

当前结构

组件

primary key (id)

| id | price |
|----|------ |
| A  | 1     |
| B  | 1     |
| C  | 1     |
| D  | 2     |
| E  | 2     |

组件闭包

unique index (component, component_of)
index (component_of)
FK (component) References component (id)
FK (component_of) References component (id)

| component | component_of |
|--------------------------|
|     D     |  B           |
|     D     |  C           |
|     B     |  A           |
|     E     |  C           |
|     E     |  A           |

结果图模型:

示例查询:

UPDATE component
SET price = 2
WHERE id = 'A';

期望的结果(* 表示递归更新的值)

| id | price |
|----|------ |
| A  | 2     |
| B  | 2     | *
| C  | 1     |
| D  | 3     | *
| E  | 3     | * 

我想我需要将整个树关系存储在 component_closure 表中，以便我能够检索所有组件的 component_of 关系并使用深度列来确定组件的顺序。虽然当不需要全树时这看起来很浪费，例如直接的 components_of。

例如:

| component | component_of | depth |
|-----------|--------------|-------|
|  D        | A            | 1     |
|  D        | B            | 2     |
|  D        | C            | 1     |

Answer 1

是的，如果要存储传递闭包，就需要存储所有的路径。

对于某些操作，存储长度为 0 的路径甚至会有帮助:

| component | component_of | depth |
|-----------|--------------|-------|
|  D        | D            | 0     |
|  D        | A            | 1     |
|  D        | B            | 2     |
|  C        | C            | 0     |
|  B        | B            | 0     |
|  B        | A            | 1     |
|  A        | A            | 0     |

在 MySQL 8.0 中，这些都不需要了。我们最终将能够使用递归查询。