1. <i id="s6b2k"><small id="s6b2k"></small></i>
    <b id="s6b2k"><bdo id="s6b2k"></bdo></b>
  2. <wbr id="s6b2k"></wbr>

    在PostgreSQL中使用ltree處理層次結構數據的方法_PostgreSQL

    來源:腳本之家  責任編輯:小易  

    在本文中,我們將學習如何使用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個數字和點。

    讓我們在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)

    讓我們通過EXPLAIN命令檢查這個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進行測試:

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

    現在SQL慢了,但是能看到SQL是怎么使用index的。
    第一個SQL語句使用了sequence scan,因為在表中沒有太多的數據。

    我們可以將select “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)

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

    概述

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

    了解更多PostgreSQL熱點資訊、新聞動態、精彩活動,請訪問中國PostgreSQL官方網站:www.postgresqlchina.com

    解決更多PostgreSQL相關知識、技術、工作問題,請訪問中國PostgreSQL官方問答社區:www.pgfans.cn

    下載更多PostgreSQL相關資料、工具、插件問題,請訪問中國PostgreSQL官方下載網站:www.postgreshub.cn

    到此這篇關于在PostgreSQL中使用ltree處理層次結構數據的文章就介紹到這了,更多相關PostgreSQL層次結構數據內容請搜索真格學網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持真格學網!

    您可能感興趣的文章:postgresql中的ltree類型使用方法PostgreSQL 恢復誤刪數據的操作postgreSQL數據庫默認用戶postgres常用命令分享SpringBoot連接使用PostgreSql數據庫的方法

  3. 本文相關:
  4. postgresql中like和ilike操作符的用法詳解
  5. postgresql 查找當前數據庫的所有表操作
  6. 修改postgresql存儲目錄的操作方式
  7. postgresql 使用raise函數打印字符串
  8. postgresql 計算時間差的秒數、天數實例
  9. docker環境下升級postgresql的步驟方法詳解
  10. postgresql 索引之 hash的使用詳解
  11. 15個postgresql數據庫實用命令分享
  12. postgresql刪除主鍵的操作
  13. postgresql 跨庫同步表及postgres_fdw的用法說明
  14. postgreSQL有沒有實現樹形結構查詢
  15. postgresql 數據庫問題
  16. 如何用postgresql操作幾何數據類型
  17. PostgreSQL數據庫,想在腳本里面指定連接的數據庫...
  18. 基于PostgreSQL數據庫的幾種數據加密實現方法
  19. 如何向postgresql數據庫里導入其它類型的數據?
  20. postgresql中使用if else語句
  21. 請教關于postgresql過程中,使用二維數組的問題
  22. 有postgresql的備份表結構及數據,想把這些數據導...
  23. 有沒辦法在postgreSQL中查詢oracle上的數據
  24. 網站首頁網頁制作腳本下載服務器操作系統網站運營平面設計媒體動畫電腦基礎硬件教程網絡安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess數據庫文摘數據庫其它首頁postgresql中的ltree類型使用方法postgresql 恢復誤刪數據的操作postgresql數據庫默認用戶postgres常用命令分享springboot連接使用postgresql數據庫的方法postgresql中like和ilike操作符的用法詳解postgresql 查找當前數據庫的所有表操作修改postgresql存儲目錄的操作方式postgresql 使用raise函數打印字符串postgresql 計算時間差的秒數、天數實例docker環境下升級postgresql的步驟方法詳解postgresql 索引之 hash的使用詳解15個postgresql數據庫實用命令分享postgresql刪除主鍵的操作postgresql 跨庫同步表及postgres_fdw的用法說明postgresql 角色與用戶管理介紹windows下postgresql數據庫的下載windows下postgresql安裝圖解postgresql中的oid和xid 說明15個postgresql數據庫實用命令分postgresql alter語句常用操作小windows postgresql 安裝圖文教程postgresql 安裝和簡單使用postgresql 創建表分區postgresql新手入門教程postgresql 序列增刪改案例postgresql自定義函數詳解postgresql數據庫事務出現未知狀態的處理postgresql 慢查詢sql跟蹤操作解決postgresql表中的字段名稱包含特殊符postgresql查詢鎖表以及解除鎖表操作postgresql 實現給查詢列表增加序號操作解決postgresql 將varchar類型字段修改為關于postgresql 行排序的實例解析postgresql教程(十八):客戶端命令(2)
    免責聲明 - 關于我們 - 聯系我們 - 廣告聯系 - 友情鏈接 - 幫助中心 - 頻道導航
    Copyright © 2017 www.yu113.com All Rights Reserved
    战天txt全集下载