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

標籤：mysql   軟體設計   應用程式   文章   資料庫操作   

上篇，我寫的是如何用資料存放區，並通過html展示出樹形的結構， 本篇我將講述，如何通過預存程序，刪除一個樹節點及其附屬節點。網上已經有非常多的關於mysql 遊標使用的文章了， 為什麼我還要寫這篇文章呢，主要是想表達一些在軟體設計領域的一些想法。

我不贊同在資料庫中大量的使用預存程序， 因為我覺得隨著軟體系統的複雜度逐漸提高，維護的難度會逐漸的增大。而且大批量的使用預存程序，不利於後期通過分布式部署應用程式來解決日益增多的業務需求。

我推薦把預存程序用在處理一些不包含太多業務，大量操作資料的情境中，因為這樣會降低批量提交資料到資料庫或者頻繁的跟資料庫操作情況的發生。刪除一個樹的子節點正好符合這一情境。

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

要實現刪除一個樹節點， 一個預存程序肯定可以搞定，但是為了讓程式邏輯更簡單些， 我寫了兩個預存程序，一個用來擷取預存程序所有合格節點，另一個用來執行刪除操作。 整個儲存過成的入口是目標預存程序的節點id。

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

/* 建立一個擷取樹節點及其子節點的函數，並以 節點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

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