这篇文章主要介绍了在PostgreSQL中使用ltree处理层次结构数据,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下。
在本文中，我们将学习如何使用PostgreSQL的ltree模块，该模块允许以分层的树状结构存储数据。
什么是ltree？
Ltree是PostgreSQL模块。它实现了一种数据类型ltree，用于表示存储在分层树状结构中的数据的标签。提供了用于搜索标签树的广泛工具。
为什么选择ltree？
ltree实现了一个物化路径，对于INSERT / update / DELETE来说非常快，而对于SELECT操作则较快
通常，它比使用经常需要重新计算分支的递归CTE或递归函数要快
如内置的查询语法和专门用于查询和导航树的运算符
索引！！！
初始数据
首先，您应该在数据库中启用扩展。您可以通过以下命令执行此操作：
CREATE 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);
另外，我们应该添加一些索引：
CREATE index path_gist_comments_idx ON comments USING GIST(path);CREATE index path_comments_idx ON comments USING btree(path);
正如您看到的那样，我建立comments表时带有path字段，该字段包含该表的tree全部路径。如您所见，对于树分隔符，我使用4个数字和点。
让我们在comments表中找到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)
如果你的路径不是唯一的，你会得到多条记录。
