一个递归获取祖先元素值的MySQL函数范例

一个递归获取祖先元素值的MySQL函数范例

-- 创建测试数据
 drop table if exists T1;
 CREATE TABLE T1 (
   id bigint NOT NULL auto_increment,
   pid bigint,
   code varchar(255),
   PRIMARY KEY (id)
 );
 insert into t1(id,pid,code) values(1,null,'1');
 insert into t1(id,pid,code) values(2,null,'2');
 insert into t1(id,pid,code) values(3,1,'1.1');
 insert into t1(id,pid,code) values(4,1,'1.2');
 insert into t1(id,pid,code) values(5,2,'2.1');
 insert into t1(id,pid,code) values(6,3,'1.1.1');
 select * from t1 order by code;
 
 -- 定义递归处理函数:获取祖先的id和code，并用符号'/'按序连接，id和code间用';'连接
 DELIMITER $$ 
     DROP FUNCTION IF EXISTS getAncestors $$
     CREATE FUNCTION getAncestors(id bigint) RETURNS VARCHAR(1000)
     BEGIN
             DECLARE done INT DEFAULT 0;
             DECLARE r VARCHAR(1000);
             DECLARE ri VARCHAR(1000);
             DECLARE rc VARCHAR(1000);
             DECLARE lev int;
             DECLARE cid bigint;
             DECLARE pid bigint;
             DECLARE pcode VARCHAR(255);
             DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
             SET cid = id;
             SET lev = 0;
             SET ri = '';
             SET rc = '';
             REPEAT
                 SELECT p.id,p.code into pid,pcode FROM T1 c inner join  T1 p on p.id=c.pid where c.id=cid;
                 IF NOT done THEN
                     SET cid = pid;
                     if length(ri) > 0 then
                         SET ri = concat(cast(pid as char),'/',ri);
                         SET rc = concat(cast(pid as char),'/',rc);
                     else
                         SET ri= cast(pid as char);
                         SET rc= pcode;
                     end if;
                 END IF;
             UNTIL done END REPEAT;
             if length(ri) > 0 then
                 SET r = concat(ri,';',rc);
             else
                 SET r = null;
             end if;
            RETURN r;
     END $$
 DELIMITER ;
 -- 返回：null;
 select getAncestors(1);
 -- 返回：'1;1';
 select getAncestors(3);
 -- 返回：'1/3;1/1.1';
 select getAncestors(6);