mysql建立的一个自动更新组织树案案例

组织树实现案例

一、实现功能：
根据组织基表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 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 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;