数据库如何设计树形结构

chensoul   database

在 MySQL 中，设计树形结构的区域表有多种方式。以下是一些常见的方案：

  1. 父子关系（Parent-Child Relationship）模型：在这种模型中，每个行记录包含一个指向其父级的引用。可以使用一个额外的列来存储父级 ID，或者使用自连接表来表示关系。这种模型简单直观，易于理解和管理。
  2. 路径（Path）模型：在这种模型中，每个行记录都包含一个代表其完整路径的字段。路径可以是以某种分隔符（如斜杠）分隔的字符串，例如：/地区/国家/城市。通过解析和处理路径字段，可以轻松地查询父级、子级和兄弟节点。
  3. 嵌套集模型（Nested Set Model）：这是一种基于左右值的模型，通过预先计算每个节点的左右值，可以高效地查询树形结构。每个节点都有一个左值和一个右值，用于表示其在树中的位置。这种模型适用于大型树结构，但需要特殊的操作来维护左右值。
  4. 物化路径（Materialized Path）模型：这是路径模型的一种改进版本，它使用额外的列来存储节点的层级关系。除了路径字段外，还可以添加一个表示节点级别的字段。这样可以更高效地进行查询，并且可以轻松地获取节点的父级、子级和兄弟节点。

父子关系（Parent-Child Relationship）模型

父子关系（Parent-Child Relationship）模型是一种在 MySQL 中设计树形结构的方式。在该模型中，每个区域记录包含一个指向其父级区域的引用。通过这种父子关系，可以建立区域之间的层级结构。

以区域为例，我们可以创建一个名为"area"的表来存储区域信息。该表可以包含以下列：

  • id：区域的唯一标识符（主键）
  • name：区域的名称
  • parent_id：指向父级区域的引用

通过使用父子关系模型，我们可以创建以下区域的层级结构：

id | name       | parent_id
---------------------------
1  | 世界       | NULL
2  | 亚洲       | 1
3  | 欧洲       | 1
4  | 北美洲     | 1
5  | 中国       | 2
6  | 日本       | 2
7  | 德国       | 3
8  | 法国       | 3
9  | 美国       | 4
10 | 加拿大     | 4
11 | 北京市     | 5
12 | 上海市     | 5
13 | 东京都     | 6
14 | 横滨市     | 6

在上述示例中，“area"表的每一行代表一个区域，通过"parent_id"列建立父子关系。根区域（世界）的"parent_id"为 NULL，表示没有父级区域。其他区域通过指定父级区域的"id"来建立层级关系。

查询父区域

使用这种模型，我们可以轻松地查询区域的父级、子级和兄弟节点。例如，要查找中国的父级区域，可以通过以下查询实现：

SELECT * FROM area WHERE id = (
    SELECT parent_id FROM area WHERE name = '中国'
);

要查询中国的所有父级区域，可以使用递归查询（Recursive Query）来实现。在 MySQL 中，递归查询可以使用WITH RECURSIVE关键字进行构建。以下是一条查询中国的所有父级区域的 SQL 语句：

WITH RECURSIVE area_hierarchy AS (
    SELECT id, name, parent_id
    FROM area
    WHERE name = '中国'
    UNION ALL
    SELECT r.id, r.name, r.parent_id
    FROM area r
    INNER JOIN area_hierarchy rh ON r.id = rh.parent_id
)
SELECT * FROM area_hierarchy;

上述查询语句使用了 WITH RECURSIVE 子句来创建名为"area_hierarchy"的递归查询。初始查询选择名称为"中国"的区域记录作为起始点。然后，使用 UNION ALL 将初始查询结果与后续的递归查询结果连接起来，通过连接条件将父级区域与子级区域关联起来，直到没有更多的父级区域可供连接。

最终的 SELECT 语句从"area_hierarchy"中选择所有的父级区域记录，并返回结果集。

请注意，上述查询假设区域的名称（name）列是唯一的，因此使用名称进行查询是准确的。如果存在多个具有相同名称的区域记录，可能需要根据其他条件进行进一步的筛选。

查询子区域

同样地，我们可以查询中国的子级区域，例如：

SELECT * FROM area WHERE parent_id = (
    SELECT id FROM area WHERE name = '中国'
);

要查询中国的所有子级区域，可以使用递归查询（Recursive Query）来实现。在 MySQL 中，递归查询可以使用 WITH RECURSIVE 关键字进行构建。以下是一条查询中国的所有子级区域的 SQL 语句：

