基本上在每個系統中都有那麼幾張表是自關聯父子關係的結構。往往有很多人都是使用pid來做關聯。在剛進入IT行業時使用CAKEPHP框架編寫WEB的時候，使用它裡面的一個ACL plugin實現許可權管理的時候。發現一個表結構硬是不明白是怎麼回事。具體表結構如下：

?

CREATE TABLE acos ( id INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INTEGER(10) DEFAULT NULL, model VARCHAR(255) DEFAULT '', foreign_key INTEGER(10) UNSIGNED DEFAULT NULL, alias VARCHAR(255) DEFAULT '', lft INTEGER(10) DEFAULT NULL, rght INTEGER(10) DEFAULT NULL, PRIMARY KEY (id));

我們可以看到上面 acos 表用有lft、rght這兩個欄位。起初我根本就不明白這兩個是做什麼用的，幾次直接修改資料導致資料錯亂。

1.2. 原理解釋

其實這就是樹的後續遍歷的每個節點的左值、右值。如下圖表示:

1.3. 樹的使用（引用上圖樹結構）

構造資料

?

DROP TABLE IF EXISTS comment;CREATE TABLE `comment` ( `comment_id` int(11) DEFAULT NULL, `left_num` int(11) DEFAULT NULL, `right_num` int(11) DEFAULT NULL); INSERT INTO `comment` VALUES (1,1,14), (2,2,5), (3,3,4), (4,6,13), (5,7,8), (6,9,12), (7,10,11); CREATE INDEX idx$comment$left_num$right_num ON `comment` (`left_num`, `right_num`);

查詢 '節點4' 的所有子節點

思路:我們只要查詢出節點左值在 '節點4' 左值和右值之間的節點
通俗說法:能被 '節點4' 包住的節點,透過左節點和右節點來判斷是否被 '節點4' 包住。

?

-- 獲得 '節點4' 孩子SELECT c.*FROM comment AS p, comment AS cWHERE c.left_num BETWEEN p.left_num AND p.right_num AND p.comment_id = 4;+------------+----------+-----------+| comment_id | left_num | right_num |+------------+----------+-----------+| 4 | 6 | 13 || 5 | 7 | 8 || 6 | 9 | 12 || 7 | 10 | 11 |+------------+----------+-----------+

查詢 '節點6' 的所有父節點
思路: 找出左值小於 '節點6' 並且右值大於 '節點6' 的節點。
通俗說法: 找出那個節點能將 '節點6' 給包住。

?

-- 獲得 '節點6' 父親SELECT p.* FROM comment AS p, comment AS cWHERE c.left_num BETWEEN p.left_num AND p.right_num AND c.comment_id = 6;+------------+----------+-----------+| comment_id | left_num | right_num |+------------+----------+-----------+| 1 | 1 | 14 || 4 | 6 | 13 || 6 | 9 | 12 |+------------+----------+-----------+

計算 '節點4' 的深度
如果是MySQL5.7 需要修改sql_mode

?

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';SELECT c.*, COUNT(c.comment_id) AS depthFROM comment AS p, comment AS cWHERE c.left_num BETWEEN p.left_num AND p.right_num AND c.comment_id = 4GROUP BY c.comment_id;+------------+----------+-----------+-------+| comment_id | left_num | right_num | depth |+------------+----------+-----------+-------+| 4 | 6 | 13 | 2 |+------------+----------+-----------+-------+

獲取 '節點4' 的所有子節點, 和相關深度

?

SELECT sub_child.*, (COUNT(sub_parent.comment_id) - 1) AS depthFROM ( SELECT child.* FROM comment AS parent, comment AS child WHERE child.left_num BETWEEN parent.left_num AND parent.right_num AND parent.comment_id = 4) AS sub_child, ( SELECT child.* FROM comment AS parent, comment AS child WHERE child.left_num BETWEEN parent.left_num AND parent.right_num AND parent.comment_id = 4) AS sub_parentWHERE sub_child.left_num BETWEEN sub_parent.left_num AND sub_parent.right_numGROUP BY sub_child.comment_idORDER BY sub_child.left_num;+------------+----------+-----------+-------+| comment_id | left_num | right_num | depth |+------------+----------+-----------+-------+| 4 | 6 | 13 | 0 || 5 | 7 | 8 | 1 || 6 | 9 | 12 | 1 || 7 | 10 | 11 | 2 |+------------+----------+-----------+-------+

插入資料
資料的插入是一件相當麻煩的事，需要更新節點的所有父節點的右值和和所有孩子節點的 '左值、右值'
如上圖，如果我們想為 '節點4' 新增一個孩子 '節點44'(為了不給自己挖坑，我們將新增的孩子放在父節點的最左邊)，就是將 '節點44' 放在 '節點5' 的左邊。如下圖：

最終我們獲得的結果，如下圖：

上圖 '紫色' 的是節點需要變更的左值和右值，'綠色' 的是新增節點的值。
更新思路：
1、將左值大於 '節點4' 的左值的節點的左值加2。
2、將右值大於 '節點4' 的左值的節點的右值加2。

?

-- 獲得 '節點4' 和 '節點4'的第一個孩子的(節點5)的左右值SELECT c.*FROM comment AS p, comment AS cWHERE c.left_num BETWEEN p.left_num AND p.right_num AND p.comment_id = 4;+------------+----------+-----------+| comment_id | left_num | right_num |+------------+----------+-----------+| 4 | 6 | 13 || 5 | 7 | 8 |... omit ...-- 透過上面獲得的資訊更新 '節點4' 的父子幾點的左右值UPDATE comment SET left_num = left_num + 2 WHERE left_num > 6;UPDATE comment SET right_num = right_num + 2 WHERE right_num > 6;

插入思路
1、將 '節點44' 的左值設定為 '節點4' 的左值加1
2、將 '節點44' 的右值設定為 '節點4' 的左值加2

?

123	`INSERT` `INTO` `comment` `SELECT` `44, left_num + 1, left_num + 2FROM` `comment` `WHERE` `comment_id = 4;`

驗證

?

-- 獲得 '節點4' 孩子SELECT c.*FROM comment AS p, comment AS cWHERE c.left_num BETWEEN p.left_num AND p.right_num AND p.comment_id = 4;+------------+----------+-----------+| comment_id | left_num | right_num |+------------+----------+-----------+| 4 | 6 | 15 || 5 | 9 | 10 || 6 | 11 | 14 || 7 | 12 | 13 || 44 | 7 | 8 |+------------+----------+-----------+-- 獲得 '節點44' 父親SELECT p.* FROM comment AS p, comment AS cWHERE c.left_num BETWEEN p.left_num AND p.right_num AND c.comment_id = 44;+------------+----------+-----------+| comment_id | left_num | right_num |+------------+----------+-----------+| 1 | 1 | 16 || 4 | 6 | 15 || 44 | 7 | 8 |+------------+----------+-----------+

1.4. 總結

這種樹結構一般會用在查詢多增加修改少的場景中（比如地區表，類別表之類的）。
在現實中其實還有些表的資料欄位很多，並且具有層級關係。但是他們層級關係並不需要實時的那麼準確(最終能達到資料資料一直就行)，這是我們會將這種層級關係的欄位和主表分開放在另外一個表。這樣為了加快更新。如果實時更新影響到了效能，這是我們會考慮使用kafka(我們還沒有發現效能很差)。

MySQL多層級結構-樹搜尋介紹

相關文章