等效于SQL中图结构的闭包表 [英] Closure table equivalent for graph structures in SQL

问题描述

此问题如何在sql中存储树结构? 关闭表的想法，该表用于存储在很多方式.

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中的图结构是否存在影响.我看到了这篇论文，它似乎概述了图形索引结构，但是我有点头疼想知道是否有一种方法可以创建一些辅助表来处理SQL中对图形数据的常见查询.

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.

推荐答案

我做了您链接的演示文稿，有人问我要用类似的方法实现一般图形，但我从来没有解决过.

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.

在SQL中使用递归CTE可能会更容易，但是我最经常使用的MySQL直到8.0版才支持CTE语法.而且，如果您确实具有递归CTE功能，则最好使用它而不是闭包表，因为发生数据异常的可能性较小.

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.

另一种选择是浏览专门的图形数据库.对于MySQL/MariaDB，有一个社区存储引擎可以针对树和图查询进行优化: https://openquery.com.au/products/graph-engine

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

这篇关于等效于SQL中图结构的闭包表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！