mysql樹狀資料的資料庫設計

0 樹狀資料的分類

我們在mysql資料庫設計的時候,會遇到一種樹狀的資料.如公司下面分開數個部門,部門下面又各自分開數個科室,以此形成樹狀的資料.關於樹狀的資料,按層級數大致可分為一下兩類:

前者的優點在於,由於每一層級均有各自含義,資料庫的整體設計更為方便,可將某一子節點的不同上級節點均儲存在資料庫中,同樣以某集團為例:

這樣設計的表格冗餘較多,但在各種型別查詢的時候效率較高.在插入,更新(含子機構,由於業務邏輯特點,機構之間的更新一般是平行轉移),刪除(含子機構)的時候,由於冗餘資訊較多,資料操作時所需進行的查詢獲得也較簡單.根據情況,部分冗餘資訊也考慮刪去,如父級節點code,刪去一些設計必然會導致部分查詢的效率或複雜度提升,這個就需要根據實際情況來取捨平衡了.
缺點有兩個:

1. 一個是當層級數量較多的時候,需要儲存大量的冗餘資訊.當然也可以考慮節約方案:1)不儲存像n級祖先code這樣的欄位,但這樣就無法利用固定層級設計帶來的高效查詢特性,是不建議這麼做的;2)n級儲存不使用code而改用id,這樣做主要是在資料遷移或者他表利用的時候不方便.
2. 另一個缺點是,當需求方給出要求,需要對當前機構重新洗牌,變更層級數的時候,你會非常頭疼.

後者的優缺點則與前者的優缺點恰好相反,非固定的層級限制非常靈活,而缺點就是查詢及資料操作上兩方面的不便,這也是本文所要講述的重點,即如何設計非固定層級的樹狀資料.

1 非固定層級樹狀資料的設計方式–祖先路徑

樹狀資料最簡單的一種設計方式是,只增加父級id.但這種設計方式給查詢後代節點帶來了極大的不便,據我所知,尚沒有一種不通過函式/儲存過程這樣迴圈遍歷的查詢方式,來一次獲取某個節點的所有後代節點或是祖先節點.(此前找到過一個較複雜的查詢後代節點的sql,利用的也是祖先節點的id大於後代節點id的特性,但有可能存在通過更新節點使後代節點id大於祖先節點id,所以也不嚴謹,在此不進行詳述)
對於非固定層級樹狀資料的一種設計方式是:增加祖先路徑(ancestor_path),具體可參考下表:
id | 節點名稱 | 父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,
實際設計時,還可考慮加入層級這個冗餘欄位,但我在實際使用的過程中很少用到這個欄位.
這樣,在加了這個欄位之後,任意節點的所有祖先節點資訊就都可通過這樣一條資料全部獲取.
祖先路徑的設定具有以下特點:

1. 沒有父節點的根節點,父id預設為’0′,祖先路徑預設為’0,’;
2. 每增加的一個子節點,祖先路徑都是在要增加的子節點的父節點的祖先路徑上增加父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 祖先路徑的查詢

設計的樹節點的查詢,主要有兩種,一種是查詢某個節點的所有後代節點(與查詢祖先節點為某個已知節點的所有節點集合是一個意思),這種也是最常用的一種查詢;一種是查詢某個節點的所有祖先節點,這種不太常用.

1. 查詢某個節點的所有後代節點
   參考示例如下:

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

以上sql即是對id為?的某個節點的所有後代節點的查詢方式一,還可使用以下方式:

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

查詢方式二的方式更加簡潔.但考慮到查詢方式一只用到了右模糊查詢,可以使用索引,所以還是建議使用方式一進行查詢.
需要注意的是以上兩種方式查到的節點集合都不包含子節點,如果需要包含該節點的資訊,還需要加上

... OR node_id=?

2. 查詢某個節點的所有祖先節點

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

以上方式查詢祖先節點的效率確實不是很高,但考慮到該查詢本身並不用,便姑且用之了.

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個?均為要加入父節點的id.

2. 更新(含子節點)
   如果更新的時候,父節點的位置沒有變化,則不必考慮太多;
   如果需要更新所在父節點,相比於最簡單的樹節點設計模式,增加祖先路徑的方式除了在更新當前節點本身的父id外,還需要修改對應的祖先路徑,這個步驟通過儲存過程實現,是一種比較簡單的方式,在此不再詳述.僅對不使用儲存過程的方式進行描述.

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表示要修改的節點的id,?_p表示要修改的節點的新父節點的id.
注:使用該sql一定要先更新子節點的祖先路徑,再更新本節點的祖先路徑,如果是使用儲存過程的話就可以無視這一點了.

3. 刪除(含子節點)

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

刪除的核心在於where,和獲取所有後代節點的where可以說是完全一樣的.
同樣要主要先刪除所有後代節點,再刪除本節點;

4 祖先路徑的重置

有可能你此前的某個資料庫表格沒有使用過祖先路徑,但已經積累了一定量的資料,或者之前使用了祖先路徑,但由於某種原因導致祖先路徑的一些資料更新錯誤.因為祖先路徑本質上是一個冗餘欄位,所以還是可以通過父id的方式將之還原重置.
以下為機構表的一個重置儲存過程,供以參考:

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_reset_organ_path`(OUT resultMark varchar(50))
BEGIN
/*
使用前的說明:
1.本儲存過程非客戶使用,且自己人使用頻率同樣較低,故過程更方便除錯,但效率不是很高;
2.如果執行SELECT * FROM t_organ WHERE organ_id<parent_organ_id(即父機構產生於子機構之後)後的資料為空,則可以考慮使用分段模式(速度會快一些).
3.如果2中所述資料不為空,使用分段會使該id對應的機構及其子機構的ancestor_path不正確.結果為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);-- 測試的時候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 -- 持續迴圈,當沒有organId資料為止(如果中間機構中斷,則可能陷入死迴圈)
SELECT COUNT(1) FROM tmp_pOrganIdList INTO intPCount;-- 當前父機構id的快取區
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的快取區
-- 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