Quickly delete child nodes and their subordinate nodes using a MySQL cursor

In the previous article, I wrote how to use data storage, and through HTML to show the structure of the tree, this article I will tell how to delete a tree node and its subordinate nodes through the stored procedure. There have been a lot of articles on the use of MySQL cursors on the Internet, why do I have to write this article, mainly to express some ideas in the field of software design.

I do not agree with the extensive use of stored procedures in the database, because I think as the complexity of the software system gradually improved, the difficulty of maintenance will gradually increase. And the large-scale use of stored procedures is not conducive to the late adoption of distributed deployment of applications to address the increasing demand for business.

I recommend that you use stored procedures in scenarios where you are handling data in batches that do not contain too much business, because it can reduce the amount of data that is submitted to the database or the frequent database operations. Deleting a child node of a tree fits this scenario exactly.

We know that when a tree node is deleted from the business, its child nodes or leaf nodes need to be deleted. By using a MySQL cursor, we can traverse all the child nodes of a node, and by using recursion we can cross multiple layers up to the leaf node.

To implement the deletion of a tree node, a stored procedure can certainly be done, but in order to make the program logic simpler, I wrote two stored procedures, one to get all the eligible nodes of the stored procedure and the other to perform the delete operation. The entire stored entry is the node ID of the target stored procedure.

Traverse the node to find the implementation that conforms to the node and all of its child nodes.

/*  creates a function that gets the tree node and its child nodes, and returns it as a   node id1,  node id2   */drop procedure if exists  get_tree_node; Create  procedure get_tree_node (In node_id int, out result varchar ( ) Begin          declare nodeid int (; )    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;  #  This sentence is very important,  he can guarantee that the stored procedure will return at least one root node ID, call  CONCAT_WS  function, there is no error   SELECT id INTO result FROM treenodes WHERE id=  node_id;  open cur;  repeat             fetch cur into nodeid;                            #  Avoid one more record       IF done <> 1 THEN                          #  call the recursive function to get the child nodes under the node         call get_tree_node ( nodeid,  @temp);                     #  merging id           of parent and child nodes       select concat_ws (",", result,  @temp)  INTO result;             END IF;     until done end repeat;    #  Close Cursor     CLOSE cur; END;

The stored procedure for deleting a tree node looks like this.

/* Delete a node and its affiliated nodes */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

Quickly delete child nodes and their subordinate nodes using a MySQL cursor