主頁(yè) > 知識(shí)庫(kù) > 在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù)的方法

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

熱門標(biāo)簽:北京智能外呼系統(tǒng)供應(yīng)商家 智能電銷機(jī)器人教育 電話機(jī)器人錄音師薪資 無(wú)錫梁溪公司怎樣申請(qǐng)400電話 高德地圖標(biāo)注電話怎么沒(méi)了 孝感銷售電銷機(jī)器人廠家 中國(guó)地圖標(biāo)注省份用什么符號(hào) 江西穩(wěn)定外呼系統(tǒng)供應(yīng)商 奧維地圖標(biāo)注字體大小修改

在本文中,我們將學(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來(lái)說(shuō)非常快,而對(duì)于SELECT操作則較快
  • 通常,它比使用經(jīng)常需要重新計(jì)算分支的遞歸CTE或遞歸函數(shù)要快
  • 如內(nèi)置的查詢語(yǔ)法和專門用于查詢和導(dǎo)航樹的運(yùn)算符
  • 索引?。?!

初始數(shù)據(jù)

首先,您應(yīng)該在數(shù)據(jù)庫(kù)中啟用擴(kuò)展。您可以通過(guò)以下命令執(zhí)行此操作:

CREATE 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)該添加一些索引:

CREATE INDEX path_gist_comments_idx ON comments USING GIST(path);
CREATE INDEX path_comments_idx ON comments USING btree(path);

正如您看到的那樣,我建立comments表時(shí)帶有path字段,該字段包含該表的tree全部路徑。如您所見(jiàn),對(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)

讓我們通過(guò)EXPLAIN命令檢查這個(gè)SQL:

$ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path @ '0001.0003';
            QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on comments (cost=0.00..1.24 rows=2 width=38) (actual time=0.013..0.017 rows=12 loops=1)
 Filter: (path @ '0001.0003'::ltree)
 Rows Removed by Filter: 7
 Total runtime: 0.038 ms
(4 rows)

讓我們禁用seq scan進(jìn)行測(cè)試:

$ SET enable_seqscan=false;
SET
$ EXPLAIN ANALYZE SELECT user_id, path FROM comments WHERE path @ '0001.0003';
               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using path_gist_comments_idx on comments (cost=0.00..8.29 rows=2 width=38) (actual time=0.023..0.034 rows=12 loops=1)
 Index Cond: (path @ '0001.0003'::ltree)
 Total runtime: 0.076 ms
(3 rows)

現(xiàn)在SQL慢了,但是能看到SQL是怎么使用index的。
第一個(gè)SQL語(yǔ)句使用了sequence scan,因?yàn)樵诒碇袥](méi)有太多的數(shù)據(jù)。

我們可以將select “path @ ‘0001.0003'” 換種實(shí)現(xiàn)方法:

$ 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)

我們來(lái)為parent ‘0001.0003'找到所有直接的childrens,見(jiàn)下:

$ 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,見(jià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)

為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ì)得到多條記錄。

概述

可以看出,使用ltree的物化路徑非常簡(jiǎn)單。在本文中,我沒(méi)有列出ltree的所有可能用法。它不被視為全文搜索問(wèn)題ltxtquery。但是您可以在PostgreSQL官方文檔(http://www.postgresql.org/docs/current/static/ltree.html)中找到它。

了解更多PostgreSQL熱點(diǎn)資訊、新聞動(dòng)態(tài)、精彩活動(dòng),請(qǐng)?jiān)L問(wèn)中國(guó)PostgreSQL官方網(wǎng)站:www.postgresqlchina.com

解決更多PostgreSQL相關(guān)知識(shí)、技術(shù)、工作問(wèn)題,請(qǐng)?jiān)L問(wèn)中國(guó)PostgreSQL官方問(wèn)答社區(qū):www.pgfans.cn

下載更多PostgreSQL相關(guān)資料、工具、插件問(wèn)題,請(qǐng)?jiān)L問(wèn)中國(guó)PostgreSQL官方下載網(wǎng)站:www.postgreshub.cn

到此這篇關(guān)于在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù)的文章就介紹到這了,更多相關(guān)PostgreSQL層次結(jié)構(gòu)數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

您可能感興趣的文章:
  • PostGreSql 判斷字符串中是否有中文的案例
  • PostgreSQL的中文拼音排序案例
  • 自定義函數(shù)實(shí)現(xiàn)單詞排序并運(yùn)用于PostgreSQL(實(shí)現(xiàn)代碼)
  • PostgreSQL將數(shù)據(jù)加載到buffer cache中操作方法
  • postgresql 中的時(shí)間處理小技巧(推薦)
  • Postgresql限制用戶登錄錯(cuò)誤次數(shù)的實(shí)例代碼
  • PostgreSQL用戶登錄失敗自動(dòng)鎖定的處理方案
  • postgresql影子用戶實(shí)踐場(chǎng)景分析
  • 如何使用PostgreSQL進(jìn)行中文全文檢索

標(biāo)簽:海北 通化 泰州 那曲 齊齊哈爾 阜陽(yáng) 荊州 臨滄

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù)的方法》,本文關(guān)鍵詞  在,PostgreSQL,中,使用,ltree,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù)的方法》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù)的方法的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章