MySQL uses user-defined functions to recursively query the parent ID or child ID

Time:2020-8-3

Background:

In mysql, if it is a limited level, for example, if we can determine the maximum depth of the tree in advance, then the depth of the tree with all nodes as roots will not exceed the maximum depth of the tree, then we can directly implement it through left join.

But many times we can’t control or know the depth of the tree. At this time, you need to use stored procedures (functions) in MySQL or recursion in the program. This paper discusses the method of using function in MySQL

1、 Environmental preparation

1. Table building


CREATE TABLE `table_name` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `status` int(255) NULL DEFAULT NULL,
 `pid` int(11) NULL DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2. Insert data


INSERT INTO `table_name` VALUES (1, 12, 0);
INSERT INTO `table_name` VALUES (2, 4, 1);
INSERT INTO `table_name` VALUES (3, 8, 2);
INSERT INTO `table_name` VALUES (4, 16, 3);
INSERT INTO `table_name` VALUES (5, 32, 3);
INSERT INTO `table_name` VALUES (6, 64, 3);
INSERT INTO `table_name` VALUES (7, 128, 6);
INSERT INTO `table_name` VALUES (8, 256, 7);
INSERT INTO `table_name` VALUES (9, 512, 8);
INSERT INTO `table_name` VALUES (10, 1024, 9);
INSERT INTO `table_name` VALUES (11, 2048, 10);

2、 The writing of MySQL function

1. Query all the parent nodes of the current node


delimiter // 
CREATE FUNCTION `getParentList`(root_id BIGINT) 
   RETURNS VARCHAR(1000) 
   BEGIN 
     DECLARE k INT DEFAULT 0;
     DECLARE fid INT DEFAULT 1;
     DECLARE str VARCHAR(1000) DEFAULT '$';
     WHILE rootId > 0 DO
       SET fid=(SELECT pid FROM table_name WHERE root_id=id); 
       IF fid > 0 THEN
         SET str = concat(str,',',fid);  
         SET root_id = fid; 
       ELSE 
         SET root_id=fid; 
       END IF; 
   END WHILE;
   RETURN str;
 END //
 delimiter ;

2. Query all child nodes of the current node


delimiter //
CREATE FUNCTION `getChildList`(root_id BIGINT) 
  RETURNS VARCHAR(1000) 
  BEGIN 
   DECLARE str VARCHAR(1000) ; 
   DECLARE cid VARCHAR(1000) ; 
   DECLARE k INT DEFAULT 0;
   SET str = '$'; 
   SET cid = CAST(root_id AS CHAR);12    WHILE cid IS NOT NULL DO 
        IF k > 0 THEN
         SET str = CONCAT(str,',',cid);
        END IF;
        SELECT GROUP_CONCAT(id) INTO cid FROM table_name WHERE FIND_IN_SET(pid,cid)>0;
        SET k = k + 1;
   END WHILE; 
   RETURN str; 
END // 
delimiter ;

3、 Testing

1. Gets all the parents of the current node


SELECT getParentList(10);

2. Gets all bytes of the current node


SELECT getChildList(3);

summary

The above is the introduction of Mysql to you, through the user-defined function to achieve recursive query of parent ID or child ID, I hope to help you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the developeppaer website!
If you think this article is helpful to you, welcome to reprint, please indicate the source, thank you!

Recommended Today

[Study Notes] Line segment tree optimization mapping

Open the pit first, and then talk about it when you have time. example CF786B Legacy Code //Line segment tree optimization mapping, from winter vacation to summer vacation //I thought it was rare before, but now it’s quite understandable #include #include #include #include using namespace std; const int MAXN = 2e6 + 10; int n, […]