如何在sql中存储树结构？ [英] How to store tree structure in sql?

问题描述

这是我使用sqlite的架构，我不确定这是否是在sql中创建树结构的好方法，因为我必须遍历许多次才能获取整棵树，而反对在顶部注释的基础上提取整棵树并在python中构建树。有人可以给我一些建议。

Here is my schema using sqlite, I am not sure if this is a good way to create tree structure in sql as I have to traverse many time to obtain the entire tree, as oppose to extracting the entire tree base on top comment and construct the tree in python. Can someone give me some suggestions.

BEGIN;
CREATE TABLE "tree_comment" 
    ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
     "text" text NOT NULL, 
     "parent_id" integer NULL REFERENCES "tree_comment" ("id"));
CREATE INDEX "tree_comment_6be37982" ON "tree_comment" ("parent_id");

COMMIT;

推荐答案

您的示例是表示层次结构数据的正确方法关系数据库。您应该使用递归公用表表达式（R CTE）来查询表。

Your example is the correct way to represent hierarchical data in a relational database. You should use Recursive Common Table Expressions (R CTE) to query the table.

过去，您可能不得不使用嵌套集或实体化路径，但是R CTE是专门为修复其缺陷而设计的，因此请使用它。

In the past, you would have had to have used Nested Set or Materialized Path, but R CTE was purpose-built to fix their flaws, so use it.

SQLite支持递归CTE，它是SQL：1999标准的一部分。

SQLite supports Recursive CTE, and it's part of the SQL:1999 standard.

以下是示例查询：

http://blog.databasepatterns.com/2014/02/trees-paths-recursive-cte-postgresql.html

这篇关于如何在sql中存储树结构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！