在关系数据库中存储分层数据的选项

2023-09-11 08:42:24 · 2 min read · hierarchical-data relational-database tree database sql

摘要

本教程将介绍如何在关系数据库中存储分层数据的选项。我们将介绍不同的存储方法，如邻接表、嵌套集模型、桥接表和线性列等，以及它们的优缺点。

内容

在关系数据库中存储分层数据可能存在一些挑战，但是有几种方法可以解决这个问题。下面是一些常用的选项：

邻接表（Adjacency List）

邻接表是一种简单而直观的方法，它使用一个包含父节点ID的列来表示层级关系。每个节点在表中都有一行，该行包含其自身ID和父节点ID。

列：

ID：节点的唯一标识符
ParentID：父节点的ID

例如，以下是一个使用邻接表存储的示例：

1CREATE TABLE categories (
2  ID INT PRIMARY KEY,
3  ParentID INT,
4  Name VARCHAR(100)
5);

对于上述模式，我们可以这样插入数据：

 1INSERT INTO categories (ID, ParentID, Name)
 2VALUES (1, NULL, 'ELECTRONICS'),
 3       (2, 1, 'TELEVISIONS'),
 4       (3, 2, 'TUBE'),
 5       (4, 2, 'LCD'),
 6       (5, 2, 'PLASMA'),
 7       (6, 1, 'PORTABLE ELECTRONICS'),
 8       (7, 6, 'MP3 PLAYERS'),
 9       (8, 7, 'FLASH'),
10       (9, 6, 'CD PLAYERS'),
11       (10, 6, '2 WAY RADIOS');

使用邻接表可以轻松地查询父节点、子节点和层级关系。例如，要获取所有子节点，可以执行以下查询：

1SELECT *
2FROM categories
3WHERE ParentID = 2; -- 返回TELEVISIONS的所有子节点

优点：

简单直观，易于实现和理解
插入、更新和删除节点方便快捷

缺点：

需要多次查询才能查找所有后代节点
处理大量数据时可能性能较低

嵌套集模型（Nested Sets Model）

嵌套集模型使用两个列来表示节点的层次结构：左值（Left Value）和右值（Right Value）。每个节点使用这两个值定义一个闭区间，左值小于右值。嵌套集模型允许快速查询节点的子节点和祖先节点。

列：

ID：节点的唯一标识符
LeftValue：节点的左值
RightValue：节点的右值

以下是一个使用嵌套集模型存储的示例：

1CREATE TABLE categories (
2  ID INT PRIMARY KEY,
3  LeftValue INT,
4  RightValue INT,
5  Name VARCHAR(100)
6);

要插入数据，可以使用以下查询：

 1INSERT INTO categories (ID, LeftValue, RightValue, Name)
 2VALUES (1, 1, 20, 'ELECTRONICS'),
 3       (2, 2, 9, 'TELEVISIONS'),
 4       (3, 3, 4, 'TUBE'),
 5       (4, 5, 6, 'LCD'),
 6       (5, 7, 8, 'PLASMA'),
 7       (6, 10, 19, 'PORTABLE ELECTRONICS'),
 8       (7, 11, 14, 'MP3 PLAYERS'),
 9       (8, 12, 13, 'FLASH'),
10       (9, 15, 16, 'CD PLAYERS'),
11       (10, 17, 18, '2 WAY RADIOS');

使用嵌套集模型可以轻松地查询父节点、子节点和层级关系。例如，要获取所有子节点，可以执行以下查询：

1SELECT *
2FROM categories
3WHERE LeftValue > 2 AND RightValue < 9; -- 返回TELEVISIONS的所有子节点

优点：

快速查询节点的子节点和祖先节点
便于处理查询层级关系的需求

缺点：

插入、更新和删除节点可能较慢
容易出错并导致树状结构不再有效

桥接表（Bridge Table）

桥接表模型使用单独的连接表来表示父节点和子节点之间的关系。连接表中的每一行包含一个祖先节点和一个后代节点的ID。

列：

AncestorID：祖先节点的ID
DescendantID：后代节点的ID

