CREATE DEFINER=`root`@`%` FUNCTION `findParent`(`subId` varchar(64)) RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp = '';
SET sTempPar =subId;
#循环递归
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 t_fs_folder where parent_id<>id and FIND_IN_SET(id,sTempPar)>0
;
END WHILE;
RETURN sTemp;
END
#使用方式
select tt.id,tt.`Type`,findParent(tt.parent_id) as ParentIds from
(
select g.id,'FromfolderStart' as `Type`,g.parent_id as parent_id from nerko.t_fs_folder g where g.title like CONCAT('%', 'b', '%')
union all
select h.id,'FromEntityStart' as `Type`,h.folder_id as parent_id from nerko.t_fs_entity h where h.title like CONCAT('%', 'b', '%')
) as tt
CREATE DEFINER=`root`@`%` PROCEDURE `search_folder_entity`(IN `title_key` varchar(36))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE ptemp text;
DECLARE ctemp VARCHAR(1000);
DECLARE rs_cursor CURSOR FOR select r.id from (
select g.id from nerko.t_fs_folder g where g.title like CONCAT('%', title_key, '%') OR g.id = title_key
union all
select h.id from nerko.t_fs_entity h where h.title like CONCAT('%', title_key, '%') OR h.id = title_key
) r;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
SET ptemp = '#';
SET ctemp = '';
DROP TABLE IF EXISTS temp_result;
CREATE TEMPORARY TABLE `temp_result` (
`ids` varchar(1000) not null
) ENGINE=MEMORY DEFAULT CHARSET='utf8';
OPEN rs_cursor;
read_loop: LOOP
FETCH rs_cursor into ctemp;
WHILE ctemp IS NOT NULL DO
SET ptemp = concat(ptemp, ',', ctemp);
SELECT group_concat(c.parent_id)
INTO ctemp
FROM (
select a.id as id, a.parent_id from nerko.t_fs_folder a
union all
select b.id as id, b.folder_id as parent_id from nerko.t_fs_entity b
) c
WHERE FIND_IN_SET(c.id, ctemp) ;
END WHILE;
IF ptemp != '#' AND ptemp != '#,' THEN
INSERT INTO temp_result select substr(ptemp, 3) from dual;
END IF;
SET ptemp = '#';
IF done THEN
LEAVE read_loop;
END IF;
END LOOP read_loop;
CLOSE rs_cursor;
select * from temp_result;
END
#使用方式
call search_folder_entity('4c74be60-3806-11ea-a5f9-99fa1d1b4104')