萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 存儲過程實現無限分類:巢狀集合模型完整實作

無限分類模型比較

模型查詢子樹查詢祖先移動節點插入節點適用場景
遞迴 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);

核心存儲過程

1. 新增子節點

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 ;

2. 刪除節點(含子樹)

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 ;

3. 移動節點(含子樹)

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 服務層封裝

<?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();
    }
}
?>

⚠️ 重要限制與遷移建議

  • 寫入效能:移動/插入需更新大量行的 lft/rgt,大樹寫入極慢
  • 並發控制:必須序列化寫入操作(表鎖或應用層鎖)
  • MySQL 8.0+ 建議改用遞迴 CTE:WITH RECURSIVE 查詢子樹/祖先極快,寫入只需維護 parent_id
  • 遷移策略:新增 parent_id 欄位,雙寫同步,驗證無誤後切換查詢邏輯,最後移除 lft/rgt

巢狀集合模型在「讀多寫少、深層次、需高效範圍查詢」場景仍具價值,但現代應用優先考慮 遞迴 CTE + 邻接表 的簡潔與維護性!

copyright © 萬盛學電腦網 all rights reserved