你有沒(méi)有遇到過(guò)這樣一種情況：

一張表就實(shí)現(xiàn)了一對(duì)多的關(guān)系，并且表中每一行數(shù)據(jù)都存在“爺爺-父親-兒子-…”的聯(lián)系，這也就是所謂的樹(shù)形結(jié)構(gòu)

對(duì)于這樣的表很顯然想要通過(guò)查詢(xún)來(lái)實(shí)現(xiàn)價(jià)值絕對(duì)是不能只靠select * from table 來(lái)實(shí)現(xiàn)的，下面提供兩種解決方案：

1.自連接

inner join 關(guān)鍵可以實(shí)現(xiàn)多種分類(lèi)的查詢(xún)，其實(shí)SQL很簡(jiǎn)單

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ī)矩矩的就查出一整棵樹(shù)

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

1.one

2.one,two

3.one,two,three

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

2.遞歸！

向上遞歸

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

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

先來(lái)看一個(gè)簡(jiǎ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：

這是遞歸查詢(xún)的開(kāi)始，創(chuàng)建了一個(gè)名為t1的遞歸表。

SELECT 1 AS n：

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

UNION ALL：

使用UNION ALL運(yùn)算符將初始行和遞歸查詢(xún)結(jié)果合并，形成遞歸步驟。這也就是下次遞歸的起點(diǎn)表

SELECT n+1 FROM t1 WHERE n<5：

遞歸部分的查詢(xún)，從t1表中選擇n加1的結(jié)果，當(dāng)n小于5時(shí)進(jìn)行遞歸。

SELECT * FROM t1：

最終查詢(xún)，返回t1表的所有行。

其實(shí)在使用遞歸的過(guò)程只需要注意要去避免死龜就好！

如何去查開(kāi)頭的那張樹(shù)形表呢？這樣就好:

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

下面我們逐幀分析：

其實(shí)關(guān)鍵的地方就在于第三步，在樹(shù)根的基礎(chǔ)上去找葉子：

神之一手：
select t.* from course_category t inner join temp on temp.id = t.parentid
這就是遞歸相較于第一種方式可以無(wú)視層級(jí)inner jion的關(guān)鍵，因?yàn)檫@個(gè)動(dòng)作已經(jīng)被遞歸自動(dòng)完成了，遞歸巧妙地一點(diǎn)就在這里！

向下遞歸

基于向上遞歸父找子的思想，向下遞歸則是子找父，即在葉子基礎(chǔ)上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表是下次遞歸的基礎(chǔ)
)
select *  from temp order by temp.id, temp.orderby

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

使用遞歸的好處無(wú)需言語(yǔ)，一次io連接就搞定了全部

總結(jié)

到此這篇關(guān)于Mysql樹(shù)形表的2種查詢(xún)解決方案的文章就介紹到這了,更多相關(guān)Mysql樹(shù)形表查詢(xún)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論