您好,登錄后才能下訂單哦!
本篇文章為大家展示了MySQL中怎么實(shí)現(xiàn)樹狀數(shù)據(jù),內(nèi)容簡明扼要并且容易理解,絕對(duì)能使你眼前一亮,通過這篇文章的詳細(xì)介紹希望你能有所收獲。
0 樹狀數(shù)據(jù)的分類
我們?cè)趍ysql數(shù)據(jù)庫設(shè)計(jì)的時(shí)候,會(huì)遇到一種樹狀的數(shù)據(jù)。如公司下面分開數(shù)個(gè)部門,部門下面又各自分開數(shù)個(gè)科室,以此形成樹狀的數(shù)據(jù)。關(guān)于樹狀的數(shù)據(jù),按層級(jí)數(shù)大致可分為一下兩類:
分類 | 特點(diǎn) |
---|---|
固定數(shù)量層級(jí) | 層級(jí)數(shù)量固定,每一層級(jí)都有各自的意義,如集團(tuán)-分公司-部門-科室,省-市-區(qū)等 |
可變數(shù)量層級(jí) | 層級(jí)數(shù)量不固定,前幾層級(jí)可能會(huì)有特殊含義,但整體在相當(dāng)大的范圍內(nèi)是浮動(dòng)的 |
前者的優(yōu)點(diǎn)在于,由于每一層級(jí)均有各自含義,數(shù)據(jù)庫的整體設(shè)計(jì)更為方便,可將某一子節(jié)點(diǎn)的不同上級(jí)節(jié)點(diǎn)均存儲(chǔ)在數(shù)據(jù)庫中,同樣以某集團(tuán)為例:
節(jié)點(diǎn)code | 節(jié)點(diǎn)名稱 | 節(jié)點(diǎn)層級(jí) | 父級(jí)節(jié)點(diǎn)code | 1級(jí)祖先code | 2級(jí)祖先cdoe |
---|---|---|---|---|---|
010000 | 公司1 | 1 | 000000 | null | null |
020000 | 公司2 | 1 | 000000 | null | null |
010300 | 制造部 | 2 | 010000 | 010000 | null |
010400 | 品質(zhì)部 | 2 | 010000 | 010000 | null |
010301 | 前工程制造 | 3 | 010300 | 010000 | 010300 |
010303 | 組裝制造 | 3 | 010300 | 010000 | 010300 |
這樣設(shè)計(jì)的表格冗余較多,但在各種類型查詢的時(shí)候效率較高.在插入,更新(含子機(jī)構(gòu),由于業(yè)務(wù)邏輯特點(diǎn),機(jī)構(gòu)之間的更新一般是平行轉(zhuǎn)移),刪除(含子機(jī)構(gòu))的時(shí)候,由于冗余信息較多,數(shù)據(jù)操作時(shí)所需進(jìn)行的查詢獲得也較簡單。根據(jù)情況,部分冗余信息也考慮刪去,如父級(jí)節(jié)點(diǎn)code,刪去一些設(shè)計(jì)必然會(huì)導(dǎo)致部分查詢的效率或復(fù)雜度提升,這個(gè)就需要根據(jù)實(shí)際情況來取舍平衡了。
缺點(diǎn)有兩個(gè):
一個(gè)是當(dāng)層級(jí)數(shù)量較多的時(shí)候,需要存儲(chǔ)大量的冗余信息.當(dāng)然也可以考慮節(jié)約方案:1)不存儲(chǔ)像n級(jí)祖先code這樣的字段,但這樣就無法利用固定層級(jí)設(shè)計(jì)帶來的高效查詢特性,是不建議這么做的;2)n級(jí)存儲(chǔ)不使用code而改用id,這樣做主要是在數(shù)據(jù)遷移或者他表利用的時(shí)候不方便。
另一個(gè)缺點(diǎn)是,當(dāng)需求方給出要求,需要對(duì)當(dāng)前機(jī)構(gòu)重新洗牌,變更層級(jí)數(shù)的時(shí)候,你會(huì)非常頭疼。
后者的優(yōu)缺點(diǎn)則與前者的優(yōu)缺點(diǎn)恰好相反,非固定的層級(jí)限制非常靈活,而缺點(diǎn)就是查詢及數(shù)據(jù)操作上兩方面的不便,這也是本文所要講述的重點(diǎn),即如何設(shè)計(jì)非固定層級(jí)的樹狀數(shù)據(jù)。
1 非固定層級(jí)樹狀數(shù)據(jù)的設(shè)計(jì)方式--祖先路徑
樹狀數(shù)據(jù)最簡單的一種設(shè)計(jì)方式是,只增加父級(jí)id。但這種設(shè)計(jì)方式給查詢后代節(jié)點(diǎn)帶來了極大的不便,據(jù)我所知,尚沒有一種不通過函數(shù)/存儲(chǔ)過程這樣循環(huán)遍歷的查詢方式,來一次獲取某個(gè)節(jié)點(diǎn)的所有后代節(jié)點(diǎn)或是祖先節(jié)點(diǎn)。(此前找到過一個(gè)較復(fù)雜的查詢后代節(jié)點(diǎn)的sql,利用的也是祖先節(jié)點(diǎn)的id大于后代節(jié)點(diǎn)id的特性,但有可能存在通過更新節(jié)點(diǎn)使后代節(jié)點(diǎn)id大于祖先節(jié)點(diǎn)id,所以也不嚴(yán)謹(jǐn),在此不進(jìn)行詳述)
對(duì)于非固定層級(jí)樹狀數(shù)據(jù)的一種設(shè)計(jì)方式是:增加祖先路徑(ancestor_path),具體可參考下表:
id | 節(jié)點(diǎn)名稱 | 父id | 祖先路徑
--- | --- | --- | --- 1 | node1 | 0 | 0, 2 | node2 | 0 | 0, 3 | node1.1 | 1 | 0,1, 4 | node1.2 | 1 | 0,1, 5 | node2.1 | 2 | 0,2, 6 | node1.1.1 | 3 | 0,1,3, 7 | node1.1.2 | 3 | 0,1,3, 8 | node1.2.1 | 4 | 0,1,4, 9 | node2.1.1 | 5 | 0,2,5,
實(shí)際設(shè)計(jì)時(shí),還可考慮加入層級(jí)這個(gè)冗余字段,但我在實(shí)際使用的過程中很少用到這個(gè)字段。
這樣,在加了這個(gè)字段之后,任意節(jié)點(diǎn)的所有祖先節(jié)點(diǎn)信息就都可通過這樣一條數(shù)據(jù)全部獲取。
祖先路徑的設(shè)定具有以下特點(diǎn):
沒有父節(jié)點(diǎn)的根節(jié)點(diǎn),父id默認(rèn)為'0',祖先路徑默認(rèn)為'0,';
每增加的一個(gè)子節(jié)點(diǎn),祖先路徑都是在要增加的子節(jié)點(diǎn)的父節(jié)點(diǎn)的祖先路徑上增加父id和',';參考的表結(jié)構(gòu)如下:
CREATE TABLE `t_node` ( `node_id` int(11) NOT NULL AUTO_INCREMENT, `node_name` varchar(50) NOT NULL, `p_id` int(11) NOT NULL, `ancestor_path` varchar(100) NOT NULL, PRIMARY KEY (`node_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
2 祖先路徑的查詢
設(shè)計(jì)的樹節(jié)點(diǎn)的查詢,主要有兩種,一種是查詢某個(gè)節(jié)點(diǎn)的所有后代節(jié)點(diǎn)(與查詢祖先節(jié)點(diǎn)為某個(gè)已知節(jié)點(diǎn)的所有節(jié)點(diǎn)集合是一個(gè)意思),這種也是最常用的一種查詢;一種是查詢某個(gè)節(jié)點(diǎn)的所有祖先節(jié)點(diǎn),這種不太常用。
1. 查詢某個(gè)節(jié)點(diǎn)的所有后代節(jié)點(diǎn) 參考示例如下:
SELECT * FROM t_node WHERE ancestor_path LIKE CONCAT( (SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt), ?,',%')
以上sql即是對(duì)id為?的某個(gè)節(jié)點(diǎn)的所有后代節(jié)點(diǎn)的查詢方式一,還可使用以下方式:
SELECT * FROM t_node WHERE ancestor_path LIKE CONCAT('%,',?,',%')
查詢方式二的方式更加簡潔。但考慮到查詢方式一只用到了右模糊查詢,可以使用索引,所以還是建議使用方式一進(jìn)行查詢。
需要注意的是以上兩種方式查到的節(jié)點(diǎn)集合都不包含子節(jié)點(diǎn),如果需要包含該節(jié)點(diǎn)的信息,還需要加上
... OR node_id=?
2. 查詢某個(gè)節(jié)點(diǎn)的所有祖先節(jié)點(diǎn)
SELECT * FROM t_node WHERE node_id REGEXP CONCAT('^(', REPLACE((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?) wt),',','|'), '0)$')
以上方式查詢祖先節(jié)點(diǎn)的效率確實(shí)不是很高,但考慮到該查詢本身并不用,便姑且用之了。
3 祖先路徑的插入,更新和刪除
分別分插入,更新和刪除來講:
1. 插入
INSERT INTO t_node (node_name,p_id,ancestor_path) VALUE('node?',?, CONCAT((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt),?,','))
sql中的3個(gè)?均為要加入父節(jié)點(diǎn)的id。
2. 更新(含子節(jié)點(diǎn))
如果更新的時(shí)候,父節(jié)點(diǎn)的位置沒有變化,則不必考慮太多;
如果需要更新所在父節(jié)點(diǎn),相比于最簡單的樹節(jié)點(diǎn)設(shè)計(jì)模式,增加祖先路徑的方式除了在更新當(dāng)前節(jié)點(diǎn)本身的父id外,還需要修改對(duì)應(yīng)的祖先路徑,這個(gè)步驟通過存儲(chǔ)過程實(shí)現(xiàn),是一種比較簡單的方式,在此不再詳述。僅對(duì)不使用存儲(chǔ)過程的方式進(jìn)行描述。
UPDATE t_node SET p_id=?_p WHERE node_id=?_n; UPDATE t_node SET ancestor_path=CONCAT((SELECT * FROM(SELECT ancestor_path FROM t_node WHERE node_id=?_p)wt2),?_p,',',SUBSTR(ancestor_path,LENGTH(@PPath)+1)) WHERE ancestor_path LIKE CONCAT((SELECT * FROM (SELECT @ppath:=ancestor_path FROM t_node WHERE node_id=?_n)wt),?_n,',%') OR node_id=?_n ;
其中?_n表示要修改的節(jié)點(diǎn)的id,?_p表示要修改的節(jié)點(diǎn)的新父節(jié)點(diǎn)的id。
注:使用該sql一定要先更新子節(jié)點(diǎn)的祖先路徑,再更新本節(jié)點(diǎn)的祖先路徑,如果是使用存儲(chǔ)過程的話就可以無視這一點(diǎn)了。
3. 刪除(含子節(jié)點(diǎn))
DELETE FROM t_node WHERE ancestor_path LIKE CONCAT( (SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt), ?,',%')
刪除的核心在于where,和獲取所有后代節(jié)點(diǎn)的where可以說是完全一樣的。
同樣要主要先刪除所有后代節(jié)點(diǎn),再刪除本節(jié)點(diǎn);
4 祖先路徑的重置
有可能你此前的某個(gè)數(shù)據(jù)庫表格沒有使用過祖先路徑,但已經(jīng)積累了一定量的數(shù)據(jù),或者之前使用了祖先路徑,但由于某種原因?qū)е伦嫦嚷窂降囊恍?shù)據(jù)更新錯(cuò)誤。因?yàn)樽嫦嚷窂奖举|(zhì)上是一個(gè)冗余字段,所以還是可以通過父id的方式將之還原重置。
以下為機(jī)構(gòu)表的一個(gè)重置存儲(chǔ)過程,供以參考:
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_reset_organ_path`(OUT resultMark varchar(50)) BEGIN /* 使用前的說明: 1.本存儲(chǔ)過程非客戶使用,且自己人使用頻率同樣較低,故過程更方便調(diào)試,但效率不是很高; 2.如果執(zhí)行SELECT * FROM t_organ WHERE organ_id<parent_organ_id(即父機(jī)構(gòu)產(chǎn)生于子機(jī)構(gòu)之后)后的數(shù)據(jù)為空,則可以考慮使用分段模式(速度會(huì)快一些). 3.如果2中所述數(shù)據(jù)不為空,使用分段會(huì)使該id對(duì)應(yīng)的機(jī)構(gòu)及其子機(jī)構(gòu)的ancestor_path不正確.結(jié)果為partfail. */ DECLARE intACount INT(11) DEFAULT 0; DECLARE intPCount INT(11) DEFAULT 0; DECLARE intPIndex INT(11) DEFAULT 0; DECLARE intPOrganId INT(11) DEFAULT 0; DECLARE strPPath VARCHAR(100) DEFAULT ''; DECLARE intLoopDone INT(11) DEFAULT 0; DECLARE intRCount INT(11) DEFAULT 0; DECLARE intRIndex INT(11) DEFAULT 0; DECLARE intROrganId INT(11) DEFAULT 0; DROP TABLE IF EXISTS tmp_aOrganIdList; CREATE TEMPORARY TABLE tmp_aOrganIdList( rowid INT(11) auto_increment PRIMARY KEY, organ_id INT(11), p_organ_id INT(11) ); DROP TABLE IF EXISTS tmp_pOrganIdList; CREATE TEMPORARY TABLE tmp_pOrganIdList( rowid INT(11) auto_increment PRIMARY KEY, organ_id INT(11) ); /**/ DROP TABLE IF EXISTS tmp_cOrganIdList; CREATE TEMPORARY TABLE tmp_cOrganIdList( rowid INT(11) auto_increment PRIMARY KEY, organ_id INT(11) ); DROP TABLE IF EXISTS tmp_rOrganIdList; CREATE TEMPORARY TABLE tmp_rOrganIdList( rowid INT(11) auto_increment PRIMARY KEY, organ_id INT(11), p_organ_id INT(11), ancestor_path VARCHAR(100) ); INSERT INTO tmp_aOrganIdList (organ_id,p_organ_id) (SELECT organ_id,parent_organ_id FROM t_organ);-- 測試的時(shí)候limit: LIMIT 0,100 INSERT INTO tmp_pOrganIdList (organ_id) VALUES (0); INSERT INTO tmp_rOrganIdList (organ_id,p_organ_id,ancestor_path) VALUES (0,-1,''); WHILE ((SELECT COUNT(1) FROM tmp_aOrganIdList)>0 AND intLoopDone=0) DO -- 持續(xù)循環(huán),當(dāng)沒有organId數(shù)據(jù)為止(如果中間機(jī)構(gòu)中斷,則可能陷入死循環(huán)) SELECT COUNT(1) FROM tmp_pOrganIdList INTO intPCount;-- 當(dāng)前父機(jī)構(gòu)id的緩存區(qū) SET intPIndex=0; WHILE intPIndex<=intPCount DO -- 對(duì)每個(gè)當(dāng)前查詢到的父id進(jìn)行對(duì)應(yīng)操作 SELECT organ_id FROM tmp_pOrganIdList LIMIT intPIndex,1 INTO intPOrganId; SELECT ancestor_path FROM tmp_rOrganIdList WHERE organ_id=intPOrganId INTO strPPath; INSERT INTO tmp_cOrganIdList (organ_id) (SELECT organ_id FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId);-- 次級(jí)機(jī)構(gòu)id的緩存區(qū) -- SELECT COUNT(1) FROM tmp_pOrganIdList INTO intDelCount; INSERT INTO tmp_rOrganIdList (organ_id,p_organ_id,ancestor_path) (SELECT organ_id,intPOrganId,CONCAT(strPPath,intPOrganId,',') FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId); DELETE FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId; SET intPIndex=intPIndex+1; END WHILE; DELETE FROM tmp_pOrganIdList; IF (SELECT COUNT(1) FROM tmp_cOrganIdList)>0 THEN INSERT INTO tmp_pOrganIdList (organ_id) (SELECT organ_id FROM tmp_cOrganIdList); DELETE FROM tmp_cOrganIdList; ELSE SET intLoopDone=1; END IF; -- SELECT * FROM tmp_pOrganIdList; -- SELECT COUNT(1) FROM tmp_aOrganIdList; -- SELECT intLoopDone; END WHILE; -- SELECT * FROM tmp_rOrganIdList;-- 想要查看測試的結(jié)果,請(qǐng)看此表 SELECT COUNT(1) FROM tmp_rOrganIdList INTO intRCount; WHILE intRIndex<=intRCount DO SELECT organ_id,ancestor_path FROM tmp_rOrganIdList LIMIT intRIndex,1 INTO intROrganId,strPPath; UPDATE t_organ SET ancestor_path=strPPath WHERE organ_id=intROrganId; SET intRIndex=intRIndex+1; END WHILE; IF (SELECT COUNT(1) FROM tmp_aOrganIdList)=0 THEN SET resultMark='perfect'; ELSE SET resultMark='partfail'; END IF; END
上述內(nèi)容就是MySQL中怎么實(shí)現(xiàn)樹狀數(shù)據(jù),你們學(xué)到知識(shí)或技能了嗎?如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備,歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。