gogoWebsite

When I read the FIND_IN_SET function function today, I found that an article by Teacher Zeng using MySQL for recursive hierarchical query is very practical and involves Mysql functions. It is quite interesting, so I tried to write the function. Haha, I have never used a function before, so I laughed at it. Here, I would like to thank Teacher Zeng, haha, and every IT person who loves to share!

The table structure is basically the same as that of Teacher Zeng, as follows:

CREATE TABLE `tree` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `pid` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The data is inserted as follows:

INSERT INTO `tree` VALUES ('1', 'A', '0');
INSERT INTO `tree` VALUES ('2', 'B', '1');
INSERT INTO `tree` VALUES ('3', 'C', '1');
INSERT INTO `tree` VALUES ('4', 'D', '2');
INSERT INTO `tree` VALUES ('5', 'E', '2');
INSERT INTO `tree` VALUES ('6', 'F', '3');
INSERT INTO `tree` VALUES ('7', 'G', '6');
INSERT INTO `tree` VALUES ('8', 'H', '0');
INSERT INTO `tree` VALUES ('9', 'I', '8');
INSERT INTO `tree` VALUES ('10', 'J', '8');
INSERT INTO `tree` VALUES ('11', 'K', '8');
INSERT INTO `tree` VALUES ('12', 'L', '9');
INSERT INTO `tree` VALUES ('13', 'M', '9');
INSERT INTO `tree` VALUES ('14', 'N', '12');
INSERT INTO `tree` VALUES ('15', 'O', '12');
INSERT INTO `tree` VALUES ('16', 'P', '15');
INSERT INTO `tree` VALUES ('17', 'Q', '15');

Recursive hierarchical query function:

CREATE DEFINER = `root`@`localhost` FUNCTION `NewProc`(`rId` int)
  RETURNS varchar(500)
 BEGIN
 #Declare two local variables
 DECLARE sTemp VARCHAR(500);
 DECLARE stempChd VARCHAR(500);
 #Initialize local variables
 SET sTemp = '$';
 #Calling cast function to convert int to char
 SET stempChd = CAST(rId AS CHAR);
 #Recursive stitching
 WHILE stempChd is not NULL DO
 #Storing each recursive result
 SET sTemp = CONCAT(sTemp,',',stempChd);
 #Take the parameter as the pid, then query its child id, then use the child id as the pid,
 #Query the child id with the child id as the pid, and loop it in turn until all nodes are leaf nodes
  SELECT GROUP_CONCAT(id) INTO sempChd FROM tree where FIND_IN_SET(pid,stempChd)>0;
 END WHILE;
	 RETURN sTemp;
 END;

This is generated using Navicat for MySQL. BEGIN and END are the real function body. Each complete statement must be ended with a semicolon, otherwise an error will be reported. This function returns a string of all children of the parent node with a given parameter, haha, of course there will be multiple $.

Example of usage:

mysql> select getChildLst(1); 
+-----------------+ 
| getChildLst(1)  | 
+-----------------+ 
| $,1,2,3,4,5,6,7 | 
+-----------------+ 
1 row in set (0.00 sec) 
==========================

mysql> select * from treeNodes 
    -> where FIND_IN_SET(id, getChildLst(1)); 
+----+----------+------+ 
| id | name | pid  | 
+----+----------+------+ 
|  1 | A        |    0 | 
|  2 | B        |    1 | 
|  3 | C        |    1 | 
|  4 | D        |    2 | 
|  5 | E        |    2 | 
|  6 | F        |    3 | 
|  7 | G        |    6 | 
+----+----------+------+ 
7 rows in set (0.01 sec) 

It seems that there are functions in oracle that can directly implement this functionCONNECT BY, I haven't used it, haha. . .