CREATE DEFINER = `root`@`localhost` PROCEDURE `AddNode`(`_parent_name` varchar(255),`_node_name` varchar(255)) BEGIN DECLARE _ancestor INT; DECLARE _descendant INT; DECLARE _parent INT; IF NOTEXISTS(SELECT node_id From nodeinfo WHERE node_name = _node_name) THEN INSERTINTO nodeinfo (node_name) VALUES(_node_name); SET _descendant = (SELECT node_id FROM nodeinfo WHERE node_name = _node_name); INSERTINTO noderelationship (ancestor,descendant,distance) VALUES(_descendant,_descendant,0); IF EXISTS (SELECT node_id FROM nodeinfo WHERE node_name = _parent_name) THEN SET _parent = (SELECT node_id FROM nodeinfo WHERE node_name = _parent_name); INSERTINTO noderelationship (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1from noderelationship where descendant = _parent; END IF; END IF; END;
完成后2张表的数据大致是这样的:(注意:每个节点都有一条到其本身的记录。)
查询Fruit下所有的子节点:
1 2 3 4 5 6 7 8 9
SELECT n3.node_name FROM nodeinfo n1 INNERJOIN noderelationship n2 ON n1.node_id = n2.ancestor INNERJOIN nodeinfo n3 ON n2.descendant = n3.node_id WHERE n1.node_name ='Fruit' AND n2.distance !=0
查询Fruit下直属子节点
1 2 3 4 5 6 7 8 9
SELECT n3.node_name FROM nodeinfo n1 INNERJOIN noderelationship n2 ON n1.node_id = n2.ancestor INNERJOIN nodeinfo n3 ON n2.descendant = n3.node_id WHERE n1.node_name ='Fruit' AND n2.distance =1
查询Fruit所处的层级:
1 2 3 4 5 6 7 8 9 10
SELECT n2.*, n3.node_name FROM nodeinfo n1 INNERJOIN noderelationship n2 ON n1.node_id = n2.descendant INNERJOIN nodeinfo n3 ON n2.ancestor = n3.node_id WHERE n1.node_name ='Fruit' ORDERBY n2.distance DESC