1.表结构
CREATE TABLE folder(
id BIGINT(20) NOT NULL,
parent_id BIGINT(20) DEFAULT NULL,
PRIMARY KEY id
);
2.根据传入id查询所有父节点及其的id
创建函数:
CREATE DEFINER=`root`@`%` FUNCTION `getParList`(rootId BIGINT) RETURNS varchar(1000) CHARSET utf8
READS SQL DATA
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp = '';
SET sTempPar =rootId;
#循环递归
WHILE sTempPar is not null DO
#判断是否是第一个,不加的话第一个会为空
IF sTemp != '' THEN
SET sTemp = concat(sTemp,',',sTempPar);
ELSE
SET sTemp = sTempPar;
END IF;
SET sTemp = concat(sTemp,',',sTempPar);
SELECT group_concat(parent_id) INTO sTempPar FROM folder where parent_id<>id and FIND_IN_SET(id,sTempPar)>0;
END WHILE;
RETURN sTemp;
END
调用:
select id
from folder
where FIND_IN_SET(id,getChildList(2))
3.根据传入id查询所有子节点及其的id
创建函数:
CREATE DEFINER=`root`@`%` FUNCTION `getChildList`(rootId BIGINT) RETURNS varchar(1000) CHARSET utf8
READS SQL DATA
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =cast(rootId as CHAR);
WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM folder where FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END
调用:
select id
from folder
where FIND_IN_SET(id,getParList(10))