数据库迁移，oracle至mysql递归的解决方案

由于mysql不支持oracle的递归方案（start with ......connect by prior......）

mysql改造方案，使用存储过程，以叶子递归为例：

CREATE DEFINER=`cpc`@`%` PROCEDURE `spg_GetChild`(IN `p_id` varchar(100),IN `p_col_name` varchar(100),IN `p_col_p_name` varchar(100),IN `p_t_name` varchar(100),IN `sColumn` varchar(1000))
BEGIN
  /**
   递归查找从属项，输入参数分别为具体的父项ID，查找的ID列名，父项的ID列名，和表名，需要查询的列名集合。
  **/
  DECLARE sTemp BLOB(100000);
  DECLARE sTempChd BLOB(30000);
  declare v_sql BLOB(10000);    -- 需要执行的SQL语句
  DECLARE doSql BLOB(100000); 
  SET sTemp = '$';
  SET sTempChd =cast(p_id as CHAR);
  set group_concat_max_len = 30000;
  set v_sql= concat('SELECT group_concat(',p_col_name,' ) INTO @sTempChd FROM ',p_t_name,' where FIND_IN_SET(',p_col_p_name,',@sTempChd)>0');
  set @v_sql=v_sql;   
       WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SET @sTempChd=sTempChd;
         prepare stmt from @v_sql;  -- 预处理需要执行的动态SQL
         EXECUTE stmt;  -- 执行SQL语句
         deallocate prepare stmt;     -- 释放掉预处理段
         SET sTempChd=@sTempChd; 
      END WHILE;
   set doSql = CONCAT('select ',sColumn,' from ',p_t_name,' where FIND_IN_SET(',p_col_name,',"',sTemp,'")');
   set @doSql = doSql;
   prepare dtmt from @doSql;
   EXECUTE dtmt; 
END

关键处在于动态sql和@用户变量的灵活使用

v_sql构建为，select 主键id into 用户变量@sTempChd from 表 where 父id 在@sTempChd上。

不断调用该v_sql，直到查不出主键id，则完成了递归过程。

然后得到sTemp(递归后的id集合)查找该集合则完成了递归的过程。--执行dosql

进一步设想，可以再加个限制条件在dosql中，则类似于oracle的where 限制条件，如例：

CREATE DEFINER=`cpc`@`%` PROCEDURE `spg_GetChild_Limit`(IN `p_id` varchar(100),IN `p_col_name` varchar(100),IN `p_col_p_name` varchar(100),IN `p_t_name` varchar(100),IN `sColumn` varchar(1000),IN `limit_sql` varchar(100))
BEGIN
  /**
   递归查找从属项，输入参数分别为具体的父项ID，查找的ID列名，父项的ID列名，和表名，需要查询的列名集合。
  **/
  DECLARE sTemp BLOB(100000);
  DECLARE sTempChd BLOB(30000);
  declare v_sql BLOB(10000);    -- 需要执行的SQL语句
  DECLARE doSql BLOB(100000); 
  SET sTemp = '$';
  SET sTempChd =cast(p_id as CHAR);
  set group_concat_max_len = 30000;
  set v_sql= concat('SELECT group_concat(',p_col_name,' ) INTO @sTempChd FROM ',p_t_name,' where FIND_IN_SET(',p_col_p_name,',@sTempChd)>0');


  set @v_sql=v_sql;   
  
       WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SET @sTempChd=sTempChd;
         prepare stmt from @v_sql;  -- 预处理需要执行的动态SQL
         EXECUTE stmt;  -- 执行SQL语句
         deallocate prepare stmt;     -- 释放掉预处理段
         SET sTempChd=@sTempChd;
              
      END WHILE;
   set doSql = CONCAT('select ',sColumn,' from ',p_t_name,' where FIND_IN_SET(',p_col_name,',"',sTemp,'") and ',limit_sql);
   set @doSql = doSql;
   prepare dtmt from @doSql;
   EXECUTE dtmt; 
END

再进一步设想，可以输出id的变量集合到out参数中，则可以分离存储过程，真正实现了oracle的递归功能，如：

CREATE DEFINER=`cpc`@`%` PROCEDURE `spg_GetChild_Limit_Out`(IN p_id varchar(100), IN p_col_name varchar(100), IN p_col_p_name varchar(100), IN p_t_name varchar(100), IN sColumn varchar(1000), IN limit_sql varchar(100), OUT out_string BLOB(100000))
BEGIN
  /**
   递归查找从属项，输入参数分别为具体的父项ID，查找的ID列名，父项的ID列名，和表名，需要查询的列名集合。
  **/
  DECLARE sTemp BLOB(100000);
  DECLARE sTempChd BLOB(30000);
  declare v_sql BLOB(10000);    -- 需要执行的SQL语句
  DECLARE doSql BLOB(100000); 
  SET sTemp = '$';
  SET sTempChd =cast(p_id as CHAR);
  set group_concat_max_len = 30000;
  set v_sql= concat('SELECT group_concat(',p_col_name,' ) INTO @sTempChd FROM ',p_t_name,' where FIND_IN_SET(',p_col_p_name,',@sTempChd)>0');


  set @v_sql=v_sql;   
  
       WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SET @sTempChd=sTempChd;
         prepare stmt from @v_sql;  -- 预处理需要执行的动态SQL
         EXECUTE stmt;  -- 执行SQL语句
         deallocate prepare stmt;     -- 释放掉预处理段
         SET sTempChd=@sTempChd;
              
      END WHILE;
   set doSql = CONCAT('select group_concat(',sColumn,') into @out_string from ',p_t_name,' where FIND_IN_SET(',p_col_name,',"',sTemp,'") and ',limit_sql);
   set @doSql = doSql;
   prepare dtmt from @doSql;
   EXECUTE dtmt;
set out_string = @out_string;
END

得到的out_string 参数则为递归后的id集合，我们对id集合进行操作即可，如例：

call spg_GetChild_Limit_Out(#{org_id},'org_id','parent_org_id','organization','org_id','1=1',@a);--递归后的主键id集合记录到@a变量中

select

staff.* from staff inner join organization on staff.org_id = organization.org_id where FIND_IN_SET (organization.org_id,@a);--使用@a变量做限制

set @a = null --复位@a变量