在PostgreSQL中使用ltree处理层次结构数据,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下。
在本文中，我们将学习如何使用PostgreSQL的ltree模块，该模块允许以分层的树状结构存储数据。
什么是ltree?
Ltree是PostgreSQL模块。它实现了一种数据类型ltree，用于表示存储在分层树状结构中的数据的标签。提供了用于搜索标签树的广泛工具。
为什么选择ltree?
ltree实现了一个物化路径，对于INSERT / UPDATE / DELETE来说非常快，而对于SELECT操作则较快
通常，它比使用经常需要重新计算分支的递归CTE或递归函数要快
如内置的查询语法和专门用于查询和导航树的运算符
索引!!!
初始数据
首先，您应该在数据库中启用扩展。您可以通过以下命令执行此操作：
1CREATE 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');
另外，我们应该添加一些索引：
1
2CREATE 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)
你不应该忘记数据的顺序，如下的例子：
$ 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)
如果你的路径不是唯一的，你会得到多条记录。
dribbble.com/jksia/collections/4691583
dribbble.com/jksia/collections/4691582
dribbble.com/jksia/collections/4691580
dribbble.com/jksia/collections/4691578
dribbble.com/nucei/collections/4691625
dribbble.com/nucei/collections/4691623
dribbble.com/nucei/collections/4691621
dribbble.com/nucei/collections/4691620
dribbble.com/nucei/collections/4691619
dribbble.com/nucei/collections/4691617
dribbble.com/nucei/collections/4691616
dribbble.com/nucei/collections/4691614
dribbble.com/nucei/collections/4691612
dribbble.com/nucei/collections/4691610
dribbble.com/nucei/collections/4691609
dribbble.com/nucei/collections/4691608
dribbble.com/bacio/collections/4691653
dribbble.com/bacio/collections/4691650
dribbble.com/bacio/collections/4691649
dribbble.com/bacio/collections/4691648
dribbble.com/bacio/collections/4691641
dribbble.com/bacio/collections/4691640
dribbble.com/bacio/collections/4691639
dribbble.com/bacio/collections/4691637
dribbble.com/bacio/collections/4691636
dribbble.com/bacio/collections/4691634
dribbble.com/bacio/collections/4691633
dribbble.com/bacio/collections/4691632
dribbble.com/uiwea/collections/4691689
dribbble.com/uiwea/collections/4691687
dribbble.com/uiwea/collections/4691681
dribbble.com/uiwea/collections/4691679
dribbble.com/uiwea/collections/4691677
dribbble.com/uiwea/collections/4691676
dribbble.com/uiwea/collections/4691675
dribbble.com/uiwea/collections/4691674
dribbble.com/uiwea/collections/4691670
dribbble.com/uiwea/collections/4691668
dribbble.com/uiwea/collections/4691665
dribbble.com/uiwea/collections/4691664
dribbble.com/nbdie/collections/4691723
dribbble.com/nbdie/collections/4691722
dribbble.com/nbdie/collections/4691720
dribbble.com/nbdie/collections/4691719
dribbble.com/nbdie/collections/4691718
dribbble.com/nbdie/collections/4691717
dribbble.com/nbdie/collections/4691714
dribbble.com/nbdie/collections/4691713
dribbble.com/nbdie/collections/4691711
dribbble.com/nbdie/collections/4691710
dribbble.com/nbdie/collections/4691709
dribbble.com/nbdie/collections/4691706
dribbble.com/uicle/collections/4691759
dribbble.com/uicle/collections/4691756
dribbble.com/uicle/collections/4691754
dribbble.com/uicle/collections/4691753
dribbble.com/uicle/collections/4691751
dribbble.com/uicle/collections/4691750
dribbble.com/uicle/collections/4691748
dribbble.com/uicle/collections/4691747
dribbble.com/uicle/collections/4691746
dribbble.com/uicle/collections/4691744
dribbble.com/aeuni/collections/4693270
dribbble.com/aeuni/collections/4693251
dribbble.com/aeuni/collections/4693237
dribbble.com/aeuni/collections/4693217
dribbble.com/aeuni/collections/4693202
dribbble.com/aeuni/collections/4693186
dribbble.com/aeuni/collections/4693167
dribbble.com/aeuni/collections/4693152
dribbble.com/aeuni/collections/4693133
dribbble.com/aeuni/collections/4693115
dribbble.com/nviya/collections/4693717
dribbble.com/nviya/collections/4693698
dribbble.com/nviya/collections/4693671
dribbble.com/nviya/collections/4693648
dribbble.com/nviya/collections/4693612
dribbble.com/nviya/collections/4693595
dribbble.com/nviya/collections/4693567
dribbble.com/nviya/collections/4693547
dribbble.com/nviya/collections/4693507
dribbble.com/nviya/collections/4693473
dribbble.com/caieyc/collections/4694187
dribbble.com/caieyc/collections/4694171
dribbble.com/caieyc/collections/4694151
dribbble.com/caieyc/collections/4694134
dribbble.com/caieyc/collections/4694113
dribbble.com/caieyc/collections/4694093
dribbble.com/caieyc/collections/4694067
dribbble.com/caieyc/collections/4694047
dribbble.com/caieyc/collections/4694024
dribbble.com/caieyc/collections/4693996
dribbble.com/caieyc/collections/4693980
dribbble.com/caieyc/collections/4693963
dribbble.com/liuec/collections/4694617
dribbble.com/liuec/collections/4694604
dribbble.com/liuec/collections/4694583
dribbble.com/liuec/collections/4694562
dribbble.com/liuec/collections/4694545
dribbble.com/liuec/collections/4694532
dribbble.com/liuec/collections/4694523
dribbble.com/liuec/collections/4694507
dribbble.com/liuec/collections/4694491
dribbble.com/liuec/collections/4694470
dribbble.com/liuec/collections/4694451
dribbble.com/liuec/collections/4694433
dribbble.com/liuec/collections/4694421
dribbble.com/nueabu/collections/4694993
dribbble.com/nueabu/collections/4694980
dribbble.com/nueabu/collections/4694963
dribbble.com/nueabu/collections/4694947
dribbble.com/nueabu/collections/4694934
dribbble.com/nueabu/collections/4694929
dribbble.com/nueabu/collections/4694922
dribbble.com/nueabu/collections/4694908
dribbble.com/nueabu/collections/4694896
dribbble.com/nueabu/collections/4694879
dribbble.com/nueabu/collections/4694863
dribbble.com/nueabu/collections/4694847
dribbble.com/nyuie/collections/4695326
dribbble.com/nyuie/collections/4695310
dribbble.com/nyuie/collections/4695296
dribbble.com/nyuie/collections/4695277
dribbble.com/nyuie/collections/4695262
dribbble.com/nyuie/collections/4695248
dribbble.com/nyuie/collections/4695234
dribbble.com/nyuie/collections/4695220
dribbble.com/nyuie/collections/4695207
dribbble.com/nyuie/collections/4695191
dribbble.com/nyuie/collections/4695176
dribbble.com/nyuie/collections/4695161
dribbble.com/neisda/collections/4645232
dribbble.com/neisda/collections/4645230
dribbble.com/neisda/collections/4645227
dribbble.com/neisda/collections/4645225
dribbble.com/neisda/collections/4645221
dribbble.com/neisda/collections/4645220
dribbble.com/neisda/collections/4645216
dribbble.com/neisda/collections/4645215
dribbble.com/neisda/collections/4645213
dribbble.com/dfafae/collections/4641926
dribbble.com/dfafae/collections/4641930
dribbble.com/dfafae/collections/4641932
dribbble.com/dfafae/collections/4641936
dribbble.com/dfafae/collections/4641939
dribbble.com/dfafae/collections/4641942
dribbble.com/dfafae/collections/4641945
dribbble.com/dfafae/collections/4641948
dribbble.com/dfafae/collections/4641949
dribbble.com/dfafae/collections/4641950
dribbble.com/kjsdfei/collections/4652056
dribbble.com/kjsdfei/collections/4652042
dribbble.com/kjsdfei/collections/4652025
dribbble.com/kjsdfei/collections/4652008
dribbble.com/kjsdfei/collections/4651994
dribbble.com/kjsdfei/collections/4651979
dribbble.com/kjsdfei/collections/4651962
dribbble.com/kjsdfei/collections/4651949
dribbble.com/kjsdfei/collections/4651940
dribbble.com/kjsdfei/collections/4651927
szxy.doodlekit.com/
hzdh.doodlekit.com/
shxyba.doodlekit.com/
cdxye.doodlekit.com/
dribbble.com/cauio/collections/4652271
dribbble.com/cauio/collections/4652266
dribbble.com/cauio/collections/4652262
dribbble.com/cauio/collections/4652254
dribbble.com/cauio/collections/4652248
dribbble.com/cauio/collections/4652238
dribbble.com/cauio/collections/4652232
dribbble.com/cauio/collections/4652227
dribbble.com/cauio/collections/4652220
dribbble.com/cauio/collections/4652208
dribbble.com/nainw/collections/4652443
dribbble.com/nainw/collections/4652440
dribbble.com/nainw/collections/4652434
dribbble.com/nainw/collections/4652426
dribbble.com/nainw/collections/4652420
dribbble.com/nainw/collections/4652415
dribbble.com/nainw/collections/4652405
dribbble.com/nainw/collections/4652399
dribbble.com/nainw/collections/4652393
dribbble.com/nainw/collections/4652384
dribbble.com/nainw/collections/4652379
dribbble.com/nainw/collections/4652372
