Shulou(Shulou.com)06/01 Report--

In the previous article, I wrote how to use html to show the tree structure stored in the database. In this article, I will talk about how to quickly delete a tree node and its affiliated nodes through stored procedures. There are already a lot of articles about the use of mysql cursors on the Internet, why do I want to write this article? I mainly want to express some ideas in the field of software design.

Always do not agree with the use of a large number of stored procedures in the database, because with the gradual increase of the complexity of the software system, the difficulty of maintenance will gradually increase. And the mass use of stored procedures is not conducive to the later distributed deployment of applications to solve the growing business requirements.

I recommend using stored procedures only in scenarios that do not contain too much business and manipulate data in batches, because this can prevent applications from frequently accessing the database. Deleting a child node of a tree fits this scenario.

We know that when you delete a tree node, you need to delete its child or leaf node. By using mysql cursors, we can traverse all the children of a node, and by using recursion, we can span multiple layers up to the leaf node.

To delete a tree node, a stored procedure can certainly be done, but to make the program logic easier, I wrote two stored procedures, one to get all the eligible nodes of the stored procedure, and the other to perform the delete operation.

Traverse the node to find out that the implementation of the matching node and all its child nodes is like this.

/ * create a function to get the tree node and its children, and return * / 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) in the form of node id1 and node id2; 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, it can guarantee that the stored procedure will return at least one root node id, and there will be no mistake SELECT id INTO result FROM treenodes WHERE id= node_id; OPEN cur; REPEAT FETCH cur INTO nodeid when calling the CONCAT_WS function # avoid one more record IF done 1 THEN # call a recursive function to get the child node CALL get_tree_node (nodeid, @ temp) under the node; # merge the id SELECT CONCAT_WS (",", result, @ temp) INTO result of the parent and child nodes END IF; UNTIL done END REPEAT; # close the 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

Notice here the msyql parameter max_sp_recursion_depth and the FIND_IN_SET function.

Max_sp_recursion_depth controls the number of layers that can perform recursion

The function of the FIND_IN_SET function changes @ result to a list of strings, and then looks for the value of the compound condition. If you use the IN keyword here, you won't get any results.

Attachment: http://down.51cto.com/data/2366729

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.