本篇文章為大家展示了MySQL中怎么實現(xiàn)樹狀數(shù)據(jù)，內(nèi)容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

創(chuàng)新互聯(lián)建站服務項目包括湄潭網(wǎng)站建設、湄潭網(wǎng)站制作、湄潭網(wǎng)頁制作以及湄潭網(wǎng)絡營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關系等，向廣大中小型企業(yè)、政府機構等提供互聯(lián)網(wǎng)行業(yè)的解決方案，湄潭網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前，我們服務的客戶以成都為中心已經(jīng)輻射到湄潭省份的部分城市，未來相信會繼續(xù)擴大服務區(qū)域并繼續(xù)獲得客戶的支持與信任！

0 樹狀數(shù)據(jù)的分類

我們在mysql數(shù)據(jù)庫設計的時候，會遇到一種樹狀的數(shù)據(jù)。如公司下面分開數(shù)個部門，部門下面又各自分開數(shù)個科室，以此形成樹狀的數(shù)據(jù)。關于樹狀的數(shù)據(jù)，按層級數(shù)大致可分為一下兩類：

前者的優(yōu)點在于，由于每一層級均有各自含義，數(shù)據(jù)庫的整體設計更為方便，可將某一子節(jié)點的不同上級節(jié)點均存儲在數(shù)據(jù)庫中，同樣以某集團為例：

這樣設計的表格冗余較多，但在各種類型查詢的時候效率較高.在插入，更新(含子機構,由于業(yè)務邏輯特點,機構之間的更新一般是平行轉移)，刪除(含子機構)的時候，由于冗余信息較多，數(shù)據(jù)操作時所需進行的查詢獲得也較簡單。根據(jù)情況,部分冗余信息也考慮刪去，如父級節(jié)點code，刪去一些設計必然會導致部分查詢的效率或復雜度提升，這個就需要根據(jù)實際情況來取舍平衡了。

缺點有兩個：

1. 一個是當層級數(shù)量較多的時候,需要存儲大量的冗余信息.當然也可以考慮節(jié)約方案：1)不存儲像n級祖先code這樣的字段，但這樣就無法利用固定層級設計帶來的高效查詢特性，是不建議這么做的；2)n級存儲不使用code而改用id，這樣做主要是在數(shù)據(jù)遷移或者他表利用的時候不方便。

2. 另一個缺點是，當需求方給出要求，需要對當前機構重新洗牌，變更層級數(shù)的時候，你會非常頭疼。

后者的優(yōu)缺點則與前者的優(yōu)缺點恰好相反，非固定的層級限制非常靈活，而缺點就是查詢及數(shù)據(jù)操作上兩方面的不便，這也是本文所要講述的重點，即如何設計非固定層級的樹狀數(shù)據(jù)。

1 非固定層級樹狀數(shù)據(jù)的設計方式--祖先路徑

樹狀數(shù)據(jù)最簡單的一種設計方式是,只增加父級id。但這種設計方式給查詢后代節(jié)點帶來了極大的不便，據(jù)我所知，尚沒有一種不通過函數(shù)/存儲過程這樣循環(huán)遍歷的查詢方式，來一次獲取某個節(jié)點的所有后代節(jié)點或是祖先節(jié)點。(此前找到過一個較復雜的查詢后代節(jié)點的sql，利用的也是祖先節(jié)點的id大于后代節(jié)點id的特性，但有可能存在通過更新節(jié)點使后代節(jié)點id大于祖先節(jié)點id，所以也不嚴謹，在此不進行詳述)

對于非固定層級樹狀數(shù)據(jù)的一種設計方式是：增加祖先路徑(ancestor_path)，具體可參考下表：

id | 節(jié)點名稱 | 父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,

實際設計時，還可考慮加入層級這個冗余字段，但我在實際使用的過程中很少用到這個字段。

這樣，在加了這個字段之后，任意節(jié)點的所有祖先節(jié)點信息就都可通過這樣一條數(shù)據(jù)全部獲取。

祖先路徑的設定具有以下特點：

1. 沒有父節(jié)點的根節(jié)點，父id默認為'0'，祖先路徑默認為'0,'；

