mysql 递归获取内容

表结构

CREATE TABLE `sys_permission` (
  `fn_code` varchar(50) NOT NULL COMMENT '功能权限id 功能权限编码',
  `fn_name` varchar(100) DEFAULT NULL COMMENT '功能权限名',
  `parent_id` varchar(11) DEFAULT NULL COMMENT '上级功能权限'
  PRIMARY KEY (`fn_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='权限表';

子求父

5.7版本

<foreach collection="permissionList" separator=" union " item="fnCode" index="i">
    SELECT t2.fn_code, t2.`fn_name`,t2.fn_order
    FROM
    (
    SELECT
    @r${i} AS _id,
    (SELECT @r${i} := parent_id FROM sys_permission WHERE fn_code = _id) AS parent_id,
    @l${i} := @l${i} + 1 AS lvl
    FROM
    (SELECT @r${i} := #{fnCode}, @l${i} := 0) vars, sys_permission AS h
    WHERE @r${i} != 0
    ) t1
    JOIN sys_permission t2
    ON t1._id = t2.fn_code
</foreach>
 
原始
SELECT
	t2.fn_code,
	t2.`fn_name`,
	t2.fn_order 
FROM
	(
	SELECT
		@r AS _id,
		( SELECT @r := parent_id FROM sys_permission WHERE fn_code = _id ) AS parent_id,
		@l := @l + 1 AS lvl 
	FROM
		( SELECT @r := '010101', @l := 0 ) vars,
		sys_permission AS h 
	WHERE
		@r <> 0 
	) t1
	JOIN sys_permission t2 ON t1._id = t2.fn_code

8.0版本

WITH recursive temp AS (
        SELECT
            *
        FROM
        sys_permission p
        WHERE
        p.fn_code in
        <foreach collection="permissionList" open="(" close=")" item="fnCode" separator=",">
            #{fnCode}
        </foreach>
UNION ALL
        SELECT
          *
        FROM
        sys_permission p,
        temp t
        WHERE
        t.parent_id = p.fn_code
)
SELECT
      *
FROM
   temp 

父获子

8.0版本

  WITH recursive temp AS (
        SELECT
            *
        FROM
        sys_permission p
        WHERE
        p.fn_code in
        <foreach collection="permissionList" open="(" close=")" item="fnCode" separator=",">
            #{fnCode}
        </foreach>
UNION ALL
        SELECT
          *
        FROM
        sys_permission p,
        temp t
        WHERE
        t.fn_code = p.parent_id
)
SELECT
      *
FROM
   temp 

5.7

根据一个父节点查询所有子节点（包含自身）
SELECT
	au.fn_code 
FROM
	( SELECT * FROM sys_permission WHERE parent_id IS NOT NULL ) au,
	( SELECT @pid := '01' ) pd 
WHERE
	FIND_IN_SET( parent_id, @pid ) > 0 
	AND @pid := concat( @pid, ',', fn_code ) UNION
SELECT
	fn_code 
FROM
	sys_permission 
WHERE
	fn_code = '01';
根据多个父节点查询所有子节点（包含自身） 
SELECT
	au.fn_code 
FROM
	( SELECT * FROM sys_permission WHERE parent_id IS NOT NULL ) au,
	( SELECT @pid := '01,02' ) pd 
WHERE
	FIND_IN_SET( parent_id, @pid ) > 0 
	AND @pid := concat( @pid, ',', fn_code ) UNION
SELECT
	fn_code 
FROM
	sys_permission 
WHERE
	FIND_IN_SET( fn_code, @pid ) > 0;