查询SQLite树形结构

Question

我有一个像下面这样的表格数据库：

tree{id,name,parent}

content{id,content,parent}

table tree 包含树形结构，如果 parent 为 0，则是顶级元素，否则它是来自同一表中父元素的 ID。 content 表格具有来自 tree 表的 id 的 parent 列。

我想要的是，在给定内容 ID 的情况下，获取整个父子元素链。

我认为这不是最佳的表格结构，我认为我可以对其进行更改。

请问您的看法如何？

- Francisc

Answer 1

首先，我建议使用parent = NULL而不是0来表示根节点，这将允许tree在parent上具有外键，该外键引用id；引用完整性非常有用。

然后，您可以在每行中包括从根节点到当前节点的物化路径。物化路径将是一个字符串列，表示从根节点到当前节点的路径；对于您的目的，您需要为路径中的每个节点使用固定宽度格式，然后您可以按字母顺序对路径进行排序以按树顺序提取记录（就像此答案中所示）。

假设您认为99999足以涵盖所有节点。那么您可能会有一个类似于以下内容的字符串路径：

'00001000110002300042'

那将代表ID序列[1, 11, 23, 42]，所以节点的父节点将是42，祖父节点将是23，以此类推直到根节点1。要从节点到根获取整个分支：获取路径，将其拆分成片段以获取ID，并在对物化路径进行排序的同时一次性提取所有节点以按正确顺序输出它们。

这种方法还使得一次性提取整个子树变得容易：只需构建与所需子树匹配的路径前缀并执行path LIKE 'pfx%' ORDER BY path, id即可一次性提取整个子树。此外，大多数数据库将使用以开头为根的LIKE表达式（即LIKE 'X%'）的索引，因此这些查询可以非常快速。您还可以通过简单的字符串长度和除法计算来计算节点的深度。

您需要做一些额外的工作来构建物化路径，但不需要太多，它们使得许多树操作变得简单而保持了自然表示树的优点。

- mu is too short

---

物化路径在查询时非常出色，尤其是在查询方面。但对于写操作速度较慢。如果树是动态的、写入强度大、读取强度小且不是很深，则简单的id、parent递归可能更好。这高度依赖于访问模式。但通常，一个物化路径列是很好的选择。 - bryanmac

@bryanmac：一切都取决于访问模式 :) 我通常使用PostgreSQL，因此递归CTE或实现为数组和触发器的材质化路径通常可以解决问题。 - mu is too short

谢谢，mu。 - Francisc

@Michael 你在路径上有索引吗？一些数据库可以使用索引来执行某些锚定的LIKE查询，我不知道SQLite是否支持。 - mu is too short

@Michael 但是你的材料化路径将会是一个字符串，这就是你问题的根源，不是吗？ - mu is too short

Answer 2

或者您可以使用递归公共表达式

来实现。

- Ray Hulha