mysql使用函数递归查找父级或子级id

CREATE FUNCTION `getParentList`(rootId bigint)
  RETURNS varchar(1000)
  BEGIN
    DECLARE fid varchar(100) default '';
    DECLARE str varchar(1000) default rootId;
    WHILE rootId is not null do
      SET fid = (SELECT parent_id FROM table_name WHERE id = rootId);
      IF fid is not null AND fid > 0 THEN SET str = concat(str, ',', fid);
        SET rootId = fid;
      ELSE
        SET rootId = fid;
      END IF;
    END WHILE;
  RETURN str;
END

CREATE FUNCTION `getChildList`(rootId bigint) 
  RETURNS VARCHAR(1000) 
  BEGIN
   DECLARE str VARCHAR(1000); 
   DECLARE cid VARCHAR(1000); 
   DECLARE k INT DEFAULT 0;
   SET cid = CAST(rootId AS CHAR);
   SET str = cid; 
   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(parent_id, cid) > 0;
        SET k = k + 1;
   END WHILE; 
   RETURN str; 
END

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `t_id` bigint(20) DEFAULT NULL,
  `t_no` varchar(20) DEFAULT NULL,
  `t_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

CREATE PROCEDURE create_test_data(size bigint)
BEGIN
SET @i=1;
WHILE @i<=size DO
INSERT INTO test (t_id, t_no, t_time) VALUES(concat('100', @i%3), @i%3, now());
SET @i=@i+1;
END WHILE;
END

call create_test_data(10000)