在数据表的设计中,对于树形的表格数据,处理起来时比较棘手的,个人经历过sql server,oracle,mysql,发现对于树形的表格数据处理都不同。
通常的属性表格设计时我们会这样设计:
create table demo_tree (
id int ,
nodename varchar(50),
parentid int
)
这样对于不同的数据库处理起来方法就各异了。在sql server里面可能需要用函数递归,在oracle里面可以使用 connect by .. start with .. order sibling by ..去达到深度遍历树的目的。
虽然主流的ORM框架能处理数据库厂商sql语法的差异,但是对于树形数据还是缺乏统一的支持。所以有人设计了一个这样的表结构:
1 create table DEMO_TREE
2 (
3 NODELEVEL INTEGER not null,
4 LEVELCODE VARCHAR2(500) not null,
5 PARENTNODE VARCHAR2(500),
6 NODENAME VARCHAR2(200)
7 /*其他字段略*/
8 )
- nodelevel是树的深度,1,2,3...
- levelcode这个字段格式是这样的,我们可以假定每级节点的数量是有上限的,可以根据需要约定比如我们限定每个节点的最多是99999个子节点。这样。levelcode 的第一个节点可以levelcode编号为"00001",其相邻节点为"00002",他的第一个子节点为"0000100001",以此类推,可以为每个节点一个唯一编号。
- parentnode就是父节点的levelcode
- nodename是节点名称
这样的设计后。我们给出一个实例查询:
select t.nodelevel,t.nodename,t.levelcode,t.parentnode From demo_tree t order by t.levelcode
这样查询的结果形如:
NODELEVEL |
NODENAME |
LEVELCODE |
PARENTNODE |
1 |
一级测试节点1 |
00001 |
2 |
二级测试节点1 |
0000100001 |
00001 |
3 |
sdfasfasfad |
000010000100001 |
0000100001 |
2 |
二级测试节点2 |
0000100002 |
00001 |
2 |
二级测试节点3 |
0000100003 |
00001 |
2 |
二级测试节点5 |
0000100005 |
00001 |
2 |
asdfgh |
0000100007 |
00001 |
1 |
一级测试节点2 |
00002 |
2 |
二级测试节点2 |
0000200001 |
00002 |
3 |
fasdfasfsaf |
000020000100001 |
0000200001 |
2 |
二级测试gg4 |
0000200002 |
00002 |
3 |
dfasfasfas |
000020000200001 |
0000200002 |
3 |
fgh |
000020000200001 |
0000200002 |
4 |
fdsafdas |
00002000020000100001 |
000020000200001 |
4 |
dfasfsafsda |
00002000020000100001 |
000020000200001 |
5 |
fadsfasfsa |
0000200002000010000100001 |
00002000020000100001 |
5 |
fdasfdasfasdf |
0000200002000010000100001 |
00002000020000100001 |
3 |
dsafasfasdf |
000020000300001 |
0000200003 |
1 |
测试深度节点1 |
10001 |
2 |
测试深度节点10 |
1000100000 |
10001 |
3 |
测试深度节点100 |
100010000000000 |
1000100000 |
4 |
测试深度节点1000 |
10001000000000000000 |
100010000000000 |
5 |
测试深度节点10000 |
1000100000000000000000000 |
10001000000000000000 |
1 |
测试深度节点2 |
10002 |
2 |
测试深度节点20 |
1000200000 |
10002 |
3 |
测试深度节点200 |
100020000000000 |
1000200000 |
4 |
测试深度节点2000 |
10002000000000000000 |
100020000000000 |
5 |
测试深度节点20000 |
1000200000000000000000000 |
10002000000000000000 |
6 |
qwerfga |
100020000000000000000000000001 |
1000200000000000000000000 |
2 |
sdfg |
1000200001 |
10002 |
3 |
safsdfsadfaaa |
100020000100001 |
1000200001 |
他是树的深度遍历结果,这也就是这样设计的最大的好处。
对于新增树节点时需要多做一步就是计算levelcode,比如增加同级节点时需要找到同级节点的最后一个节点。然后将levelcode最后一节+1。对于新增子节点需要找到最大levelcode的子节点然后+1。
删除也比较方便,如需要删除一个节点以及其所有的子节点,可以
delete from demo_tree where levelcode like '00001%'
需要获取树的广度遍历结果可以直接用nodelevel排序。
这样的设计带来的好处是在各种数据库上都可以用。不会因为数据库不同获取树的遍历结果需要写不同的sql。
当然,问题在于levelcode的计算会导致同级节点的排序不好实现。要获取遍历结果,通常是按照levelcode排序,由于计算levelcode是根据新增的先后顺序,所以同级排序就留给大家思考了。