mysql搜索树节点

195 阅读1分钟
  • 函数方式实现
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')