CREATE DEFINER=`root`@`%` FUNCTION `getChildList`(rootId VARCHAR (255)) RETURNS mediumtext CHARSET utf8mb4
BEGIN
       DECLARE sTemp MEDIUMTEXT ;
       DECLARE sTempChd MEDIUMTEXT ;
    
       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 树表    where FIND_IN_SET(pid,sTempChd)>0;
       END WHILE;
       RETURN sTemp;
     END

这里注意mysql要设置group_concat长度默认是1024
配置文件修改如下

[mysqld]

　　group_concat_max_len=102400

如果想传多个id可以用临时表和上面的函数继续封装

CREATE DEFINER=`root`@`%` FUNCTION `getChildListByTableNameAndIds`(tableName VARCHAR ( 32 )) RETURNS mediumtext CHARSET utf8mb4
BEGIN
	DECLARE rootId VARCHAR(255);
	DECLARE sTemp MEDIUMTEXT;
	DECLARE count int DEFAULT 0 ;
	DECLARE i int DEFAULT 0 ;
	DECLARE cur CURSOR FOR SELECT id1 FROM temp;

	SELECT count(*) into count FROM (SELECT id1 FROM temp) t1;
	set sTemp = '$';
	
	OPEN cur;
			WHILE i<count DO
			fetch cur into rootId;
				SET sTemp = concat( sTemp, ',', getChildListByTableName(rootId,tableName) );
				 SET i=i+1;
			END WHILE;	 
	close cur;
	RETURN sTemp;
END

temp 为MySQL的临时会话表
这里一个sql 只能用一张临时表所以 java 创建时查询几次创建几张 可以用 标记为创建多张 控制 创建如下

CREATE  TEMPORARY  TABLE IF NOT EXISTS `temp` (
                      `ID1` varchar(200) NOT NULL,
                      `ID2` varchar(200) DEFAULT NULL,
                      `ID3` varchar(200) DEFAULT NULL,
                      KEY `IDX_TEMP_ID1` (`ID1`) USING BTREE,
                      KEY `IDX_TEMP_ID2` (`ID2`) USING BTREE,
                      KEY `IDX_TEMP_ID3` (`ID3`) USING BTREE
                    );

java创建多张如下

   String createSql = "CREATE  TEMPORARY  TABLE IF NOT EXISTS `temp" + postFix + "` (\n" +
                    "  `ID1` varchar(200) NOT NULL,\n" +
                    "  `ID2` varchar(200) DEFAULT NULL,\n" +
                    "  `ID3` varchar(200) DEFAULT NULL,\n" +
                    "  KEY `IDX_TEMP_ID1` (`ID1`) USING BTREE,\n" +
                    "  KEY `IDX_TEMP_ID2` (`ID2`) USING BTREE,\n" +
                    "  KEY `IDX_TEMP_ID3` (`ID3`) USING BTREE\n" +
                    ")";
            jdbcTemplate.execute(createSql);
            jdbcTemplate.execute("delete from temp" + postFix);
            String sql = "insert into temp" + postFix + " (id1) values (?)";
            jdbcTemplate.batchUpdate(sql, data);