使用mysql 游標，快速刪除子節點及其附屬節點

上篇，我寫了如何使用html展示數據庫中存儲的樹形結構， 本篇我將說一下如何通過存儲過程，快速刪除一個樹節點及其附屬節點。網上已經有非常多的關于mysql 游標使用的文章了， 為什么我還要寫這篇文章呢，我主要是想表達一些在軟件設計領域的一些想法。

一直不太贊同在數據庫中大量的使用存儲過程， 因為隨著軟件系統的復雜度逐漸提高，維護的難度會逐漸的增大。而且大批量的使用存儲過程，不利于后期通過分布式部署應用程序來解決日益增多的業務需求。

我推薦把存儲過程僅用在處理一些不包含太多業務，批量操作數據的場景中，因為這樣可以避免應用程序頻繁的訪問數據庫。刪除一個樹的子節點正好符合這一場景。

我們知道刪除一個樹節點的同時，需要刪除其子節點或葉子節點。通過使用mysql的游標，我們可以遍歷某一節點的所有子節點，通過使用遞歸，我們可以跨越多層直至葉子節點。

要實現刪除一個樹節點， 一個存儲過程肯定可以搞定，但是為了讓程序邏輯更簡單些， 我寫了兩個存儲過程，一個用來獲取存儲過程所有符合條件的節點，另一個用來執行刪除操作。

遍歷節點，找出符合節點及其所有的子節點的實現是這樣的。

/* 創建一個獲取樹節點及其子節點的函數，并以 節點id1, 節點id2 的形式返回 */
DROP PROCEDURE IF EXISTS get_tree_node;
CREATE  PROCEDURE get_tree_node(IN node_id INT, OUT result VARCHAR(2000))
BEGIN      
    DECLARE nodeid INT(50);  
  DECLARE done INT DEFAULT 0;  
     DECLARE cur CURSOR FOR SELECT id FROM treenodes WHERE pid = node_id;
    DECLARE CONTINUE HANDLER FOR NOT found SET done = TRUE;

  # 這一句非常重要, 他可以保證存儲過程至少會返回一個根節點id，調用 CONCAT_WS 函數時就不會出錯了
  SELECT id INTO result FROM treenodes WHERE id= node_id;

  OPEN cur;
  REPEAT
            FETCH cur INTO nodeid;                     
      # 避免多一條記錄
      IF done <> 1 THEN                
        # 調用遞歸函數獲取節點下的子節點
        CALL get_tree_node(nodeid, @temp);            
        # 合并父節點和子節點的id
                SELECT CONCAT_WS(",", result, @temp) INTO result;
            END IF;
    UNTIL done END REPEAT;  
  # 關閉光標
    CLOSE cur;

END;

刪除樹節點的存儲過程是這個樣子的

/* 刪除一個節點及其附屬節點 */
DROP PROCEDURE IF EXISTS delete_tree_node;
CREATE PROCEDURE delete_tree_node(IN node_id int)
BEGIN
    SET  max_sp_recursion_depth = 10;
    CALL get_tree_node(node_id, @result);
    DELETE FROM treenodes WHERE FIND_IN_SET(id, @result);
    DELETE FROM books WHERE FIND_IN_SET(id, @result);
END

這里注意 max_sp_recursion_depth 這個msyql 參數 和 FIND_IN_SET 函數。

max_sp_recursion_depth 控制可以執行遞歸的層數，

FIND_IN_SET 函數的作用，會將@result 先變為一個字符串列表，再查找復合條件的值。這里若用 IN 關鍵字的話，將得不到任何結果