·方法 I
path数据库结构: (领接表增强,比较简单)
id | name | pid | path | level(非必要) | order(非必要) |
1 | 总裁办 | 0 | /0/1/ | 1 | |
3 | 人事部 | 1 | /0/1/3/ | 2 | |
8 | 行政部 | 1 | /0/1/8/ | 2 | |
10 | 招聘一部 | 3 | /0/1/3/10/ | 3 | |
11 | 招聘二部 | 3 | /0/1/3/11/ | 3 | |
12 | 招聘专员 | 11 | /0/1/3/11/12/ | 4 | |
2 | 财务部 | 0 | /0/2/ | 1 | |
15 | 出纳 | 2 | /0/2/15/ | 2 | |
16 | 会计 | 2 | /0/2/16/ | 2 | |
9 | 技术部 | 0 | /0/9/ | 1 |
缺点:
- 树的级别会受「path」字段长度的限制,但是应付一般tree足矣,如果「path」为「varchar(255)」,20级树完全没有问题;
- 修改某节点父级时,其下属「path」、「level」均需要重新改变;
- 查询子孙集时,通过「LIKE %/{$id}/%」查询比较低效,但是相对于递归查询来说,效率已经算是不错了。
树结构:
查询语句:
说明 | SQL语句 |
查询子集 | SELECT * FROM `table` WHERE `pid` = {$id}; ($id = 3:人事部,下同) |
结果:招聘一部、招聘二部 | |
查询子孙集 | SELECT * FROM `table` WHERE `path` LIKE '%/{$id}/%'; |
结果:人事部、招聘一部、招聘二部、招聘专员 | |
查询某代子集 | SELECT * FROM `table` WHERE `path` LIKE '%/{$id}/%' AND `level` = 4; |
结果:招聘专员 | |
插入 | $pid = 10; (10:招聘一部) $insert_id = INSERT INTO `table` (`name`,`pid`) VALUES('面试专员','{$pid}'); $parent = SELECT * FROM `table` WHERE `id` = {$pid}; 如果$pid == 0 则需要手动设定$parent = array('path' => '/0/','level' => 1); UPDATE `table` SET `path` = '{$parent['path']}{$insert_id}/',`level`= {$parent['level']} + 1 WHERE `id` = {$insert_id}; |
结果:在「10招聘一部」下面插入了一项:「面试专员」 | |
修改父级 | $src_parent = SELECT * FROM `table` WHERE `id` = {$src_pid}; $new_parent = SELECT * FROM `table` WHERE `id` = {$new_pid}; 如果$src_pid/new_pid == 0 则需要手动设定$src_parent/$new_parent = array('path' => '/0/','level' => 1); $level_delta = $src_parent['level'] - $new_parent['level']; 修改当前节点:UPDATE `table` SET `pid` = {$new_pid} WHERE `id` = {$id}; 修改自己和所有子孙的「path」: UPDATE `table` SET `path` = REPLACE(`path`,{$src_parent['path']},{$new_parent['path']}),`level`= `level` - {$level_delta} WHERE `path` LIKE '%/{$id}/%' |
结果:将「$id」节点的父级修改为「$new_pid」,并修改其以及其下属的「path」和「level」 | |
删除 | DELETE FROM `table` WHERE `path` LIKE '%/{$id}/%'; 此方法会删除掉此节点下面所有节点,如果需要保留下属节点,那需要先修改其子集的父级(见上) |
结果:将「$id」以及其下属全部删除 | |
查找path | SELECT `path` FROM `table` WHERE `id` = {$id}; |
结果:/0/1/3/ | |
前台显示 | $tree = SELECT `id`,`name`,`pid` FROM `table`; $.fn.zTree.init($("#treeDemo"),{data: {simpleData: {enable: true,pIdKey: "pid"}}},json_encode($tree)); |
·方法 II
预排序遍历树结构:(MPTT,Modified Preorder Tree Traversal)
id | name | pid | left | right | level(非必要) | order(非必要) |
1 | Software | 0 | 1 | 18 | 1 | |
2 | Database | 1 | 2 | 11 | 2 | |
3 | MySQL | 2 | 3 | 6 | 3 | |
4 | MySQL 5.0 | 3 | 4 | 5 | 4 | |
5 | Oracle | 2 | 7 | 10 | 3 | |
6 | Oracle 9i | 5 | 8 | 9 | 4 | |
7 | Language | 1 | 12 | 17 | 2 | |
8 | PHP | 7 | 13 | 14 | 3 | |
9 | Java | 15 | 13 | 16 | 3 |
缺点:对于新手来说,此种结构需要时间来消化。相对上面结构来说,全部采用数字来操作,树则会无限等级。但是修改或添加时,修改列会比较多,可能会造成锁表。
堆栈结构:
![](https://i-blog.csdnimg.cn/blog_migrate/6b251e405635e0f5f4fd2e6291600515.gif)
查询语句:
说明 | SQL语句 |
查询子集 | SELECT * FROM `table` WHERE `pid` = {$id} ($id = 2:Database,下同) |
结果:MySQL、Oracle | |
查询子孙集 | $pos = SELECT `left`,`right` FROM `table` WHERE `id` = $id; $pos得到的值为 2、11 SELECT * FROM `table` WHERE `left` BETWEEN 2 AND 11; |
结果:Database、MySQL、MySQL 5.0、Oracle、Oracle 9i | |
查询某代子集 | $pos = SELECT `left`,`right` FROM `table` WHERE `id` = $id; SELECT * FROM `table` WHERE `left` BETWEEN {$pos['left']} AND {$pos['right']} AND `level` >= 4 |
结果:MySQL 5.0、Oracle 9i | |
插入 | $pid = 2; (2:Datebase) $parent = SELECT * FROM `table` WHERE `id` = {$pid} $left = $parent['right']; 如果「$pid == 0」则 $left = (SELECT MAX(`right`) WHERE `pid` = 0) + 1; UPDATE `table` SET `right` = `right` + 2 WHERE `right` >= {$left}; UPDATE `table` SET `left` = `left` + 2 WHERE `left` > {$left}; (腾出插入空间) INSERT INTO (`name`,`pid`,`left`,`right`,`level`) VALUES('MS SQL Server','{$pid}','{$left}','{$left} + 1','{$parent['level']} + 1'); |
结果:在「2:Datebase」下面插入了一项:「MS SQL Server」,插入了同级别堆栈之后。 | |
修改父级 | 由于代码较多,详见mptt_bd.class.zip中的描述 |
结果:将$id的项目的父级修改为$pid | |
删除 | $node = SELECT `left`,`right` FROM `table` WHERE `id` = `{$id}`; $delta = $node['right'] - $node['left'] + 1;
DELETE FROM `table` WHERE `left` BETWEEN {$node['left']} AND {$node['right']} UPDATE `table` SET `left` = `left` - {$delta} WHERE `left` > {$node['right']} AND `right` > {$node['right']}; UPDATE `table` SET `right` = `right` - {$delta} WHERE `right` > {$node['right']}; 此方法会删除掉此节点下面所有节点,如果需要保留下属节点,那需要先修改其子集的父级(见上) |
结果:删除「$id」以及所有下属节点 | |
显示path | $pos = SELECT `left`,`right` FROM `table` WHERE `id` = 6; (6: Oracle 9i) SELECT `id`,`name` FROM `table` WHERE `left` <= {$pos['left']} AND `right` >= $row['right'] ORDER BY `left` ASC |
结果:/Software/Database/Oracle/Oracle 9i/、 /1/2/5/6/ | |
前台显示 | $tree = SELECT `id`,`name`,`pid` FROM `table` $.fn.zTree.init($("#treeDemo"),{data: {simpleData: {enable: true,pIdKey: "pid"}}},json_encode($tree)); |
MPTT Class详细算法请见:
http://www.codingforums.com/showthread.php?t=79773
PHP Class下载:
mptt_bd.class.zip