树状结构数据存储和检索的两种方案

CK
717 阅读2分钟

1. 继承关系驱动的Schema设计

基础数据

image.png

SQL生成脚本

 CREATE TABLE `item_for_recursion` (
`node_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
PRIMARY KEY (`node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO item_for_recursion (name,parent_id) VALUES ('Food',null);
INSERT INTO item_for_recursion (name,parent_id) VALUES ('Fruit',1);
INSERT INTO item_for_recursion (name,parent_id) VALUES ('Red',2);
INSERT INTO item_for_recursion (name,parent_id) VALUES ('Cherry',3);
INSERT INTO item_for_recursion (name,parent_id) VALUES ('Yellow',2);
INSERT INTO item_for_recursion (name,parent_id) VALUES ('Banana',5);
INSERT INTO item_for_recursion (name,parent_id) VALUES ('Meat',1);
INSERT INTO item_for_recursion (name,parent_id) VALUES ('Beef',7);
INSERT INTO item_for_recursion (name,parent_id) VALUES ('Pork',7);

优点

设计和实现自然而然，非常直观和方便。添加更新和删除节点操作方便

缺点

通过递归操作进行检索，不断访问数据库导致性能较低

2. 左右值编码的Schema设计

基础数据

image.png

image.png

SQL生成脚本

 CREATE TABLE `item_for_left_right` (
`node_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`lft` int(11) not null,
`rgt` int(11) not null,
PRIMARY KEY (`node_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO item_for_left_right (name,lft,rgt) VALUES ('Food',1,18);
INSERT INTO item_for_left_right (name,lft,rgt) VALUES ('Fruit',2,11);
INSERT INTO item_for_left_right (name,lft,rgt) VALUES ('Red',3,6);
INSERT INTO item_for_left_right (name,lft,rgt) VALUES ('Cherry',4,5);
INSERT INTO item_for_left_right (name,lft,rgt) VALUES ('Yellow',7,10);
INSERT INTO item_for_left_right (name,lft,rgt) VALUES ('Banana',8,9);
INSERT INTO item_for_left_right (name,lft,rgt) VALUES ('Meat',12,17);
INSERT INTO item_for_left_right (name,lft,rgt) VALUES ('Beef',13,14);
INSERT INTO item_for_left_right (name,lft,rgt) VALUES ('Pork',15,16);

检索方案

  • 获取某节点的所有子孙节点数量，公式：(右值-左值-1)/2

    • select (rgt-lft-1)/2 from item_for_left_right where node_id=?

  • 获取某节点下的所有子孙节点，公式：大于节点的左值，且小于节点的右值的所有节点

    • select r.* from item_for_left_right r ,(select * from item_for_left_right where node_id=?) t where r.lft>t.lft and r.rgt<t.rgt order by r.lft;

  • 获取某节点所在的层数，公式：小于等于节点的左值，且大于等于节点的右值的节点数量

    • select count(0) from item_for_left_right r, (select * from item_for_left_right where node_id=?) t where r.lft<=t.lft and r.rgt>=t.lft

  • 获取某节点所有的祖父节点，公式：小于节点的左值，且大于节点的右值的所有节点

    • select r.* from item_for_left_right r, (select * from item_for_left_right where node_id=?) t where r.lft<t.lft and r.rgt>t.lft
  • 创建获取某节点所在的层数的函数
CREATE FUNCTION `CountLayer`(p_node_id int) RETURNS int(11)
    DETERMINISTIC
BEGIN
  declare p_result,p_lft,p_rgt int default 0;
	if exists (select 1 from item_for_left_right where node_id=p_node_id) then
		begin
			select lft, rgt into p_lft, p_rgt from item_for_left_right where node_id=p_node_id;
			select count(*) into p_result from item_for_left_right where lft <= p_lft and rgt >= p_rgt;
		end;
		return p_result;
	end if;
RETURN 0;
END
  • 创建包含节点所在层数的视图
CREATE VIEW tree_view AS
SELECT
	`item_for_left_right`.`node_id` AS `node_id`,
	`item_for_left_right`.`name` AS `NAME`,
	`item_for_left_right`.`lft` AS `lft`,
	`item_for_left_right`.`rgt` AS `rgt`,
	`CountLayer` ( `item_for_left_right`.`node_id` ) AS `layer` 
FROM
	`item_for_left_right` 
ORDER BY
	`item_for_left_right`.`lft`

  • 获取某节点直属父节点

    • select tv.* from tree_view tv, (select * from tree_view where node_id=?) t where tv.lft<t.lft and tv.rgt>t.rgt and tv.layer=t.layer-1

  • 获取某节点所有的子节点

    • select tv.* from tree_view tv, (select * from tree_view where node_id=?) t where tv.lft>t.lft and tv.rgt<t.rgt and tv.layer=t.layer+1

  • 获取所有叶子节点，公式：右值=左值+1

    • SELECT * FROM item_for_left_right WHERE rgt = lft+1

  • 获取某节点所有同父的兄弟节点，公式：与某节点同一层级，且为该节点左值-1或右值+1（即表示相邻）

    • SELECT tv.* FROM tree_view tv, (select * from tree_view where node_id=?) t where tv.layer = t.layer and (tv.rgt=t.lft-1 or tv.lft=t.rgt+1)

添加或删除节点方案

  • 创建存储过程，在某节点下添加子节点
CREATE PROCEDURE `AddSubNode`(IN p_node_id int, IN node_name varchar(50))
BEGIN
declare p_rgt int default 0;
if exists(select node_id from item_for_left_right where node_id = p_node_id)  then
	begin
		START TRANSACTION;
			select rgt into p_rgt from item_for_left_right where node_id =  p_node_id;
			update item_for_left_right set rgt = rgt + 2 where rgt >= p_rgt;
			update item_for_left_right set lft = lft + 2 where lft >= p_rgt;
			insert into item_for_left_right(name, lft, rgt) values(node_name, p_rgt, p_rgt + 1);
		COMMIT;
	end;
end if;
END
  • 创建存储过程，删除某节点（包含其子孙节点）
    • 删除节点的数量 = (被删除节点的右值-被删除节点的左值+1)/2
CREATE PROCEDURE `DelNode`(IN del_node_id int)
BEGIN
  declare p_lft,p_rgt int  default 0;
	if exists(select node_id from item_for_left_right where node_id = del_node_id)  then
		START TRANSACTION;
			select lft, rgt into p_lft, p_rgt from item_for_left_right where node_id=del_node_id;
			delete from item_for_left_right where lft>=p_lft and rgt<=p_rgt;
			update item_for_left_right set lft=lft-(p_rgt - p_lft + 1) where lft > p_lft;
			update item_for_left_right set rgt=rgt-(p_rgt - p_lft + 1) where rgt > p_rgt;
		COMMIT;
	end if;
END

优点

消除递归操作，查询条件是基于整形数字的比较，效率很高

缺点

节点的添加、删除及修改代价较大，将会涉及到表中多方面数据的改动

avatar
文章
阅读
粉丝