組織樹實現案例

一、實現功能：
根據組織基表organizitions，更新組織樹子樹表orgs，子樹表包括每個組織樹的節點為頂點子樹，以及帶層級格式子樹。新增，刪除，修改組織樹基表organizitions，同時修改組織樹子樹表orgs。刪除organizitions時，如果該節點具有子樹節點，需要完成所有的子樹刪除后，才能刪除該節點。

組織樹舉例：

子樹排序順序：1，2，4，6，3，5，7 （窮盡每一條線在開始第二條線）

假設以上各節點為organization表的ID為例，則org表中數據為：
id 父樹 Id為頂點樹 id為頂點的帶層級格式子樹
(層級說明：頂點下一層為一級，以此類推)
1 1,2,4,6,3,5,7 1,-2,--4,--6,-3,--5,-7
2 1 2,4,6 2，-4,-6
3 1 3,5 3,-5
5 1,3
7 1

二、建表：
1、組織樹表（organizitions）
序號 字段名 類型 備注
1 Id int 主鍵自增字段
2 Name Varchar(64)
3 Seq int 同層級排列順序
4 adress Varchar(200)
5 Organization_id int

2、各節點子樹父樹序號表（orgs）
序號 字段名 類型 備注
1 Organization_id int 主鍵非自增
2 Parent_ids Varchar(100) 父樹
3 Child_ids Varchar(100) 子樹
4 Child_ids_format Varchar(100) 子樹節點帶-

