樹狀資料結構儲存方式—— CUD 篇

lxping發表於2019-09-08
資料結構

前文簡單介紹了巢狀集合的資料模型，以及查詢的方法，傳送門: 樹狀資料結構儲存方式——查詢篇

Create

在巢狀集合模型中，每個資料其實就是一個節點 (node)，而每個節點佔用2個位值，比如我們先新增一個 Smartphones 一級節點開始。

INSERT INTO `categories` (`title`, `lft`, `rgt`) VALUES('Smartphones', 1, 2);

Smartphones 作為一個主節點(root)，它的 lft 必定為 1，而 rgt 的值，會隨著其集合內的子元素增加而增加。

現在，我們希望在 Smartphones 內，新增一個子元素 Android。藉助 mysql 的儲存過程。

LOCK TABLE categories WRITE;

SELECT @root_left := lft FROM categories WHERE title = 'Smartphones';

UPDATE categories SET rgt = rgt + 2 WHERE rgt > @root_left;
UPDATE categories SET lft = lft + 2 WHERE lft > @root_left;

INSERT INTO categories (title, lft, rgt) VALUES('Android', @root_left + 1, @root_left + 2);

UNLOCK TABLES;

SELECT `title`, `lft`, `rgt` FROM `categories`;
+-------------+-----+-----+
| title       | lft | rgt |
+-------------+-----+-----+
| Smartphones |   1 |   4 |
| Android     |   2 |   3 |
+-------------+-----+-----+

我們再嘗試往 Android 內新增一個子元素 小米：

LOCK TABLE categories WRITE;

SELECT @root_left := lft FROM categories WHERE title = 'Android';

UPDATE categories SET rgt = rgt + 2 WHERE rgt > @root_left;
UPDATE categories SET lft = lft + 2 WHERE lft > @root_left;

INSERT INTO categories (title, lft, rgt) VALUES('小米', @root_left + 1, @root_left + 2);

UNLOCK TABLES;

SELECT `title`, `lft`, `rgt` FROM `categories`;
+-------------+-----+-----+
| title       | lft | rgt |
+-------------+-----+-----+
| Smartphones |   1 |   6 |
| Android     |   2 |   5 |
| 小米        |   3 |   4 |
+-------------+-----+-----+

這時候，我們再嘗試往 Smartphones 內新增一個子元素 iOS，在前面，我們已經在裡面新增了一個 Android 元素，所以這裡要調整一下儲存過程，將 iOS 插入到 Android 的右邊

LOCK TABLE categories WRITE;

SELECT @next_right := rgt FROM categories WHERE title = 'Android';

UPDATE categories SET rgt = rgt + 2 WHERE rgt > @next_right;
UPDATE categories SET lft = lft + 2 WHERE lft > @next_right;

INSERT INTO categories(title, lft, rgt) VALUES('iOS', @next_right + 1, @next_right + 2);

UNLOCK TABLES;

SELECT `title`, `lft`, `rgt` FROM `categories`;
+-------------+-----+-----+
| title       | lft | rgt |
+-------------+-----+-----+
| Smartphones |   1 |   8 |
| Android     |   2 |   5 |
| 小米        |   3 |   4 |
| iOS         |   6 |   7 |
+-------------+-----+-----+

Delete

刪除節點時，其實可以看做是新增節點的逆過程，我們引入一個寬度，來衡量節點的寬段，其表示為： rgt - lft + 1 所以我們可以這樣寫儲存過程：

LOCK TABLE categories WRITE;

SELECT @delete_left := lft, @delete_right := rgt, @delete_width := rgt - lft + 1
FROM categories WHERE title = 'Android';

DELETE FROM categories WHERE lft BETWEEN @delete_left AND @delete_right;

UPDATE categories SET rgt = rgt - @delete_width WHERE rgt > @delete_right;
UPDATE categories SET lft = lft - @delete_width WHERE lft > @delete_right;

UNLOCK TABLES;

SELECT `title`, `lft`, `rgt` FROM `categories`;
+-------------+-----+-----+
| title       | lft | rgt |
+-------------+-----+-----+
| Smartphones |   1 |   4 |
| iOS         |   2 |   3 |
+-------------+-----+-----+

Update

移動節點，是一個比較複雜的過程，例如下圖，macOS 應該歸類到 Unix 分類下。

樹狀資料結構儲存方式—— CUD 篇

要實現節點的移動，需要三步：

  1. 將要移動的節點摘出來
  2. 重新編排 lft 和 rgt 引數
  3. 將節點移動到指定位置
LOCK TABLE categories WRITE;

-- 將要移動的節點摘出來，並且重新邊篇 lft 和 rgt
SELECT @move_left := lft , @move_right := rgt, @move_width := rgt - lft + 1
FROM categories WHERE title = 'macOS';

UPDATE categories SET rgt = -rgt WHERE lft BETWEEN @move_left AND @move_right;
UPDATE categories SET lft = -lft WHERE lft BETWEEN @move_left AND @move_right;

UPDATE categories SET rgt = rgt - @move_width WHERE rgt > @move_right;
UPDATE categories SET lft = lft - @move_width WHERE lft > @move_right;

-- 將節點放到 Unix 節點裡
SELECT @root_left := lft FROM categories WHERE title = 'Unix';

UPDATE categories SET rgt = rgt + @move_width WHERE rgt > @root_left;
UPDATE categories SET lft = lft + @move_width WHERE lft > @root_left;
-- 
UPDATE categories SET lft = @root_left + 1 WHERE lft BETWEEN -@move_right AND -@move_left;
UPDATE categories SET rgt = @root_left + 2 WHERE rgt BETWEEN -@move_right AND -@move_left;

UNLOCK TABLES;

總結

其實 SQL 中的巢狀集合的資料模型已經提出很久了，也有很多包已經實現了這個功能，比如 laravel-nestedset 或者 django-mptt

對於生產使用中，肯定是沒有這麼簡單的表結構設計，或者甚至別的優化，比如一種稱為閉合表的資料模型，這個應該會在本系列文章中介紹給大家。

筆者比較了不同語言所實現的包，發現 django-mptt 設計會比較優一些，會在下一篇文章中，給各位介紹其優秀的設計。

相關文章