mysql对树进行递归查询

在进行数据库移植的过程中，遇到了对树进行递归查询的问题。

在SQL server 中，很容易实现。如下图：

WITH Tree
AS (SELECT T1.F_EnergyItemCode FROM T_DT_EnergyItemDict AS T1 WHERE T1.F_EnergyItemCode = @EnergyItemCode
UNION ALL
SELECT T2.F_EnergyItemCode FROM T_DT_EnergyItemDict AS T2 INNER JOIN Tree ON T2.F_ParentItemCode = Tree.F_EnergyItemCode)

而mysql 不支持with as。

实现方法：

建立一个存储过程：

CREATE DEFINER=`root`@`localhost` PROCEDURE `spg_GetChildLst`(IN `p_id` varchar(100),IN `p_col_name` varchar(100),IN `p_col_p_name` varchar(100),IN `p_t_name` varchar(100),OUT `p_RetVal` varchar(1000))
BEGIN
  /**
   递归查找从属项，输入参数分别为具体的父项ID，查找的ID列名，父项的ID列名，和表名。输出一个所有ID的字符串。
  **/
    DECLARE sTemp VARCHAR(1000);
  DECLARE sTempChd VARCHAR(1000);
  declare v_sql varchar(500);    -- 需要执行的SQL语句
 
  SET sTemp = '$';
  SET sTempChd =cast(p_id as CHAR);

  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，其中stmt是一个变量
         EXECUTE stmt;  -- 执行SQL语句
         deallocate prepare stmt;     -- 释放掉预处理段
         SET sTempChd=@sTempChd;
         
         -- SELECT group_concat(F_EnergyItemCode) INTO sTempChd FROM T_DT_EnergyItemDict where FIND_IN_SET(F_ParentItemCode,sTempChd)>0;
      END WHILE;
  
    SET p_RetVal=sTemp;

END

为什么要建立存储过程那？因为我们项目很多业务都涉及到树这种从属关系，所以抽象出来，每个表使用递归的时候，直接输入一些字段名和ID和表名即可。

而函数是不支持执行动态SQL的。

然后在实际项目中，调用这个存储过程即可。、

例如：

CALL spg_GetChildLst(@EnergyItemCode,'F_EnergyItemCode','F_ParentItemCode','T_DT_EnergyItemDict',@stmp);
             
            INSERT INTO TmpP01 (F_Id, F_Type) SELECT F_EnergyItemCode, 0 FROM  ( SELECT F_EnergyItemCode FROM T_DT_EnergyItemDict 
      WHERE FIND_IN_SET(F_EnergyItemCode, @stmp)
          ) AS T;

总结：关键点就是mysql动态执行SQL，并且通过@变量名的形式获取返回值。

然后就是FIND_IN_SET（）这个函数。