软文发布平台资讯中心

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

　 项目招商找A5 快速获取精准代理名单

这篇文章主要介绍了在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)

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

文章来源：脚本之家

来源地址：https://www.jb51.net/article/208595.htm

尊敬的看官您对在PostgreSQL中使用ltree处理层次结构数据的方法有什么看法呢？互联网品牌制作专家愿与您共同探讨！版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请加微信号oem365 举报，一经查实，本站将立刻删除。

上一篇: postgresql中的时间处理小技巧   返 回   下一篇:PostgreSQL将数据加载到buffercache中操作方法