CREATEFUNCTION `CountLayer`(`_node_id` int) RETURNSint(11) BEGIN DECLARE _result INT; DECLARE _lft INT; DECLARE _rgt INT; IF EXISTS(SELECT Node_id FROM Tree WHERE Node_id = _node_id) THEN SELECT Lft,Rgt FROM Tree WHERE Node_id = _node_id INTO _lft,_rgt; SET _result = (SELECTCOUNT(1) FROM Tree WHERE Lft <= _lft AND Rgt >= _rgt); RETURN _result; ELSE RETURN0; END IF; END;
在添加完函数以后,我们创建一个a视图,添加新的层次列:
1 2
CREATEVIEW `NewView`AS SELECT Node_id, Name, Lft, Rgt, CountLayer(Node_id) AS Layer FROM Tree ORDERBY Lft ;
5、 获取当前节点父节点,以Fruit为例:
1
SELECT*FROM treeview WHERE Lft <=2AND Rgt >=11AND Layer=1
6、 获取所有直属子节点,以Fruit为例:
1
SELECT*FROM treeview WHERE Lft BETWEEN2AND11AND Layer=3
7、 获取所有兄弟节点,以Fruit为例:
1
SELECT*FROM treeview WHERE Rgt >11AND Rgt < (SELECT Rgt FROM treeview WHERE Lft <=2AND Rgt >=11AND Layer=1) AND Layer=2
8、 返回所有叶子节点
1
SELECT*FROM Tree WHERE Rgt = Lft +1
如何创建树?如何新增数据?
上面已经介绍了如何检索结果,那么如何才能增加新的节点呢?Nested set 最重要是一定要有一个根节点作为所有节点的起点,而且通常这个节点是不被使用的。为了便于控制查询级别,在建表的时候建议添加parent_id配合之联结列表方式一起使用。
LOCK TABLE Tree WRITE; SELECT@parent_id := node_id, @myLeft := lft FROM Tree WHERE name ='Food'; UPDATE Tree SET rgt = rgt +2WHERE rgt >@myLeft; UPDATE Tree SET lft = lft +2WHERE lft >@myLeft; INSERTINTO Tree(parent_id, name, lft, rgt) VALUES(@parent_id, 'Fruit', @myLeft+1, @myLeft+2); UNLOCK TABLES;
如需在末尾追加就需要以下方式进行(以在Red下添加Apple为例):
1 2 3 4 5 6
LOCK TABLE Tree WRITE; SELECT@parent_id := node_id , @myRight := rgt FROM Tree WHERE name ='Red'; UPDATE Tree SET rgt = rgt +2WHERE rgt >=@myRight; UPDATE Tree SET lft = lft +2WHERE lft >@myRight; INSERTINTO Tree(parent_id, name, lft, rgt) VALUES(@parent_id, 'Apple', @myRight, @myRight+1); UNLOCK TABLES;
在节点A后面添加同级节点(以在Yellow后面添加Green为例)
1 2 3 4 5 6
LOCK TABLE Tree WRITE; SELECT@parent_id := parent_id , @myRight := rgt FROM Tree WHERE name ='Yellow'; UPDATE Tree SET rgt = rgt +2WHERE rgt >@myRight; UPDATE Tree SET lft = lft +2WHERE lft >@myRight; INSERTINTO Tree(parent_id, name, lft, rgt) VALUES(@parent_id, 'Green', @myRight+1, @myRight+2); UNLOCK TABLES;
LOCK TABLE Tree WRITE; SELECT@nodeId := node_id , @myLeft := lft , @myRight := rgt FROM Tree WHERE name ='Apple'; UPDATE Tree SET lft = lft - (@myRight-@myLeft) -1WHERE lft >@myRight; UPDATE Tree SET rgt = rgt - (@myRight-@myLeft) -1WHERE rgt >@myRight; SELECT@parent_id := node_id , @Left := lft , @Right := rgt FROM Tree WHERE name ='Yellow'; UPDATE Tree SET lft = lft + (@myRight-@myLeft) +1WHERE lft >@Left; UPDATE Tree SET rgt = rgt + (@myRight-@myLeft) +1WHERE lft >@Left; UPDATE Tree SET parent_id =@parent_id WHERE name = node_id =@nodeId; UPDATE Tree SET lft =@Left+ lft -@myLeft+1, rgt =@Left+ lft -@myLeft+1+ (@myRight-@myLeft) WHERE lft >=@myLeftAND rgt <=@myRight; UNLOCK TABLES;
删除节点(包含子节点)
1 2 3 4 5 6
LOCK TABLE Tree WRITE; SELECT@myLeft := lft , @myRight := rgt FROM Tree WHERE name ='Apple'; DELETE Tree WHERE lft >=@myLeftAND rgt <=@myRight; UPDATE Tree SET lft = lft - (@myRight-@myLeft) -1WHERE lft >@myRight; UPDATE Tree SET rgt = rgt - (@myRight-@myLeft) -1WHERE rgt >@myRight; UNLOCK TABLES;
如果需要只删除该节点,子节点自动上移一级如何处理?
1 2 3 4 5 6 7
LOCK TABLE Tree WRITE; SELECT@parent_id := parent_id , @node_id :=node_id , @myLeft := lft , @myRight := rgt FROM Tree WHERE name ='Red'; UPDATE Tree SET parent_id =@parent_id WHERE parent_id =@node_id DELETE Tree WHERE lft =@myLeft; UPDATE Tree SET lft = lft -1,rgt = rgt-1Where lft >@myLeftAND@rgt<@myRight UPDATE Tree SET lft = lft -2,rgt = rgt-2Where lft >@rgt>@myRight UNLOCK TABLES;