mysql uses stored procedures to implement tree node acquisition method

As shown in the figure:

Table Data

For such a tree, how to get all the nodes under "Gao Yinrui" (one SQL statement is definitely not enough)

Write via stored procedure

DELIMITER //
CREATE FUNCTION `getChildLst`(rootId INT)
 
RETURNS varchar(1000) READS SQL DATA
 
BEGIN
 DECLARE sTemp VARCHAR(1000);
 
 DECLARE sTempChd VARCHAR(1000);
 
 SET sTemp = '$';
 
 SET sTempChd =cast(rootId as CHAR);
 
 WHILE sTempChd is not null DO
 
 SET sTemp = concat(sTemp,',',sTempChd);
 
 SELECT group_concat(id) INTO sTempChd FROM document_file_name where FIND_IN_SET(pId,sTempChd)>0;
 
 END WHILE;
 
 RETURN sTemp;
 
END //

Create the above stored procedure

select * from document_file_name where find_in_set(id, getChildLst(1)); 

Summarize

This is the end of this article about how to use stored procedures in MySQL to get tree nodes. For more information about how to get tree nodes in MySQL stored procedures, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!