您的位置:首页 > 站长 > 编程技术 > 正文

在PostgreSQL中使用ltree处理层次结构数据的方法

2021-10-29 15:49:24来源:然猫网编辑:发财猫 我要投稿 浏览:

A-A+

  这篇文章主要介绍了在PostgreSQL中使用ltree处理层次结构数据,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下。

  在本文中,我们将学习如何使用PostgreSQL的ltree模块,该模块允许以分层的树状结构存储数据。

  什么是ltree?

  Ltree是PostgreSQL模块。它实现了一种数据类型ltree,用于表示存储在分层树状结构中的数据的标签。提供了用于搜索标签树的广泛工具。

  为什么选择ltree?

  ltree实现了一个物化路径,对于INSERT / UPDATE / DELETE来说非常快,而对于SELECT操作则较快

  通常,它比使用经常需要重新计算分支的递归CTE或递归函数要快

  如内置的查询语法和专门用于查询和导航树的运算符

  索引!!!

  初始数据

  首先,您应该在数据库中启用扩展。您可以通过以下命令执行此操作:

  1CREATE EXTENSION ltree;

  让我们创建表并向其中添加一些数据:

  CREATE TABLE comments (user_id integer, description text, path ltree);

  INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001');

  INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001');

  INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0001.0001.0001');

  INSERT INTO comments (user_id, description, path) VALUES ( 1, md5(random()::text), '0001.0001.0001.0002');

  INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0001.0001.0003');

  INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002');

  INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0002.0001');

  INSERT INTO comments (user_id, description, path) VALUES ( 6, md5(random()::text), '0001.0003');

  INSERT INTO comments (user_id, description, path) VALUES ( 8, md5(random()::text), '0001.0003.0001');

  INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0002');

  INSERT INTO comments (user_id, description, path) VALUES ( 11, md5(random()::text), '0001.0003.0002.0001');

  INSERT INTO comments (user_id, description, path) VALUES ( 2, md5(random()::text), '0001.0003.0002.0002');

  INSERT INTO comments (user_id, description, path) VALUES ( 5, md5(random()::text), '0001.0003.0002.0003');

  INSERT INTO comments (user_id, description, path) VALUES ( 7, md5(random()::text), '0001.0003.0002.0002.0001');

  INSERT INTO comments (user_id, description, path) VALUES ( 20, md5(random()::text), '0001.0003.0002.0002.0002');

  INSERT INTO comments (user_id, description, path) VALUES ( 31, md5(random()::text), '0001.0003.0002.0002.0003');

  INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0004');

  INSERT INTO comments (user_id, description, path) VALUES ( 34, md5(random()::text), '0001.0003.0002.0002.0005');

  INSERT INTO comments (user_id, description, path) VALUES ( 22, md5(random()::text), '0001.0003.0002.0002.0006');

  另外,我们应该添加一些索引:

  1

  2CREATE INDEX path_gist_comments_idx ON comments USING GIST(path);CREATE INDEX path_comments_idx ON comments USING btree(path);

  正如您看到的那样,我建立comments表时带有path字段,该字段包含该表的tree全部路径。如您所见,对于树分隔符,我使用4个数字和点。

  让我们在commenets表中找到path以‘0001.0003'的记录:

  $ SELECT user_id, path FROM comments WHERE path <@ '0001.0003';

  user_id | path

  ---------+--------------------------

  6 | 0001.0003

  8 | 0001.0003.0001

  9 | 0001.0003.0002

  11 | 0001.0003.0002.0001

  2 | 0001.0003.0002.0002

  5 | 0001.0003.0002.0003

  7 | 0001.0003.0002.0002.0001

  20 | 0001.0003.0002.0002.0002

  31 | 0001.0003.0002.0002.0003

  22 | 0001.0003.0002.0002.0004

  34 | 0001.0003.0002.0002.0005

  22 | 0001.0003.0002.0002.0006

  (12 rows)

  你不应该忘记数据的顺序,如下的例子:

  $ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0001');

  $ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0002');

  $ INSERT INTO comments (user_id, description, path) VALUES ( 9, md5(random()::text), '0001.0003.0001.0003');

  $ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*';

  user_id | path

  ---------+--------------------------

  6 | 0001.0003

  8 | 0001.0003.0001

  9 | 0001.0003.0002

  11 | 0001.0003.0002.0001

  2 | 0001.0003.0002.0002

  5 | 0001.0003.0002.0003

  7 | 0001.0003.0002.0002.0001

  20 | 0001.0003.0002.0002.0002

  31 | 0001.0003.0002.0002.0003

  22 | 0001.0003.0002.0002.0004

  34 | 0001.0003.0002.0002.0005

  22 | 0001.0003.0002.0002.0006

  9 | 0001.0003.0001.0001

  9 | 0001.0003.0001.0002

  9 | 0001.0003.0001.0003

  (15 rows)

  现在进行排序:

  $ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;

  user_id | path

  ---------+--------------------------

  6 | 0001.0003

  8 | 0001.0003.0001

  9 | 0001.0003.0001.0001

  9 | 0001.0003.0001.0002

  9 | 0001.0003.0001.0003

  9 | 0001.0003.0002

  11 | 0001.0003.0002.0001

  2 | 0001.0003.0002.0002

  7 | 0001.0003.0002.0002.0001

  20 | 0001.0003.0002.0002.0002

  31 | 0001.0003.0002.0002.0003

  22 | 0001.0003.0002.0002.0004

  34 | 0001.0003.0002.0002.0005

  22 | 0001.0003.0002.0002.0006

  5 | 0001.0003.0002.0003

  (15 rows)

  可以在lquery的非星号标签的末尾添加几个修饰符,以使其比完全匹配更匹配:

  “ @”-不区分大小写匹配,例如a @匹配A

  “ *”-匹配任何带有该前缀的标签,例如foo *匹配foobar

  “%”-匹配以下划线开头的单词

  $ SELECT user_id, path FROM comments WHERE path ~ '0001.*{1,2}.0001|0002.*' ORDER by path;

  user_id | path

  ---------+--------------------------

  2 | 0001.0001.0001

  2 | 0001.0001.0001.0001

  1 | 0001.0001.0001.0002

  5 | 0001.0001.0001.0003

  6 | 0001.0002.0001

  8 | 0001.0003.0001

  9 | 0001.0003.0001.0001

  9 | 0001.0003.0001.0002

  9 | 0001.0003.0001.0003

  9 | 0001.0003.0002

  11 | 0001.0003.0002.0001

  2 | 0001.0003.0002.0002

  7 | 0001.0003.0002.0002.0001

  20 | 0001.0003.0002.0002.0002

  31 | 0001.0003.0002.0002.0003

  22 | 0001.0003.0002.0002.0004

  34 | 0001.0003.0002.0002.0005

  22 | 0001.0003.0002.0002.0006

  5 | 0001.0003.0002.0003

  (19 rows)

  我们来为parent ‘0001.0003'找到所有直接的childrens,见下:

  $ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*{1}' ORDER by path;

  user_id | path

  ---------+----------------

  8 | 0001.0003.0001

  9 | 0001.0003.0002

  (2 rows)

  为parent ‘0001.0003'找到所有的childrens,见下:

  $ SELECT user_id, path FROM comments WHERE path ~ '0001.0003.*' ORDER by path;

  user_id | path

  ---------+--------------------------

  6 | 0001.0003

  8 | 0001.0003.0001

  9 | 0001.0003.0001.0001

  9 | 0001.0003.0001.0002

  9 | 0001.0003.0001.0003

  9 | 0001.0003.0002

  11 | 0001.0003.0002.0001

  2 | 0001.0003.0002.0002

  7 | 0001.0003.0002.0002.0001

  20 | 0001.0003.0002.0002.0002

  31 | 0001.0003.0002.0002.0003

  22 | 0001.0003.0002.0002.0004

  34 | 0001.0003.0002.0002.0005

  22 | 0001.0003.0002.0002.0006

  5 | 0001.0003.0002.0003

  (15 rows)

  为children ‘0001.0003.0002.0002.0005'找到parent:

  $ SELECT user_id, path FROM comments WHERE path = subpath('0001.0003.0002.0002.0005', 0, -1) ORDER by path;

  user_id | path

  ---------+---------------------

  2 | 0001.0003.0002.0002

  (1 row)

  如果你的路径不是唯一的,你会得到多条记录。

相关推荐

123

微信扫一扫

然猫网微信账号