使用mysql 游標(biāo)，快速刪除子節(jié)點(diǎn)及其附屬節(jié)點(diǎn)

上篇，我寫了如何使用html展示數(shù)據(jù)庫中存儲(chǔ)的樹形結(jié)構(gòu)， 本篇我將說一下如何通過存儲(chǔ)過程，快速刪除一個(gè)樹節(jié)點(diǎn)及其附屬節(jié)點(diǎn)。網(wǎng)上已經(jīng)有非常多的關(guān)于mysql 游標(biāo)使用的文章了， 為什么我還要寫這篇文章呢，我主要是想表達(dá)一些在軟件設(shè)計(jì)領(lǐng)域的一些想法。

一直不太贊同在數(shù)據(jù)庫中大量的使用存儲(chǔ)過程， 因?yàn)殡S著軟件系統(tǒng)的復(fù)雜度逐漸提高，維護(hù)的難度會(huì)逐漸的增大。而且大批量的使用存儲(chǔ)過程，不利于后期通過分布式部署應(yīng)用程序來解決日益增多的業(yè)務(wù)需求。

我推薦把存儲(chǔ)過程僅用在處理一些不包含太多業(yè)務(wù)，批量操作數(shù)據(jù)的場景中，因?yàn)檫@樣可以避免應(yīng)用程序頻繁的訪問數(shù)據(jù)庫。刪除一個(gè)樹的子節(jié)點(diǎn)正好符合這一場景。

我們知道刪除一個(gè)樹節(jié)點(diǎn)的同時(shí)，需要?jiǎng)h除其子節(jié)點(diǎn)或葉子節(jié)點(diǎn)。通過使用mysql的游標(biāo)，我們可以遍歷某一節(jié)點(diǎn)的所有子節(jié)點(diǎn)，通過使用遞歸，我們可以跨越多層直至葉子節(jié)點(diǎn)。

要實(shí)現(xiàn)刪除一個(gè)樹節(jié)點(diǎn)， 一個(gè)存儲(chǔ)過程肯定可以搞定，但是為了讓程序邏輯更簡單些， 我寫了兩個(gè)存儲(chǔ)過程，一個(gè)用來獲取存儲(chǔ)過程所有符合條件的節(jié)點(diǎn)，另一個(gè)用來執(zhí)行刪除操作。

遍歷節(jié)點(diǎn)，找出符合節(jié)點(diǎn)及其所有的子節(jié)點(diǎn)的實(shí)現(xiàn)是這樣的。

/* 創(chuàng)建一個(gè)獲取樹節(jié)點(diǎn)及其子節(jié)點(diǎn)的函數(shù)，并以 節(jié)點(diǎn)id1, 節(jié)點(diǎn)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;

  # 這一句非常重要, 他可以保證存儲(chǔ)過程至少會(huì)返回一個(gè)根節(jié)點(diǎn)id，調(diào)用 CONCAT_WS 函數(shù)時(shí)就不會(huì)出錯(cuò)了
  SELECT id INTO result FROM treenodes WHERE id= node_id;

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

END;

刪除樹節(jié)點(diǎn)的存儲(chǔ)過程是這個(gè)樣子的

/* 刪除一個(gè)節(jié)點(diǎn)及其附屬節(jié)點(diǎn) */
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 這個(gè)msyql 參數(shù) 和 FIND_IN_SET 函數(shù)。

max_sp_recursion_depth 控制可以執(zhí)行遞歸的層數(shù)，

FIND_IN_SET 函數(shù)的作用，會(huì)將@result 先變?yōu)橐粋€(gè)字符串列表，再查找復(fù)合條件的值。這里若用 IN 關(guān)鍵字的話，將得不到任何結(jié)果