Mysql樹形表的2種查詢解決方案(遞歸與自連接)

更新時間：2023年11月25日 08:35:04 作者：懶羊羊.java

MySQL作為一個關系型數(shù)據(jù)庫,存儲著許多的數(shù)據(jù)信息,在實際應用中經(jīng)常會遇到需要存儲樹形結構數(shù)據(jù)的情境,例如部門結構、商品分類等,這篇文章主要給大家介紹了關于Mysql樹形表的2種查詢解決方案,分別是遞歸與自連接,需要的朋友可以參考下

1.自連接

inner join 關鍵可以實現(xiàn)多種分類的查詢，其實SQL很簡單

SELECT
	one.id one_id,
	one.label one_label,
	two.id two_id,
	two.label two_label
FROM
	course_category one
	INNER JOIN course_category two ON two.parentid=one.id
	INNER JOIN course_category three ON three.parentid=two.id
	WHERE one.id='1' AND one.is_show='1' AND two.is_show='1'
	ORDER BY one.orderby,two.orderby

也是規(guī)規(guī)矩矩的就查出一整棵樹

這種查詢的原則就是通過parentId去實現(xiàn)，“爺爺找爸爸，爸爸找兒子，兒子找孫子”,下面來逐幀慢放：

1.one

2.one,two

3.one,two,three

可以看到，只有在樹的層級確定的情況下我才能選擇性的去自連接子表，某種意義上來講這種方法存在弊端，我要是insert進去層級更低的新子節(jié)點那我的sql就得改變，從而就造成了一個“動一發(fā)而牽全身”的硬編碼問題，實在是不夠穩(wěn)妥！

2.遞歸！

向上遞歸

首先聲明，如果mysql的版本低于8是不支持遞歸查詢的函數(shù)的！

下面來看一下如何用遞歸優(yōu)雅的實現(xiàn)，從樹根查到樹頂：

先來看一個簡單的Demo

	with RECURSIVE t1 AS(
		SELECT 1 AS n
		union all
		SELECT n+1 FROM t1 WHERE n<5
	)
	SELECT * from t1

該怎么理解這每一步呢？

WITH RECURSIVE t1 AS：

這是遞歸查詢的開始，創(chuàng)建了一個名為t1的遞歸表。

SELECT 1 AS n：

在t1表中，插入了一個初始行，值為1，命名為n。

UNION ALL：

使用UNION ALL運算符將初始行和遞歸查詢結果合并，形成遞歸步驟。這也就是下次遞歸的起點表

SELECT n+1 FROM t1 WHERE n<5：

遞歸部分的查詢，從t1表中選擇n加1的結果，當n小于5時進行遞歸。

SELECT * FROM t1：

最終查詢，返回t1表的所有行。

其實在使用遞歸的過程只需要注意要去避免死龜就好！

如何去查開頭的那張樹形表呢？這樣就好:

with recursive temp as (
select * from  course_category p where  id= '1'
 union all
select t.* from course_category t inner join temp on temp.id = t.parentid
)
select *  from temp order by temp.id, temp.orderby

下面我們逐幀分析：

其實關鍵的地方就在于第三步，在樹根的基礎上去找葉子：

神之一手：
select t.* from course_category t inner join temp on temp.id = t.parentid
這就是遞歸相較于第一種方式可以無視層級inner jion的關鍵，因為這個動作已經(jīng)被遞歸自動完成了，遞歸巧妙地一點就在這里！

向下遞歸

基于向上遞歸父找子的思想，向下遞歸則是子找父，即在葉子基礎上union all之后去找根

子的parentId=父的id

with recursive temp as (
select * from  course_category p where  id= '1-1-1'
 union all
select t.* from course_category t inner join temp on temp.parentid = t.id  
//temp表是下次遞歸的基礎
)
select *  from temp order by temp.id, temp.orderby

值得注意的是Mysql為了避免無限遞歸遞歸次數(shù)為1000次，也可以人為來設置cte_max_recursion_depth和max_execution_time來自定義遞歸深度和執(zhí)行時間

使用遞歸的好處無需言語，一次io連接就搞定了全部