数据库树形遍历查询

表结构

mysql

5.0及以后

方式一：自连接

优点：性能会比递归高，尤其是数据量大的时候。

缺点：需要手动连接层数，如果层数较多的话，会比较复杂，且表结构变化的话，容易导致自连接不稳定

SELECT p2.*
FROM posts_category p1
RIGHT JOIN posts_category p2 ON p1.id = p2.parent_id
WHERE p2.is_show = 1;

输出结果

方式二：存储过程

优点：

1. 可以将复杂的操作封装成一个方法，通过call调用，简化操作，而且如果表面，列名或业务逻辑有变化，只要更改存储过程（方法）中的代码，使用它的人并不需要知道这些变化（黑盒）。
2. 由于不要求反复建立一系列处理步骤，这保证了数据的完整性
3. 提高性能：因为使用存储过程比单独的sql语句要快
4. 灵活性高：存在一些只能用在单个请求中的MySql元素和特性，存储过程可以使用它们来编写功能性更强更灵活的代码

缺点：

1. 存储过程编写比基本sql语句复杂，编写存储过程需要更高的技能，更丰富的经验。
2. 你可能没有创建存储过程的权限，许多数据库管理员会限制存储过程的创建权限。

存储过程的创建权限和执行权限是分开的，所以虽然你没有创建权限，但是你依然可以使用存储过程。

CREATE DEFINER=`root`@`localhost` PROCEDURE `showcategory`(IN rootId VARCHAR(50))
    COMMENT '显示临时表中的文章类别'
BEGIN
	#Routine body goes here...
	CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst
(id VARCHAR(50) primary key,name VARCHAR(50),parent_id VARCHAR(50));
DELETE FROM tmpLst;

CALL getcategory(rootId);

SELECT id, cname as name, pid parent_id FROM tmpLst;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `getcategory`(IN start_id VARCHAR(50))
    COMMENT '树形递归父节点下的字节点，并将数据插入临时表tmpLst'
BEGIN
	
-- 	声明三个变量
	DECLARE done INT DEFAULT FALSE;
	DECLARE child_id VARCHAR(50);
	DECLARE child_name VARCHAR(50);
-- 	声明一个游标
	DECLARE cur CURSOR FOR
		SELECT id, name FROM posts_category WHERE parent_id = start_id AND is_show = 1 ORDER BY orderby;
-- 		如果没找到数据就令done = TRUE，也就是终止
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	    #设置最大递归深度
	SET @@max_sp_recursion_depth = 12;
-- 打开cur游标（就是执行遍历）
	OPEN cur;
	read_loop: LOOP
		FETCH cur INTO child_id, child_name;
		
		IF done THEN
			LEAVE read_loop;
		END IF;
-- 		将数据插入到临时表存储起来
			INSERT INTO tmpLst VALUES(child_id, child_name, start_id);	
-- 			递归调用
		CALL getcategory(child_id);
	END LOOP;
	CLOSE cur;
END

运行结果：

8.0及以后

递归:CTE

-- 可以理解为定义一个方法，方法名为category_tree
WITH RECURSIVE category_tree AS(
-- 方法体，找出根id为1的树
SELECT id, name, parent_id FROM posts_category WHERE id = "1"
UNION ALL
-- 将要查找的表内连接到本方法(category_tree)
SELECT c.id, c.name, c.parent_id FROM posts_category c JOIN category_tree ct ON c.parent_id = ct.id
)
-- 调用方法
SELECT * FROM category_tree;

Oracle

递归（暂定，还没创建Oracle数据库)