sqlite - 查询 SQLite 树结构

Question

我有一个这样的表数据库:
tree{id,name,parent}content{id,content,parent}tree 表包含一个树状结构，如果 parent 是 0，它是顶级元素，如果不同，它是来自同一个表的 parent 的 id。
content 表有一个 parent 列，它是 tree 表中的一个 id。

我想要的是在给定内容 ID 时获得整个父子元素链。

我认为这不是表格的最佳结构，我认为我可以改变这一点。

请问您对此怎么看？

Answer 1

首先，我建议使用 parent = NULL 而不是零来表示根节点，这将允许 tree 在 parent 上有一个引用 id 的外键；参照完整性很有用。

然后，您可以在每行中包含从根节点到当前节点的 materialized path。物化路径将是一个字符串列，表示从根节点到当前节点的路径；出于您的目的，您希望为路径中的每个节点使用固定宽度的格式，然后您可以对路径进行 ASCII 排序以按树顺序提取记录(如在 answer 中)。

假设您认为 99999 足以覆盖您的所有节点。那么你可能有一个这样的字符串路径:

'00001000110002300042'

这将表示 ID 序列 [1, 11, 23, 42]，因此节点的父节点将是 42，祖父节点是 23，依此类推，直到 1 的根。要获取从节点到根的整个分支:获取路径，将其分成几部分以获取ID，并在对物化路径进行排序的同时一次性拉出所有节点，以按正确的顺序将它们拉出。

这种方法还可以轻松地一次提取整个子树:只需构建与所需子树匹配的路径前缀，然后执行 path LIKE 'pfx%' ORDER BY path, id 即可一次提取整个子树。此外，大多数数据库将使用以 LIKE 开头的表达式的索引(即某些 LIKE 'X%' 的 X )，因此这些类型的查询可以非常快。您还可以使用简单的字符串长度和除法计算来计算节点的深度。

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