前文簡單介紹了巢狀集合的資料模型,以及查詢的方法,傳送門: 樹狀資料結構儲存方式(查詢篇)
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 分類下。
要實現節點的移動,需要三個步驟:
#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
#對於生產使用中,肯定是沒有這麼簡單的表結構設計,或者甚至別的優化,比如一種稱為閉合表的資料模型,這個應該會在本系列文章中介紹給大家。
以上是樹狀資料結構儲存方式(CUD 篇)的詳細內容。更多資訊請關注PHP中文網其他相關文章!