mysql查询祖类_一个递归获取祖先元素值的MySQL函数范例

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);