2. 每增加的一個子節(jié)點,祖先路徑都是在要增加的子節(jié)點的父節(jié)點的祖先路徑上增加父id和','；參考的表結構如下：

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 祖先路徑的查詢

設計的樹節(jié)點的查詢，主要有兩種，一種是查詢某個節(jié)點的所有后代節(jié)點(與查詢祖先節(jié)點為某個已知節(jié)點的所有節(jié)點集合是一個意思)，這種也是最常用的一種查詢；一種是查詢某個節(jié)點的所有祖先節(jié)點，這種不太常用。

   1. 查詢某個節(jié)點的所有后代節(jié)點 參考示例如下：

SELECT * FROM t_node  WHERE ancestor_path LIKE CONCAT( (SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt), ?,',%')

以上sql即是對id為？的某個節(jié)點的所有后代節(jié)點的查詢方式一，還可使用以下方式：

SELECT * FROM t_node WHERE ancestor_path LIKE CONCAT('%,',?,',%')

查詢方式二的方式更加簡潔。但考慮到查詢方式一只用到了右模糊查詢，可以使用索引，所以還是建議使用方式一進行查詢。

需要注意的是以上兩種方式查到的節(jié)點集合都不包含子節(jié)點，如果需要包含該節(jié)點的信息，還需要加上

... OR node_id=?

    2. 查詢某個節(jié)點的所有祖先節(jié)點

SELECT * FROM t_node WHERE node_id REGEXP  CONCAT('^(', REPLACE((SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?) wt),',','|'), '0)$')

以上方式查詢祖先節(jié)點的效率確實不是很高，但考慮到該查詢本身并不用，便姑且用之了。

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個?均為要加入父節(jié)點的id。

   2. 更新(含子節(jié)點)

如果更新的時候，父節(jié)點的位置沒有變化,則不必考慮太多；

如果需要更新所在父節(jié)點，相比于最簡單的樹節(jié)點設計模式，增加祖先路徑的方式除了在更新當前節(jié)點本身的父id外，還需要修改對應的祖先路徑,這個步驟通過存儲過程實現(xià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é)點的id，?_p表示要修改的節(jié)點的新父節(jié)點的id。

注：使用該sql一定要先更新子節(jié)點的祖先路徑，再更新本節(jié)點的祖先路徑，如果是使用存儲過程的話就可以無視這一點了。

   3. 刪除(含子節(jié)點)

DELETE FROM t_node  WHERE ancestor_path LIKE CONCAT( (SELECT * FROM (SELECT ancestor_path FROM t_node WHERE node_id=?)wt), ?,',%')

刪除的核心在于where，和獲取所有后代節(jié)點的where可以說是完全一樣的。

同樣要主要先刪除所有后代節(jié)點，再刪除本節(jié)點；

4 祖先路徑的重置

有可能你此前的某個數(shù)據(jù)庫表格沒有使用過祖先路徑，但已經(jīng)積累了一定量的數(shù)據(jù)，或者之前使用了祖先路徑，但由于某種原因?qū)е伦嫦嚷窂降囊恍?shù)據(jù)更新錯誤。因為祖先路徑本質(zhì)上是一個冗余字段，所以還是可以通過父id的方式將之還原重置。

以下為機構表的一個重置存儲過程,供以參考：

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_reset_organ_path`(OUT resultMark varchar(50)) BEGIN      /*     使用前的說明:     1.本存儲過程非客戶使用,且自己人使用頻率同樣較低,故過程更方便調(diào)試,但效率不是很高;     2.如果執(zhí)行SELECT * FROM t_organ WHERE organ_id0 AND intLoopDone=0) DO -- 持續(xù)循環(huán),當沒有organId數(shù)據(jù)為止(如果中間機構中斷,則可能陷入死循環(huán))         SELECT COUNT(1) FROM tmp_pOrganIdList INTO intPCount;-- 當前父機構id的緩存區(qū)         SET intPIndex=0;         WHILE intPIndex<=intPCount DO -- 對每個當前查詢到的父id進行對應操作                          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);-- 次級機構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;-- 想要查看測試的結果,請看此表     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中怎么實現(xiàn)樹狀數(shù)據(jù)，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注創(chuàng)新互聯(lián)行業(yè)資訊頻道。