當(dāng)前位置:首頁 >  站長(zhǎng) >  編程技術(shù) >  正文

在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù)的方法

 2021-04-20 16:45  來源: 腳本之家   我來投稿 撤稿糾錯(cuò)

  域名預(yù)訂/競(jìng)價(jià),好“米”不錯(cuò)過

這篇文章主要介紹了在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù),本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下。

在本文中,我們將學(xué)習(xí)如何使用PostgreSQL的ltree模塊,該模塊允許以分層的樹狀結(jié)構(gòu)存儲(chǔ)數(shù)據(jù)。

什么是ltree?

Ltree是PostgreSQL模塊。它實(shí)現(xiàn)了一種數(shù)據(jù)類型ltree,用于表示存儲(chǔ)在分層樹狀結(jié)構(gòu)中的數(shù)據(jù)的標(biāo)簽。提供了用于搜索標(biāo)簽樹的廣泛工具。

為什么選擇ltree?

ltree實(shí)現(xiàn)了一個(gè)物化路徑,對(duì)于INSERT / UPDATE / DELETE來說非??欤鴮?duì)于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全部路徑。如您所見,對(duì)于樹分隔符,我使用4個(gè)數(shù)字和點(diǎn)。

讓我們?cè)赾ommenets表中找到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的非星號(hào)標(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)

如果你的路徑不是唯一的,你會(huì)得到多條記錄。

文章來源:腳本之家

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

申請(qǐng)創(chuàng)業(yè)報(bào)道,分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處,共同探討創(chuàng)業(yè)新機(jī)遇!

相關(guān)文章

熱門排行

信息推薦