mysql 递归查询

第一种方法

-- 貌似跟安全有关系,要创建函数必须将
-- log_bin_trust_function_creators 设置为ON,默认是OFF,表示不信任函数创建者.

-- 查看这个函数的值:
show variables like '%func%';

-- 设置为ON
set global log_bin_trust_function_creators=1;

-- 自定义函数

-- 向下递归查询
DELIMITER $$
CREATE  FUNCTION `getByDown`(id INT) RETURNS varchar(6000) CHARSET utf8
BEGIN
	DECLARE sTemp VARCHAR(6000);
	DECLARE sTempChd VARCHAR(6000);

	SET sTemp='$';
	SET sTempChd = CAST(id AS CHAR);
	
	WHILE sTempChd IS NOT NULL DO
		SET sTemp= CONCAT(sTemp,',',sTempChd);
		SELECT GROUP_CONCAT(id) INTO sTempChd FROM sys_permission 
		WHERE FIND_IN_SET(parent_id,sTempChd)>0;
	END WHILE;
	
	RETURN sTemp;
END$$
DELIMITER ;


-- 向上递归查询
DELIMITER $$
CREATE  FUNCTION `getpermsByUp`(id INT) RETURNS varchar(4000) CHARSET utf8
BEGIN
	DECLARE sTemp VARCHAR(4000);
	DECLARE sTempChd VARCHAR(4000);

	SET sTemp='$';
	SET sTempChd = CAST(id AS CHAR);
	SET sTemp = CONCAT(sTemp,',',sTempChd);

	SELECT parent_id INTO sTempChd FROM sys_permission WHERE id = sTempChd;
	
	WHILE sTempChd <> 0 DO
		SET sTemp = CONCAT(sTemp,',',sTempChd);
		SELECT parent_id INTO sTempChd FROM sys_permission WHERE id = sTempChd;
	END WHILE;
	
	RETURN sTemp;
END$$
DELIMITER ;

第二种方法

通过 with as语法来实现递归（WITH RECURSIVE AS）

--  父 求 子, 即向下递归
WITH RECURSIVE temp AS ( -- 将结果表命名为temp
    SELECT * FROM sys_resource r WHERE r.resource_id =1200  -- 查询出父记录
    UNION ALL
    -- 这时要注意，获取的是上面sql对应的直接子记录
	-- 注意where后的条件，我们使用temp表中的唯一一条记录的id关联sys_resource 表中的parent_id
	-- 仅当temp第一条记录匹配不到resource表中的pid时才会对temp的第二条记录id进行匹配
    SELECT r.* FROM sys_resource r,temp t WHERE t.resource_id = r.parent_id
)select * from temp

-- 子 求 父, 即向上递归
WITH RECURSIVE temp AS (
	SELECT * FROM sys_resource r WHERE r.resource_id =1210  -- 查询子记录
	UNION ALL
	SELECT r.* FROM sys_resource r,temp t WHERE t.parent_id = r.resource_id --  获取直接父记录
)select * from temp