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

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

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

什么是ltree？

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

为什么选择ltree？

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

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

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

索引！！！

初始数据

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

1CREATEEXTENSIONltree;

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

CREATETABLEcomments(user_idinteger,descriptiontext,pathltree);
INSERTINTOcomments(user_id,description,path)VALUES(1,md5(random()::text),'0001');
INSERTINTOcomments(user_id,description,path)VALUES(2,md5(random()::text),'0001.0001.0001');
INSERTINTOcomments(user_id,description,path)VALUES(2,md5(random()::text),'0001.0001.0001.0001');
INSERTINTOcomments(user_id,description,path)VALUES(1,md5(random()::text),'0001.0001.0001.0002');
INSERTINTOcomments(user_id,description,path)VALUES(5,md5(random()::text),'0001.0001.0001.0003');
INSERTINTOcomments(user_id,description,path)VALUES(6,md5(random()::text),'0001.0002');
INSERTINTOcomments(user_id,description,path)VALUES(6,md5(random()::text),'0001.0002.0001');
INSERTINTOcomments(user_id,description,path)VALUES(6,md5(random()::text),'0001.0003');
INSERTINTOcomments(user_id,description,path)VALUES(8,md5(random()::text),'0001.0003.0001');
INSERTINTOcomments(user_id,description,path)VALUES(9,md5(random()::text),'0001.0003.0002');
INSERTINTOcomments(user_id,description,path)VALUES(11,md5(random()::text),'0001.0003.0002.0001');
INSERTINTOcomments(user_id,description,path)VALUES(2,md5(random()::text),'0001.0003.0002.0002');
INSERTINTOcomments(user_id,description,path)VALUES(5,md5(random()::text),'0001.0003.0002.0003');
INSERTINTOcomments(user_id,description,path)VALUES(7,md5(random()::text),'0001.0003.0002.0002.0001');
INSERTINTOcomments(user_id,description,path)VALUES(20,md5(random()::text),'0001.0003.0002.0002.0002');
INSERTINTOcomments(user_id,description,path)VALUES(31,md5(random()::text),'0001.0003.0002.0002.0003');
INSERTINTOcomments(user_id,description,path)VALUES(22,md5(random()::text),'0001.0003.0002.0002.0004');
INSERTINTOcomments(user_id,description,path)VALUES(34,md5(random()::text),'0001.0003.0002.0002.0005');
INSERTINTOcomments(user_id,description,path)VALUES(22,md5(random()::text),'0001.0003.0002.0002.0006');

另外，我们应该添加一些索引：

1

2CREATEINDEXpath_gist_comments_idxONcommentsUSINGGIST(path);CREATEINDEXpath_comments_idxONcommentsUSINGbtree(path);

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

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

$SELECTuser_id,pathFROMcommentsWHEREpath<@'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
(12rows)

你不应该忘记数据的顺序，如下的例子：

$INSERTINTOcomments(user_id,description,path)VALUES(9,md5(random()::text),'0001.0003.0001.0001');
$INSERTINTOcomments(user_id,description,path)VALUES(9,md5(random()::text),'0001.0003.0001.0002');
$INSERTINTOcomments(user_id,description,path)VALUES(9,md5(random()::text),'0001.0003.0001.0003');
$SELECTuser_id,pathFROMcommentsWHEREpath~'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
(15rows)

现在进行排序：

$SELECTuser_id,pathFROMcommentsWHEREpath~'0001.0003.*'ORDERbypath;
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
(15rows)

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

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

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

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

$SELECTuser_id,pathFROMcommentsWHEREpath~'0001.*{1,2}.0001|0002.*'ORDERbypath;
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
(19rows)

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

$SELECTuser_id,pathFROMcommentsWHEREpath~'0001.0003.*{1}'ORDERbypath;
user_id|path
---------+----------------
8|0001.0003.0001
9|0001.0003.0002
(2rows)

为parent‘0001.0003'找到所有的childrens，见下：

$SELECTuser_id,pathFROMcommentsWHEREpath~'0001.0003.*'ORDERbypath;
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
(15rows)

为children‘0001.0003.0002.0002.0005'找到parent:

$SELECTuser_id,pathFROMcommentsWHEREpath=subpath('0001.0003.0002.0002.0005',0,-1)ORDERbypath;
user_id|path
---------+---------------------
2|0001.0003.0002.0002
(1row)

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

Tags：

转载：感谢您对杨青青个人博客网站平台的认可，以及对我们原创作品以及文章的青睐，非常欢迎各位朋友分享到个人站长或者朋友圈，但转载请说明文章出处“来源杨青个人博客”。http://www.yangqq.com/jstt/bj/464.html

很赞哦！ ()