這篇文章主要介紹了在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù),本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下。
在本文中,我們將學(xué)習(xí)如何使用PostgreSQL的ltree模塊,該模塊允許以分層的樹狀結(jié)構(gòu)存儲數(shù)據(jù)。
什么是ltree?
Ltree是PostgreSQL模塊。它實(shí)現(xiàn)了一種數(shù)據(jù)類型ltree,用于表示存儲在分層樹狀結(jié)構(gòu)中的數(shù)據(jù)的標(biāo)簽。提供了用于搜索標(biāo)簽樹的廣泛工具。
為什么選擇ltree?
ltree實(shí)現(xiàn)了一個(gè)物化路徑,對于INSERT / UPDATE / DELETE來說非???,而對于SELECT操作則較快
通常,它比使用經(jīng)常需要重新計(jì)算分支的遞歸CTE或遞歸函數(shù)要快
如內(nèi)置的查詢語法和專門用于查詢和導(dǎo)航樹的運(yùn)算符
索引?。?!
初始數(shù)據(jù)
首先,您應(yīng)該在數(shù)據(jù)庫中啟用擴(kuò)展。您可以通過以下命令執(zhí)行此操作:
1CREATE EXTENSION ltree;
讓我們創(chuàng)建表并向其中添加一些數(shù)據(jù):
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');
另外,我們應(yīng)該添加一些索引:
1
2CREATE INDEX path_gist_comments_idx ON comments USING GIST(path);CREATE INDEX path_comments_idx ON comments USING btree(path);
正如您看到的那樣,我建立comments表時(shí)帶有path字段,該字段包含該表的tree全部路徑。如您所見,對于樹分隔符,我使用4個(gè)數(shù)字和點(diǎn)。
讓我們在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)
你不應(yīng)該忘記數(shù)據(jù)的順序,如下的例子:
$ 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)
現(xiàn)在進(jìn)行排序:
$ 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的非星號標(biāo)簽的末尾添加幾個(gè)修飾符,以使其比完全匹配更匹配:
“ @”-不區(qū)分大小寫匹配,例如a @匹配A
“ *”-匹配任何帶有該前綴的標(biāo)簽,例如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
申請創(chuàng)業(yè)報(bào)道,分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處,共同探討創(chuàng)業(yè)新機(jī)遇!