【Mysql&应用场景】Mysql实现树形数据查询(存储过程)

1、实现原理

测试的数据量是1740条数据，根据子节点一层一层的查询父节点直到查询到最高层，然后将所有层次的节点汇总，根据汇总的节点查询数据

2、获取所有的子节点

sql

DELIMITER //
DROP PROCEDURE IF EXISTS `getChild`;
CREATE PROCEDURE `getChild`(rootId varchar(36))
  BEGIN
		DECLARE ptemp text;#由于是批量导入的数据，不是用id做标识，使用varchar字符串撑爆了
    DECLARE ctemp VARCHAR(1000);
    SET ctemp = rootId;
		set ptemp = '#';
    WHILE ctemp is not null DO
			SET ptemp = concat(ptemp, ',', ctemp);
      SELECT group_concat(child_node)
      INTO ctemp
      FROM graph_original_data 
      WHERE relation <> '下游' and FIND_IN_SET(parent_node, ctemp);
    END WHILE;
		select 
		parent_node,relation,child_node
		from graph_original_data where relation <> '下游' and find_in_set(parent_node,ptemp);
  END//
DELIMITER ;
call getChild('农林牧渔行业')

结果

3、获取所有的父节点

sql

DELIMITER //
DROP PROCEDURE IF EXISTS `getParent`;
CREATE PROCEDURE `getParent`(rootId varchar(36))
 BEGIN
    DECLARE ptemp text;#使用varchar撑爆了
    DECLARE ctemp VARCHAR(1000);
    SET ptemp = '#';
    SET ctemp = rootId;
    WHILE ctemp IS NOT NULL DO
      SET ptemp = concat(ptemp, ',', ctemp);
      SELECT group_concat(parent_node)
      INTO ctemp
      FROM graph_original_data 
      WHERE relation <> '下游' and FIND_IN_SET(child_node, ctemp) ;
    END WHILE;
        select 
        parent_node,relation,child_node 
        from graph_original_data where find_in_set(child_node,ptemp);
  END//
DELIMITER ;
call getParent('丁香')

结果

4、查询含有多个最高级父节点的节点

sql

DELIMITER //
DROP PROCEDURE IF EXISTS `findHasTwoParentest`;
CREATE PROCEDURE `findHasTwoParentest`()
  BEGIN
		DECLARE ptemp text;
		DECLARE ctemp VARCHAR(1000);
		DECLARE childNode VARCHAR(50);
		DECLARE nodata INT DEFAULT 0;#注意：这个变量声明必须放在游标声明前面
		declare Curb cursor for select child_node from graph_original_data group by child_node order by null;
		DECLARE EXIT HANDLER FOR NOT FOUND select parent_node,child_node from graph_temporary_table group by parent_node,child_node HAVING count(parent_node) >= 2; #异常返回数据
		DROP table IF EXISTS `graph_temporary_table`;
		create temporary table graph_temporary_table(
		parent_node varchar(50), 
		child_node VARCHAR(50)
		);
		open Curb;
				fetch next from Curb into childNode;
				WHILE nodata = 0 DO#判断是不是到了最后一条数据
					SET ctemp = childNode;
					SET ptemp = '#';
					WHILE ctemp IS NOT NULL DO
						SET ptemp = concat(ptemp, ',', ctemp);
						SELECT group_concat(parent_node)
						INTO ctemp
						FROM graph_original_data 
						WHERE relation <> '下游' and FIND_IN_SET(child_node, ctemp);
					END WHILE;  
					insert into graph_temporary_table
					select parent_node,childNode as 'child_node'
					from graph_original_data where relation = '下位行业' and find_in_set(child_node,ptemp)
					group by parent_node,childNode order by null;
					fetch next from Curb into childNode;
				END WHILE; 
		 close Curb;
  END //
DELIMITER ;
call findHasTwoParentest();

结果