mysql建立数据库并自动更新_mysql建立的一个自动更新组织树案案例

组织树实现案例

一、实现功能：

根据组织基表organizitions，更新组织树子树表orgs，子树表包括每个组织树的节点为顶点子树，以及带层级格式子树。新增，删除，修改组织树基表organizitions，同时修改组织树子树表orgs。删除organizitions时，如果该节点具有子树节点，需要完成所有的子树删除后，才能删除该节点。

组织树举例：

子树排序顺序：1，2，4，6，3，5，7 (穷尽每一条线在开始第二条线)

假设以上各节点为organization表的ID为例，则org表中数据为：

id父树Id为顶点树id为顶点的带层级格式子树

(层级说明：顶点下一层为一级，以此类推)

11,2,4,6,3,5,71,-2,--4,--6,-3,--5,-7

212,4,62，-4,-6

313,53,-5

51,3

71

二、建表：

1、组织树表(organizitions)

序号字段名类型备注

1Idint主键自增字段

2NameVarchar(64)

3Seqint同层级排列顺序

4adressVarchar(200)

5Organization_idint

2、各节点子树父树序号表(orgs)

序号字段名类型备注

1Organization_idint主键非自增

2Parent_idsVarchar(100)父树

3Child_idsVarchar(100)子树

4Child_ids_formatVarchar(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;