在进行数据库移植的过程中,遇到了对树进行递归查询的问题。
在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()这个函数。