This question How to store tree structure in sql? lead to the idea of a Closure table for storing trees that is optimal in many ways.
这个问题如何在sql中存储树结构?导致了Closure表的概念,用于存储在许多方面都是最佳的树。
The question is is there something along these lines for graph structures in SQL. I saw this paper which seems to outline a graph index structure but it's a bit over my head. Wondering if there is a sort of way to create a few auxiliary tables to handle common queries on graph data in SQL.
问题是SQL中的图形结构是否存在这些问题。我看到这篇文章似乎勾勒出一个图形索引结构,但它有点超出我的想象。想知道是否有一种方法可以创建一些辅助表来处理SQL中图形数据的常见查询。
1 个解决方案
#1
1
I did the presentation you linked to, and I've been asked about implementing general graphs with a similar method, but I've never gotten around to it.
我做了你链接的演示文稿,我被问到用类似的方法实现一般图表,但我从未接触过它。
Certainly there are problems with the technique if you have cyclic graphs, unless you can unambiguously identify a "starting node." Because otherwise if you start with any node in a cycle, you'd want to be able to traverse the whole cycle in the graph.
当然,如果你有循环图,那么该技术存在问题,除非你能明确地识别出“起始节点”。因为否则如果您从一个循环中的任何节点开始,您将希望能够遍历图中的整个循环。
It might be easier in SQL using a recursive CTE, but I most often use MySQL which doesn't support CTE syntax until version 8.0. And if you do have recursive CTE capability, you'd be better off using that instead of a closure table, because you have less chance for data anomalies.
在使用递归CTE的SQL中可能更容易,但我经常使用MySQL,它在版本8.0之前不支持CTE语法。如果你确实有递归CTE功能,那么最好使用它而不是闭包表,因为你有更少的数据异常机会。
Another option is to explore a specialized graph database. For MySQL/MariaDB, there's a community storage engine that optimizes for tree and graph queries: https://openquery.com.au/products/graph-engine
另一种选择是探索专门的图形数据库。对于MySQL / MariaDB,有一个社区存储引擎可以优化树和图查询:https://openquery.com.au/products/graph-engine
#1
1
I did the presentation you linked to, and I've been asked about implementing general graphs with a similar method, but I've never gotten around to it.
我做了你链接的演示文稿,我被问到用类似的方法实现一般图表,但我从未接触过它。
Certainly there are problems with the technique if you have cyclic graphs, unless you can unambiguously identify a "starting node." Because otherwise if you start with any node in a cycle, you'd want to be able to traverse the whole cycle in the graph.
当然,如果你有循环图,那么该技术存在问题,除非你能明确地识别出“起始节点”。因为否则如果您从一个循环中的任何节点开始,您将希望能够遍历图中的整个循环。
It might be easier in SQL using a recursive CTE, but I most often use MySQL which doesn't support CTE syntax until version 8.0. And if you do have recursive CTE capability, you'd be better off using that instead of a closure table, because you have less chance for data anomalies.
在使用递归CTE的SQL中可能更容易,但我经常使用MySQL,它在版本8.0之前不支持CTE语法。如果你确实有递归CTE功能,那么最好使用它而不是闭包表,因为你有更少的数据异常机会。
Another option is to explore a specialized graph database. For MySQL/MariaDB, there's a community storage engine that optimizes for tree and graph queries: https://openquery.com.au/products/graph-engine
另一种选择是探索专门的图形数据库。对于MySQL / MariaDB,有一个社区存储引擎可以优化树和图查询:https://openquery.com.au/products/graph-engine