【JAVA秒会技术之搞定数据库递归树】Mysql快速实现递归树状查询

Mysql快速实现递归树状查询

【前言】今天一个好朋友问我的这个问题，以前也没有用到过，恰好有时间，就帮他研究了一下，纯属“现学现卖”，正好在过程中，自己也能学习一下！个人感觉，其实一点也不难，不过是“闻道有先后”，我们是“后”罢了。按照我的习惯，学完东西，总要总结一下嘛，也当做一个备忘录了。

具体需求就不描述了，简而言之，归结为两个：

1.如何通过子节点（cid）加载出所有的父节点（pid）?

2.如何通过父节点（pid）加载出所有的子节点（cid）?

废话不多说，直接上简易教程：

1.创建一个测试表

CREATE TABLE treeNodes
(
 id INT PRIMARY KEY,
     nodename VARCHAR(20),
 pid INT
);

2.编写测试数据

INSERT INTO treeNodes VALUES
(1,'A',0),(2,'B',1),(3,'C',1),
(4,'D',2),(5,'E',2),(6,'F',2),
(7,'G',3),(8,'H',6),(9,'I',0),
(10,'J',8),(11,'K',8),(12,'L',8),
(13,'M',9),(14,'N',12),(15,'O',12),
(16,'P',15),(17,'Q',15);

3.实际树型结构

 1:A
  +-- 2:B
  |    +-- 4:D
  |    +-- 5:E
  |    +-- 6:F
  |    |    +-- 8:H
  |    |    |    +-- 10:J
  |    |    |    +-- 11:K 
  |    |    |    +-- 12:L
  |    |    |    |    +-- 14:N
  |    |    |    |    +-- 15:O
  |    |    |    |    |    +-- 16:P
  |    |    |    |    |    +-- 17:Q
  +-- 3:C
  |    +-- 7:G

 9:I
  +-- 13:M

4.创建通过子节点（cid）加载出所有的父节点（pid）的存储函数

DELIMITER //    
CREATE FUNCTION `getParentList`(rootId INT) 
     RETURNS CHAR(255) 
     BEGIN 
		 DECLARE fid INT DEFAULT 1;
		 DECLARE str CHAR(255) DEFAULT rootId;
	     WHILE rootId IS NOT NULL DO 
			SET fid=(SELECT pid FROM treenodes  WHERE rootId=id); 
			 IF fid > 0 THEN  
				 SET str=CONCAT(str,',',fid);   
				 SET rootId=fid;  
			 ELSE 
				SET rootId=fid;  
			 END IF;  
		END WHILE;
		RETURN str;
     END  //

SELECT getParentList(7); 【结果：7,3,1】

DELIMITER // 
CREATE FUNCTION `getChildList`(rootId varchar(100)) 
	RETURNS varchar(2000)
	BEGIN 
		DECLARE str varchar(2000);
		DECLARE cid varchar(100); 
		SET str = '$'; 
		SET cid = rootId; 
		WHILE cid is not null DO 
			SET str = concat(str, ',', cid); 
			SELECT group_concat(id) INTO cid FROM treeNodes where FIND_IN_SET(pid, cid) > 0; 
		END WHILE; 
		RETURN str; 
	END //

7.测试（找出id=1的所有子节点）

SELECT getChildList(1); 【结果：$,1,2,3,4,5,6,7,8,10,11,12,14,15,16,17】

8.补充：以上一组简单的教程，主要也是总结于网上的各种资料，其实我个人觉得，对于程序员来说“复制，粘贴”没有什么好不耻的，重点是在于：“复制，粘贴”别人的东西后，要学会分析与进一步的思考，并且能够举一反三，最好之后还能认真的总结一下。这样，别人的东西，才变成了你的东西。并且，你可能比之前那个人做的更好；如此下去，才是一个良性循环！

下面我们就简单分析一下这个存储函数的结构：

之后，我又根据朋友的特殊需求，进行了修改，主要是我朋友想查出的并不是各种id，而是希望通过一个子节点的id，直接查出所有父级的名称，便于展示。

DELIMITER // 
CREATE FUNCTION `getParentList`(rootId VARCHAR(100)) 
	RETURNS VARCHAR(1000) 
BEGIN 
	DECLARE parentId VARCHAR(100) DEFAULT ''; 
	DECLARE str VARCHAR(1000) DEFAULT ''; 
	DECLARE parentName VARCHAR(100) DEFAULT ''; 
	SET str = (SELECT budget_account_name FROM pms_budget_account WHERE id = rootId);
	WHILE rootId IS NOT NULL  DO 
		SET parentId =(SELECT parent_id FROM pms_budget_account WHERE id = rootId);
		IF parentId IS NOT NULL THEN 		
			SET parentName = (SELECT budget_account_name FROM pms_budget_account WHERE id = parentId);
			
			SET str = CONCAT(str, ',', parentName); 
			SET rootId = parentId; 
		ELSE 
			SET rootId = parentId; 
		END IF; 
	END WHILE; 
	RETURN str;
END	 //

至此，才是一个完整的学习过程，也是我常用的学习方式：