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

test.sql:-- 创建测试数据 drop table if exists T1; CREATE TABLE T1 ( id bigint NOT NULL auto_increment, pid bigint,

test.sql:-- 创建测试数据
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);