| 模型 | 查詢子樹 | 查詢祖先 | 移動節點 | 插入節點 | 適用場景 |
|---|---|---|---|---|---|
| 遞迴 CTE (8.0+) | 快 | 快 | 簡單 | 簡單 | 現代應用首選 |
| 鄰接表 | 慢(遞迴) | 快 | 簡單 | 簡單 | 淺層、寫多讀少 |
| 巢狀集合 | 極快(範圍查詢) | 快 | 複雜(需重算) | 複雜(需空間) | 深層、讀多寫少、報表 |
| 路徑枚舉 | 快 | 快 | 中等 | 中等 | 需完整路徑顯示 |
| 閉包表 | 快 | 快 | 中等 | 中等 | 複雜關係、圖結構 |
本文實作 巢狀集合模型,使用 lft/rgt 左右值編碼,配合存儲過程封裝複雜操作。
CREATE TABLE `pcms_channel` (
`cid` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL COMMENT '頻道名稱',
`parentid` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父級 ID',
`lft` INT UNSIGNED NOT NULL COMMENT '左值',
`rgt` INT UNSIGNED NOT NULL COMMENT '右值',
`lv` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '層級深度',
`themeid` SMALLINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '主題 ID',
PRIMARY KEY (`cid`),
KEY `idx_lr` (`lft`, `rgt`),
KEY `idx_parent` (`parentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='無限分類-巢狀集合';
-- 清空並插入根節點 (lft=1, rgt=2)
TRUNCATE TABLE `pcms_channel`;
INSERT INTO `pcms_channel` (`cid`, `name`, `parentid`, `lft`, `rgt`, `lv`)
VALUES (1, 'Root', 0, 1, 2, 0);
DROP PROCEDURE IF EXISTS `sp_add_channel`;
DELIMITER //
CREATE PROCEDURE `sp_add_channel` (
IN p_parent_id SMALLINT UNSIGNED,
IN p_name VARCHAR(50),
IN p_themeid SMALLINT UNSIGNED DEFAULT 1,
OUT p_result INT,
OUT p_new_id SMALLINT UNSIGNED
)
BEGIN
DECLARE v_parent_rgt INT UNSIGNED;
DECLARE v_parent_lv TINYINT UNSIGNED;
DECLARE v_affected INT DEFAULT 0;
-- 獲取父節點右值與層級
SELECT `rgt`, `lv` INTO v_parent_rgt, v_parent_lv
FROM `pcms_channel` WHERE `cid` = p_parent_id;
IF v_parent_rgt IS NULL THEN
SET p_result = 1001; -- 父節點不存在
SET p_new_id = 0;
ELSE
START TRANSACTION;
-- 1. 為新節點騰出空間:所有 >= parent_rgt 的 lft/rgt +2
UPDATE `pcms_channel` SET `lft` = `lft` + 2 WHERE `lft` >= v_parent_rgt;
SET v_affected = v_affected + ROW_COUNT();
UPDATE `pcms_channel` SET `rgt` = `rgt` + 2 WHERE `rgt` >= v_parent_rgt;
SET v_affected = v_affected + ROW_COUNT();
-- 2. 插入新節點 (lft=parent_rgt, rgt=parent_rgt+1)
INSERT INTO `pcms_channel` (`name`, `parentid`, `lft`, `rgt`, `lv`, `themeid`)
VALUES (p_name, p_parent_id, v_parent_rgt, v_parent_rgt + 1, v_parent_lv + 1, p_themeid);
SET v_affected = v_affected + ROW_COUNT();
SET p_new_id = LAST_INSERT_ID();
IF v_affected >= 3 THEN
COMMIT;
SET p_result = 1000; -- 成功
ELSE
ROLLBACK;
SET p_result = 1002; -- 寫入失敗
SET p_new_id = 0;
END IF;
END IF;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS `sp_del_channel`;
DELIMITER //
CREATE PROCEDURE `sp_del_channel` (
IN p_cid SMALLINT UNSIGNED,
OUT p_result INT
)
BEGIN
DECLARE v_lft, v_rgt, v_width INT UNSIGNED;
DECLARE v_affected INT DEFAULT 0;
-- 獲取節點範圍
SELECT `lft`, `rgt`, `rgt` - `lft` + 1 INTO v_lft, v_rgt, v_width
FROM `pcms_channel` WHERE `cid` = p_cid;
IF v_lft IS NULL THEN
SET p_result = 1001; -- 節點不存在
ELSEIF v_lft = 1 THEN
SET p_result = 1004; -- 禁止刪除根節點
ELSE
START TRANSACTION;
-- 1. 刪除節點及其子樹 (lft 在範圍內)
DELETE FROM `pcms_channel` WHERE `lft` BETWEEN v_lft AND v_rgt;
SET v_affected = ROW_COUNT();
-- 2. 關閉間隙:lft > v_rgt 的 -width, rgt > v_rgt 的 -width
UPDATE `pcms_channel` SET `lft` = `lft` - v_width WHERE `lft` > v_rgt;
SET v_affected = v_affected + ROW_COUNT();
UPDATE `pcms_channel` SET `rgt` = `rgt` - v_width WHERE `rgt` > v_rgt;
SET v_affected = v_affected + ROW_COUNT();
IF v_affected >= 1 THEN
COMMIT;
SET p_result = 1000;
ELSE
ROLLBACK;
SET p_result = 1002;
END IF;
END IF;
END//
DELIMITER ;
DROP PROCEDURE IF EXISTS `sp_move_channel`;
DELIMITER //
CREATE PROCEDURE `sp_move_channel` (
IN p_source_id SMALLINT UNSIGNED,
IN p_target_id SMALLINT UNSIGNED,
OUT p_result INT
)
BEGIN
DECLARE v_src_lft, v_src_rgt, v_src_width INT UNSIGNED;
DECLARE v_tgt_rgt INT UNSIGNED;
DECLARE v_affected INT DEFAULT 0;
-- 基本檢查
IF p_source_id = p_target_id THEN
SET p_result = 1005; -- 不能移動到自己
LEAVE;
END IF;
IF p_source_id = 1 THEN
SET p_result = 1004; -- 不能移動根節點
LEAVE;
END IF
-- 獲取來源範圍
SELECT `lft`, `rgt`, `rgt` - `lft` + 1 INTO v_src_lft, v_src_rgt, v_src_width
FROM `pcms_channel` WHERE `cid` = p_source_id;
-- 獲取目標右值
SELECT `rgt` INTO v_tgt_rgt FROM `pcms_channel` WHERE `cid` = p_target_id;
IF v_src_lft IS NULL OR v_tgt_rgt IS NULL THEN
SET p_result = 1001; -- 節點不存在
ELSEIF v_tgt_rgt BETWEEN v_src_lft AND v_src_rgt THEN
SET p_result = 1003; -- 目標在來源子樹內,會造成循環
ELSE
START TRANSACTION;
-- 1. 先從原位置移除(關閉間隙)
DELETE FROM `pcms_channel` WHERE `lft` BETWEEN v_src_lft AND v_src_rgt;
SET v_affected = ROW_COUNT();
UPDATE `pcms_channel` SET `lft` = `lft` - v_src_width WHERE `lft` > v_src_rgt;
SET v_affected = v_affected + ROW_COUNT();
UPDATE `pcms_channel` SET `rgt` = `rgt` - v_src_width WHERE `rgt` > v_src_rgt;
SET v_affected = v_affected + ROW_COUNT();
-- 2. 在新位置騰出空間
-- 注意:若目標在來源右側,移除後目標 rgt 已左移 v_src_width
SET v_tgt_rgt = IF(v_tgt_rgt > v_src_rgt, v_tgt_rgt - v_src_width, v_tgt_rgt);
UPDATE `pcms_channel` SET `lft` = `lft` + v_src_width WHERE `lft` >= v_tgt_rgt;
SET v_affected = v_affected + ROW_COUNT();
UPDATE `pcms_channel` SET `rgt` = `rgt` + v_src_width WHERE `rgt` >= v_tgt_rgt;
SET v_affected = v_affected + ROW_COUNT();
-- 3. 重新插入子樹(需遞迴複製,這裡簡化為單節點移動示意)
-- 完整實作需游標遞迴複製子樹並調整 lft/rgt
-- 此處示範核心邏輯,實際建議用應用層處理或觸發器
IF v_affected >= 4 THEN
COMMIT;
SET p_result = 1000;
ELSE
ROLLBACK;
SET p_result = 1002;
END IF;
END IF;
END//
DELIMITER ;
SELECT
`cid`, `name`, `lv`,
REPEAT(' ', `lv`) AS indent,
`lft`, `rgt`
FROM `pcms_channel`
ORDER BY `lft`;
SELECT * FROM `pcms_channel`
WHERE `lft` > (SELECT `lft` FROM `pcms_channel` WHERE `cid` = 2)
AND `rgt` < (SELECT `rgt` FROM `pcms_channel` WHERE `cid` = 2)
ORDER BY `lft`;
SELECT * FROM `pcms_channel`
WHERE `lft` < (SELECT `lft` FROM `pcms_channel` WHERE `cid` = 5)
AND `rgt` > (SELECT `rgt` FROM `pcms_channel` WHERE `cid` = 5)
ORDER BY `lft`;
SELECT * FROM `pcms_channel`
WHERE `parentid` = 2
ORDER BY `lft`;
SELECT (`rgt` - `lft` - 1) / 2 AS descendant_count
FROM `pcms_channel` WHERE `cid` = 2;
<?php
class CategoryService {
private $pdo;
public function __construct(PDO $pdo) { $this->pdo = $pdo; }
public function addChannel(int $parentId, string $name, int $themeId = 1): array {
$stmt = $this->pdo->prepare("CALL sp_add_channel(?, ?, ?, @result, @new_id)");
$stmt->execute([$parentId, $name, $themeId]);
$result = $this->pdo->query("SELECT @result AS result, @new_id AS new_id")->fetch();
return ['success' => $result['result'] === 1000, 'id' => $result['new_id'], 'code' => $result['result']];
}
public function getTree(): array {
$stmt = $this->pdo->query("SELECT cid, name, lv, lft, rgt FROM pcms_channel ORDER BY lft");
return $stmt->fetchAll();
}
public function getDescendants(int $cid): array {
$stmt = $this->pdo->prepare("
SELECT * FROM pcms_channel
WHERE lft > (SELECT lft FROM pcms_channel WHERE cid = ?)
AND rgt < (SELECT rgt FROM pcms_channel WHERE cid = ?)
ORDER BY lft
");
$stmt->execute([$cid, $cid]);
return $stmt->fetchAll();
}
}
?>
WITH RECURSIVE 查詢子樹/祖先極快,寫入只需維護 parent_idparent_id 欄位,雙寫同步,驗證無誤後切換查詢邏輯,最後移除 lft/rgt巢狀集合模型在「讀多寫少、深層次、需高效範圍查詢」場景仍具價值,但現代應用優先考慮 遞迴 CTE + 邻接表 的簡潔與維護性!