建表腳本：
DROP TABLE IF EXISTS organizitions;
CREATE TABLE organizitions (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(64) DEFAULT NULL,
seq int(11) DEFAULT NULL,
address varchar(200) DEFAULT NULL,
organizition_id int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY organizition_id (organizition_id)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS orgs;
CREATE TABLE orgs (
organizition_id int(11) NOT NULL DEFAULT '0',
parent_ids varchar(100) DEFAULT NULL,
child_ids varchar(100) DEFAULT NULL,
child_ids_format varchar(100) DEFAULT NULL,
PRIMARY KEY (organizition_id),
KEY organizition_id (organizition_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三、實現子樹對應ORGS表中存儲舉例：
以下organizations表數據為例，假設組織樹節點為：
以如下organizations數據為例：（同層級樹排序按照seq順序排列）

(1)頂點1為案例的樹

頂點1不帶層級樹：

頂點1帶層級樹：

在orgs表中，對應為organizition_id為1的一行數據，字段child_ids,child_ids_format為帶層級格式和不帶層級格式數據。
如下圖：

(2)頂點3為案例的樹
頂點3不帶層級樹：

頂點3帶層級子樹：

對應ORGS表內容：

四、實現腳本
-- -------------------------------------------------------------------------------
-- 獲取指定ID為頂點的子樹
-- -------------------------------------------------------------------------------
drop function if exists getchild;
create function getchild(sid varchar(500))
returns varchar(500)
begin
declare org_id int;
declare lentree int ;
declare lentemptree int ;
declare lenlasttree int;
declare orgtree varchar(500);
declare cursortree varchar(500);
declare cid varchar(10);
declare ctree varchar(500);
declare foretree varchar(500);
declare lasttree varchar(500);
declare temp_tree varchar(500);
declare lenid int;
#select id into org_id from organizitions where organizition_id is null;
set org_id=cast(sid as SIGNED int);
set foretree='';
select GROUP_CONCAT(id order by seq) into ctree from organizitions where organizition_id=org_id ;
set lasttree=ctree;
#insert into test2(id,foretree,lasttree) values(org_id,foretree,lasttree);
select length(ctree) into lentree ;
if lentree >0 THEN
set cid=get_leftid(ctree);
set foretree=org_id;
select length(cid) into lenid;
set lasttree=get_lasttree(ctree,lenid);
set org_id=cast(cid as SIGNED int);
while org_id > 0 DO
select GROUP_CONCAT(id order by seq) into temp_tree from organizitions where organizition_id=org_id order by seq;
select length(temp_tree) into lentree;
if lentree>0 THEN
select length(lasttree) into lenlasttree;
if lenlasttree > 0 then
set lasttree=concat(temp_tree,',',lasttree);
else
set lasttree=temp_tree;
end if;
select length(temp_tree) into lentemptree;
END IF;
set temp_tree=lasttree;
#insert into test2(id,foretree,lasttree) values(org_id,foretree,lasttree);
set cid=get_leftid(temp_tree);
select length(cid) into lenid;
if lenid >0 THEN
set foretree=concat(foretree,',',org_id);
set lasttree=get_lasttree(temp_tree,lenid);
ELSE
set cid='0';
set orgtree=concat(foretree,',',org_id);
end if;
set org_id=cast(cid as SIGNED int);
end while;
else
set foretree=org_id;
end if;
return orgtree;
end;

---

-- 字符處理函數，返回字符串最后一個逗號后的字符串 '-1,---2,-3,-4,---5'返回到---5
-- -------------------------------------------------------------------------------
drop function if exists get_formatid;
create function get_formatid(sid varchar(20),stree varchar(100))
returns varchar(20)
-- returns int
begin
-- 將sid變為前后帶-的字符，2變為'-2-',stree中逗號替換為'-'，之后進行定位，獲取正確formatid為帶逗號最后一個字符串。
-- 例如'---2,---1,---5' 中，尋找'1'的帶'-'格式字符串'---'，處理后獲得字符串'---2,---1',在取最后一個逗號后的字符串，獲取正確結果
declare format_id varchar(20);
declare ctree varchar(500);
declare left_tree varchar(500);
declare loc int;
declare lenid int;
set sid=concat('-',sid,'-');
set ctree=concat(replace(stree,',','-'),'-');
set loc=locate(sid,ctree);
select length(sid) into lenid;
set left_tree=concat(',',left(stree,loc+lenid-2));
select REVERSE(left(REVERSE(left_tree),LOCATE(',',REVERSE(left_tree))-1)) into format_id;
return format_id;
end;

---

-- get_leftid，取第一個逗號左邊的整數。如果字符串為空，則返回空，如果沒有逗號，
-- select left('1,2,3',LOCATE(',','1,2,3')-1)
-- --------------------------------------------------------------------------------
drop function if exists get_leftid;
create function get_leftid(stree varchar(500))
-- 設置函數的返回類型
returns varchar(500)
begin-- 函數頭
declare len int;
set len=length(stree);
if len=0 then
set stree='';
ELSE
if locate(',',stree)>0 THEN
set stree=left(stree,locate(',',stree)-1);
end if;
end if;
return stree;
end;

---

-- 獲取組織樹第一個ID以外的下級樹，如果組織樹為 '1,2,3,4,5' ,第一個ID長度為1，則下級樹為'2，3，5，7，3’
-- 參數說明：參數個數：2 類型：字符型 整形 ，參數2代表第一個ID的長度
-- --------------------------------------------------------------------------------
drop function if exists get_lasttree;
create function get_lasttree(stree varchar(500),lenid int)
-- 設置函數的返回類型
returns varchar(500)
begin-- 函數頭
declare len int;
set len=length(stree);
if len<=lenid then
set stree='';
else
set stree=right(stree,len-lenid-1);
end if;
return stree;
end;
-- --------------------------------------------------------------------------------
-- 獲取組織樹帶斜杠的格式的子樹
-- --------------------------------------------------------------------------------
drop function if exists getchild_format;
create function getchild_format(sid varchar(20)) -- 設置函數的返回類型
returns varchar(500)
-- RETURNS int
begin
declare org_id int;
declare lentree int ;
declare lentemptree int ;
declare lenlasttree int;
declare orgtree varchar(500);
declare cursortree varchar(500);
declare cid varchar(10);
declare ctree varchar(500);
declare foretree varchar(500);
declare lasttree varchar(500);
declare temp_tree varchar(500);
declare lenid int;
declare format varchar(100);
declare format_xh varchar(100);
declare conn varchar(100);
declare sctree varchar(500);#用于存儲順序錯誤的所有帶有橫線標識的字符串
declare rep_format varchar(20);
declare temp_ctree varchar(100);
declare temp varchar(500);
declare format_childtree varchar(500);
declare format_id varchar(20);
set sctree='';
#設定節點橫線字符，初始值為-，子樹第一層加- ，第二層加--，依次類推
set format='-';
set org_id=cast(sid as SIGNED int);
set rep_format=concat(',',format);
set foretree='';
select GROUP_CONCAT(id order by seq) into ctree from organizitions where organizition_id=org_id ;
#為第一層子樹增加橫線比如'2,3,4'變為'-2,-3,-4'' 并講結果存儲sctree
#為首節點加橫線'2,3,4'變為'-2,3,4'
set temp_ctree=concat('-',ctree);
#為剩余加橫線'2,3,4'變為'-2,-3,-4'
select REPLACE(temp_ctree,',',rep_format) into temp;
set sctree=temp;
select length(ctree) into lentree ;
set lasttree=ctree;
if lentree >0 THEN
set cid=get_leftid(ctree);
set foretree=org_id;
select length(cid) into lenid;
set lasttree=get_lasttree(ctree,lenid);
set org_id=cast(cid as SIGNED int);
while org_id > 0 DO
#下一級增加一個'-'
select GROUP_CONCAT(id order by seq ) into temp_tree from organizitions where organizition_id=org_id;

select length(temp_tree) into lentree; if lentree>0 THEN#循環將橫線列入'-'set format=concat('-',format); #為首節點加橫線'5,6,7'變為'--5,6,7'set temp=concat(format,temp_tree);#為剩余節點增加橫線'--5,6,7'變為'--5,--6,--7'set rep_format=concat(',',format);select REPLACE(temp,',',rep_format) into temp;#將'-2,-3,-4'和'--5,--6,--7' 組合為'-2,-3,-4,--5,--6,--7' 循環結束sctree為順序不正確，但帶層級橫線的字符串set sctree=concat(sctree,',',temp);select length(lasttree) into lenlasttree;if lenlasttree > 0 thenset lasttree=concat(temp_tree,',',lasttree); elseset lasttree=temp_tree;end if;select length(temp_tree) into lentemptree;END IF; set temp_tree=lasttree; #insert into test2(id,foretree,lasttree) values(org_id,foretree,lasttree);set cid=get_leftid(temp_tree); select length(cid) into lenid; if lenid >0 THENset foretree=concat(foretree,',',org_id); set lasttree=get_lasttree(temp_tree,lenid);ELSEset cid='0'; set orgtree=concat(foretree,',',org_id); end if; set org_id=cast(cid as SIGNED int);end while;

else
set foretree=org_id;
end if;
-- return sctree;
-- return orgtree;
-- 替換orgtree中的ID為format_id，將sctree變為帶格式的子樹format_childtree
set lasttree=orgtree;
set org_id=get_leftid(lasttree);
set format_childtree=org_id;
select length(org_id) into lenid;
set lasttree=get_lasttree(lasttree,lenid);
select length(lasttree) into lentree;
while lentree > 0 DO
set org_id=get_leftid(lasttree);
if org_id > 0 THEN
select length(org_id) into lenid;
set format_id=get_formatid(org_id,sctree);
set lasttree=get_lasttree(lasttree,lenid);
set format_childtree=concat(format_childtree,',',get_formatid(org_id,sctree));
select length(lasttree) into lentree;
ELSE
set lentree=0;
end if;
end while ;
return format_childtree;
end;
-- ----------------------------------------------------------------------------------
-- 更新組織樹函數，執行一次存儲過程，則更新orgs表，更新所有的子樹和父數 調用方式：call proc_updatetree;
-- ----------------------------------------------------------------------------------
drop procedure IF EXISTS proc_updatetree;
DELIMITER $$
CREATE PROCEDURE proc_updatetree( )

BEGIN
declare stree varchar(500);
declare cid varchar(20);
declare lenid int;
declare org_id int;
declare top_id int;
declare father varchar(100);
declare child varchar(100);
declare child_format varchar(500);
delete from orgs;
set father='';
select GROUP_CONCAT(id) into stree from organizitions ;
select id into org_id from organizitions where organizition_id is null;
set top_id=org_id;
while org_id <> 0 do
set cid = get_leftid(stree);
set org_id=cast(cid as SIGNED int);
select length(cid) into lenid;
if lenid >0 then
set stree=get_lasttree(stree,lenid);
end if;
set child_format= getchild_format(org_id);
if org_id=top_id then
set father='';
else
set father=getfather(cid);
end if;
set child=getchild(cid);
insert into orgs(organizition_id,parent_ids,child_ids, child_ids_format) values(org_id,father,child,child_format);
set cid = get_leftid(stree);
select length(cid) into lenid;
if lenid > 0 then
set org_id=cast(cid as SIGNED int);
else
set org_id=0;
end if;
end while;
END;

---

-- 更新組織樹節點 的父樹
-- ----------------------------------------------------------------------------------
drop function if exists getfather;
create function getfather(sid varchar(500))
returns varchar(500)
begin
declare org_id int;
declare fid int;
declare len int;
declare foretree varchar(500);
set org_id=cast(sid as SIGNED int);
set foretree='';
while org_id > 0 do
select organizition_id into fid from organizitions where id=org_id ;
if fid >0 then
if foretree='' then
set foretree=fid;
ELSE
set foretree=concat(fid,',',foretree);
end if;
else
set fid=0;
end if;
set org_id=fid;
end while;
return foretree;
end;

---

-- 為orangization增加觸發器，在修改、新增、刪除時，調用存儲過程proc_updatetree,實現
-- 子樹更新，刪除觸發器增加子樹檢查，如果該節點存在子樹，則拋出信息存在子樹，刪除失敗。
-- --------------------------------------------------------------------------------
DROP TRIGGER IF EXISTS insertorg;
CREATE TRIGGER insertorg AFTER INSERT ON organizitions FOR EACH ROW BEGIN
call proc_updatetree();
END;
DROP TRIGGER IF EXISTS updateorg;
CREATE TRIGGER updateorg AFTER UPDATE ON organizitions FOR EACH ROW BEGIN
call proc_updatetree();
END;

DROP TRIGGER IF EXISTS deleteorg;
CREATE TRIGGER deleteorg AFTER DELETE ON organizitions FOR EACH ROW BEGIN
If getchild(old.id) is null then
call proc_updatetree();
ELSE
SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = 'Have child_tree,fail delete!';
end if;
END;

轉載于:https://www.cnblogs.com/druck/p/11224829.html

總結

以上是生活随笔為你收集整理的mysql建立的一个自动更新组织树案案例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。