WITH RECURSIVE area_hierarchy AS (
    SELECT id, name, parent_id
    FROM area
    WHERE name = '中国'
    UNION ALL
    SELECT r.id, r.name, r.parent_id
    FROM area r
    INNER JOIN area_hierarchy rh ON r.parent_id = rh.id
)
SELECT * FROM area_hierarchy;

上述查询语句使用了 WITH RECURSIVE 子句来创建名为"area_hierarchy"的递归查询。初始查询选择名称为"中国"的区域记录作为起始点。然后，使用 UNION ALL 将初始查询结果与后续的递归查询结果连接起来，通过连接条件将子级区域与父级区域关联起来，直到没有更多的子级区域可供连接。

最终的 SELECT 语句从"area_hierarchy"中选择所有的子级区域记录，并返回结果集。

请注意，上述查询假设区域的名称（name）列是唯一的，因此使用名称进行查询是准确的。如果存在多个具有相同名称的区域记录，可能需要根据其他条件进行进一步的筛选。

查询层级

要查询区域的层级，可以使用递归查询（Recursive Query）来实现。以下是一条查询区域的层级的 SQL 语句：

WITH RECURSIVE area_hierarchy AS (
    SELECT id, name, parent_id, 0 AS level
    FROM area
    WHERE parent_id IS NULL
    UNION ALL
    SELECT r.id, r.name, r.parent_id, rh.level + 1
    FROM area r
    INNER JOIN area_hierarchy rh ON r.parent_id = rh.id
)
SELECT id, name, level FROM area_hierarchy;

上述查询语句使用了 WITH RECURSIVE 子句来创建名为"area_hierarchy"的递归查询。初始查询选择没有父级区域（根区域）的记录作为起始点，并将层级（level）设置为 0。然后，使用 UNION ALL 将初始查询结果与后续的递归查询结果连接起来，通过连接条件将子级区域与父级区域关联起来，并递增层级（level）。

最终的 SELECT 语句从"area_hierarchy"中选择区域的唯一标识符（id）、名称（name）和层级（level）字段，并返回结果集。

查询结果将包含每个区域的唯一标识符、名称和其在层级结构中的层级信息。

请注意，上述查询假设区域的父子关系是正确的，且没有循环依赖。如果存在错误的父子关系或循环依赖，可能会导致查询结果不准确或产生无限递归。

路径（Path）模型

路径（Path）模型是一种在数据库中表示层级结构的方法，它使用包含完整路径信息的字段来表示每个节点的位置。每个节点的路径由其祖先节点的标识符构成，以特定的分隔符分隔开来。路径模型可以用于表示树形结构、组织结构等。

以下是一个使用路径模型表示地理区域的示例。假设我们有一个名为"area"的表，其中包含以下列：

  • id：区域的唯一标识符（主键）
  • name：区域的名称
  • path：区域的路径

我们可以使用路径模型来表示以下地理区域的层级结构。

第一种，path 包括父节点 ID 和当前节点 ID，使用/作为分隔符。

id | name       | path
---------------------------
1  | 世界       | /1/
2  | 亚洲       | /1/2/
3  | 欧洲       | /1/3/
4  | 北美洲     | /1/4/
5  | 中国       | /1/2/5/
6  | 日本       | /1/2/6/
7  | 德国       | /1/3/7/
8  | 法国       | /1/3/8/
9  | 美国       | /1/4/9/
10 | 加拿大     | /1/4/10/
11 | 北京市     | /1/2/5/11/
12 | 上海市     | /1/2/5/12/
13 | 东京都     | /1/2/6/13/
14 | 横滨市     | /1/2/6/14/

在上述示例中，每个区域的路径列（path）都以斜杠（/）开始和结束。例如，中国的路径为”/1/2/5/"，表示从根区域（id 为 1）到中国的路径。

第一种，path 包括只父节点 ID，不包括当前节点 ID，使用/作为分隔符。

id | name     | path
---+----------+---------
1  | 世界     | /
2  | 亚洲     | /1/
3  | 欧洲     | /1/
4  | 北美洲   | /1/
5  | 中国     | /1/2/
6  | 日本     | /1/2/
7  | 德国     | /1/3/
8  | 法国     | /1/3/
9  | 美国     | /1/4/
10 | 纽约     | /1/4/9/
11 | 加利福尼亚 | /1/4/9/