以下是一个使用桥接表存储的示例：

1CREATE TABLE hierarchy (
2  AncestorID INT,
3  DescendantID INT,
4  Distance INT
5);

要插入数据，可以使用以下查询：

 1INSERT INTO hierarchy (AncestorID, DescendantID, Distance)
 2VALUES (1, 2, 1),
 3       (1, 3, 1),
 4       (2, 3, 1),
 5       (2, 4, 1),
 6       (2, 5, 1),
 7       (1, 6, 1),
 8       (6, 7, 1),
 9       (7, 8, 1),
10       (6, 9, 1),
11       (6, 10, 1);

使用桥接表可以轻松地查询父节点、子节点和层级关系。例如，要获取所有子节点，可以执行以下查询：

1SELECT c.*
2FROM hierarchy AS h
3JOIN categories AS c ON h.DescendantID = c.ID
4WHERE h.AncestorID = 2; -- 返回TELEVISIONS的所有子节点

优点：

快速查询父节点、子节点和层级关系
有效处理树结构的变化

缺点：

需要额外的连接表
需要维护连接表的数据一致性

线性列（Lineage Column）

线性列模型使用包含递归路径的单个列来表示节点的层级结构。该列存储每个节点从根节点到它本身的完整路径。

列：

Lineage：递归路径（例如：/parent/child/grandchild/...）

以下是一个使用线性列存储的示例：

1CREATE TABLE categories (
2  ID INT PRIMARY KEY,
3  Lineage TEXT,
4  Name VARCHAR(100)
5);

要插入数据，可以使用以下查询：

 1INSERT INTO categories (ID, Lineage, Name)
 2VALUES (1, '/ELECTRONICS/', 'ELECTRONICS'),
 3       (2, '/ELECTRONICS/TELEVISIONS/', 'TELEVISIONS'),
 4       (3, '/ELECTRONICS/TELEVISIONS/TUBE/', 'TUBE'),
 5       (4, '/ELECTRONICS/TELEVISIONS/LCD/', 'LCD'),
 6       (5, '/ELECTRONICS/TELEVISIONS/PLASMA/', 'PLASMA'),
 7       (6, '/ELECTRONICS/PORTABLE ELECTRONICS/', 'PORTABLE ELECTRONICS'),
 8       (7, '/ELECTRONICS/PORTABLE ELECTRONICS/MP3 PLAYERS/', 'MP3 PLAYERS'),
 9       (8, '/ELECTRONICS/PORTABLE ELECTRONICS/MP3 PLAYERS/FLASH/', 'FLASH'),
10       (9, '/ELECTRONICS/PORTABLE ELECTRONICS/CD PLAYERS/', 'CD PLAYERS'),
11       (10, '/ELECTRONICS/PORTABLE ELECTRONICS/2 WAY RADIOS/', '2 WAY RADIOS');

使用线性列可以轻松地查询父节点、子节点和层级关系。例如，要获取所有子节点，可以执行以下查询：

1SELECT *
2FROM categories
3WHERE Lineage LIKE '/ELECTRONICS/TELEVISIONS/%'; -- 返回TELEVISIONS的所有子节点

优点：

快速查询子节点和祖先节点
直观易于理解

缺点：

需要额外存储空间
需要解析和拆分路径数据

其他方案

除了上述的存储方法之外，还有一些其他的解决方案，如多层级列、路径枚举等。每种方案都有其优缺点，具体选择取决于你的需求和实际情况。

总结

在关系数据库中存储分层数据是一项具有挑战性的任务，但有几种方法可以解决这个问题。根据你的需求和实际情况，可以选择邻接表、嵌套集模型、桥接表和线性列等不同的存储方法。每种方法都有其优缺点，需要根据具体情况进行权衡和选择。

在关系数据库中存储分层数据的选项

摘要

内容

邻接表（Adjacency List）

列：

优点：

缺点：

嵌套集模型（Nested Sets Model）

列：

优点：

缺点：

桥接表（Bridge Table）

列：

优点：

缺点：

线性列（Lineage Column）

列：

优点：

缺点：

其他方案

总结

相关文章推荐