实际使用过程中，个人倾向于使用第二种。第一种，需要保存区域之后，将 id 拼接到 path 做一次更新操作；而第二种只用做一次插入操作即可。

第二种建表语句：

CREATE TABLE area (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  path VARCHAR(255)
);

插入示例数据的语句：

INSERT INTO area (id, name, path) VALUES
(1, '世界', '/'),
(2, '亚洲', '/1/'),
(3, '欧洲', '/1/'),
(4, '北美洲', '/1/'),
(5, '中国', '/1/2/'),
(6, '日本', '/1/2/'),
(7, '德国', '/1/3/'),
(8, '法国', '/1/3/'),
(9, '美国', '/1/4/'),
(10, '纽约', '/1/4/9/'),
(11, '加利福尼亚', '/1/4/9/');

查询父节点

查询指定节点的直接父节点，您可以使用以下 SQL 查询语句：

SELECT *,
	SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', -2), '/', 1) AS parent_id,
	(LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) AS level
FROM area
WHERE 'your_specific_path' = CONCAT(path, id, '/')

查询指定节点的所有父节点

SELECT *,
	SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', -2), '/', 1) AS parent_id,
	(LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) AS level
FROM area
WHERE 'your_specific_path' = CONCAT(path, id, '%')
ORDER BY LENGTH(path);

请将 your_specific_path 替换为您要查找直接父节点的节点的实际路径值。

查询子节点

要查询指定节点的直接子节点，您可以使用以下 SQL 查询语句：

SELECT *,
	SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', -2), '/', 1) AS parent_id,
	(LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) AS level
FROM area
WHERE path = CONCAT('your_specific_path','your_specific_id','/')

查询指定节点的所有子节点：

SELECT *,
	SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', -2), '/', 1) AS parent_id,
	(LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) AS level
FROM area
WHERE path like CONCAT('your_specific_path','your_specific_id','%')

查询兄弟节点

查询指定节点的兄弟节点：

SELECT *,
	SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', -2), '/', 1) AS parent_id,
	(LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) AS level
FROM area
WHERE path = 'your_specific_path' and id <> 'your_specific_id'

查询叶子节点

查询所有的叶子节点：

SELECT *,
	SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', -2), '/', 1) AS parent_id,
	(LENGTH(path) - LENGTH(REPLACE(path, '/', ''))) AS level
FROM area
WHERE id NOT IN (
  SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(path, '/', -2), '/', 1)
  FROM area
  WHERE path LIKE '/%'
);

判断是否为叶子节点

判断指定节点是否为叶子节点：

SELECT count(1)=0
FROM area
WHERE path LIKE CONCAT('your_specific_path','your_specific_id','%')

嵌套集模型（Nested Set Model）

嵌套集模型（Nested Set Model）是一种用于表示树形结构数据的数据库设计模式。它使用左右值（Left and Right Values）来表示每个节点在树中的位置关系。嵌套集模型的特点是可以高效地执行树形结构的查询，如获取节点的所有子节点、父节点、兄弟节点等。

在嵌套集模型中，每个节点都有两个值：左值（Left Value）和右值（Right Value）。左值表示节点在树中的进入顺序，右值表示节点在树中的离开顺序。通过这种方式，树中的每个节点都可以用一个范围（左值和右值之间的范围）来表示。

下面是一个示例，展示了使用嵌套集模型表示的树形结构：

id | name       | left_value | right_value
-----------------------------------------
1  | 世界       | 1          | 14
2  | 亚洲       | 2          | 9
3  | 欧洲       | 3          | 8
4  | 北美洲     | 10         | 13
5  | 中国       | 4          | 7
6  | 日本       | 5          | 6
7  | 德国       | 11         | 12

在上述示例中，我们使用嵌套集模型表示了一棵树形结构，其中包含了世界、亚洲、欧洲、北美洲、中国、日本和德国等区域。

  • 世界是根节点，其左值为 1，右值为 14。
  • 亚洲是世界的子节点，其左值为 2，右值为 9。
  • 欧洲是世界的子节点，其左值为 3，右值为 8。
  • 北美洲是世界的子节点，其左值为 10，右值为 13。
  • 中国是亚洲的子节点，其左值为 4，右值为 7。
  • 日本是亚洲的子节点，其左值为 5，右值为 6。
  • 德国是欧洲的子节点，其左值为 11，右值为 12。

通过使用左右值，我们可以轻松地执行一些常见的树形结构查询：

  • 获取中国的所有子区域：

    SELECT * FROM area WHERE left_value > 4 AND right_value < 7;

  • 获取亚洲的父区域：

    SELECT * FROM area WHERE left_value < 2 AND right_value > 9 LIMIT 1;

  • 获取日本的所有兄弟区域：

    SELECT * FROM area WHERE left_value > 4 AND right_value < 7 AND id != 6;

物化路径（Materialized Path）模型

材料化路径（Materialized Path）模型：这是路径模型的一种改进版本，它使用额外的列来存储节点的层级关系。除了路径字段外，还可以添加一个表示节点级别的字段。这样可以更高效地进行查询，并且可以轻松地获取节点的父级、子级和兄弟节点。

以下是一个示例，展示了使用带有层级字段的路径模型表示区域的数据：

id | name       | path         | depth
-------------------------------------
1  | 世界       | /1/          | 0
2  | 亚洲       | /1/2/        | 1
3  | 欧洲       | /1/3/        | 1
4  | 北美洲     | /1/4/        | 1
5  | 中国       | /1/2/5/      | 2
6  | 日本       | /1/2/6/      | 2
7  | 德国       | /1/3/7/      | 2

在上述示例中，除了路径字段（path）外，增加了一个表示节点层级（depth）的列。节点的层级信息表示了节点在树中的深度，根节点的层级通常为 0。

通过添加层级字段，可以更高效地进行查询，并且轻松地获取节点的父级、子级和兄弟节点。例如：

  • 获取节点的父级节点：

    SELECT * FROM area WHERE depth = <node_depth> - 1;

  • 获取节点的父级节点

    SELECT * FROM area WHERE depth < <node_depth>

    在上述查询中，假设 <node_depth> 是要查询节点的层级。通过将节点的层级与数据库中的 depth 字段进行比较，我们可以筛选出所有层级小于指定节点层级的记录，即节点的所有父级节点。

    这种方法相对简单且直观，无需对路径进行字符串匹配和比较。然而，使用 depth 字段的前提是节点的层级信息是正确且一致的，并且在插入、更新和删除节点时，正确地维护 depth 字段的值。

    请注意，使用 depth 字段来获取节点的所有父级节点可能会受到性能方面的限制，尤其是在具有大量记录和深层次树结构的情况下。如果性能成为问题，您可能需要考虑使用其他数据模型或结合索引和缓存等技术来提高查询性能。

  • 获取节点的子级节点：

    SELECT * FROM area WHERE depth = <node_depth> + 1;

  • 获取节点的所有子级节点：

    SELECT * FROM area WHERE depth > <node_depth>

    在上述查询中，假设 <node_depth> 是要查询节点的层级。通过将节点的层级与数据库中的 depth 字段进行比较，我们可以筛选出所有层级大于指定节点层级的记录，即节点的所有子级节点。

  • 获取节点的兄弟节点：

    SELECT * FROM area WHERE depth = <node_depth> AND id != <node_id>;

通过使用带有层级字段的路径模型，查询和操作树形结构数据可以更加高效和直观。这种模型可以结合路径信息和层级信息，提供更灵活和便捷的查询能力。

总结

在 MySQL 中设计树形结构的区域表时，有多种常用的方法可供选择：

  1. 父节点引用（Parent-Reference）：在区域表中添加一个指向父节点的外键列。简单直观，每个节点都包含其父节点的引用。但查询需要使用递归或自连接。
  2. 路径（Path）：在区域表中添加一个表示节点路径的字符串字段。路径可以是层级关系的完整表示，方便查询父节点、子节点和整个子树。
  3. 左右值（Nested Set）：使用左右值模型表示树形结构，为每个节点添加左右边界字段。查询节点的父节点、子节点和整个子树时不需要递归，但更新操作可能影响性能。
  4. 嵌套集合路径（Nested Set Path）：结合路径和左右值的方法，在区域表中添加路径和左右值字段。方便查询节点的父节点、子节点和整个子树，同时避免了左右值模型的更新性能问题。

每种方法都有其优点和限制，选择适合您应用需求、查询频率、数据量和性能要求的方法至关重要。

在设计树形结构表时，需要考虑到查询的复杂性、数据一致性、更新操作的性能和数据量的大小。根据具体情况进行权衡和选择合适的设计方案。

Share this post:

Related content