MySQL 根据【父ID】获取【所有子节点】

测试表

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` longtext,
  `pid` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
1
2
3
4
5
6
7

SQL

SELECT * 
FROM (
    SELECT
		t1.*,
        IF(FIND_IN_SET(`pid`, @pids) > 0, @pids := CONCAT(@pids, ',', `id`), 0) AS ancestors
    FROM
        ( SELECT * FROM `dept` AS t WHERE t.`status` = 1 ORDER BY t.`id` ASC ) AS t1,
        ( SELECT @pids := 14 ) AS t2
) AS t3
WHERE
	ancestors != 0
	or `id` = 14;
1
2
3
4
5
6
7
8
9
10
11
12

分析

1. t1：获取所有状态正常的数据并升序排列。
2. t2：初始化@pids变量，它用于获取每条记录时缓存父ID。
3. FIND_IN_SET查询当前记录的父ID在@pids中的位置。找不到就返回0。
   3.1. 找到就将父id拼接到@pids末尾，以便下一条记录继续在@pids中找自己的父id。
4. 每一条记录都基于前一条执行时得到的@pids进行判断。
5. 总结：使用此方案的前提是：
   5.1. 所有结点的【id】必需大于【父id】： id > pid。
   5.2. 所有 id 整体是一个升序状态。
   5.3. 如果5.1无法满足，有其他字段能保证查 t1 时，结点可以按【根>干>叶】进行升序排列也行。
   

用JS生成SQL

var 结果字段 = '*';
var 表名 = '`sys_dept`';
var 主键 = '`id`';
var 父主键 = '`pid`';
var 状态 = '`status`';
var 要查的主键 = 14;
var 包含当前结点 = true;

var sql = `SELECT ${结果字段} 
FROM (
    SELECT
		t1.*,
        IF(FIND_IN_SET(${父主键}, @pids) > 0, @pids := CONCAT(@pids, ',', ${主键}), 0) AS ancestors
    FROM
        ( SELECT * FROM ${表名} AS t WHERE t.${状态} = 1 ORDER BY t.${主键} ASC ) AS t1,
        ( SELECT @pids := ${要查的当前主键} ) AS t2
) AS t3
WHERE
	ancestors != 0
	${包含当前结点 ? `or ${主键} = ${要查的主键}` : ''};`;
console.log(sql);
copy(sql);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

参考资料

笑虾：MySQL - 学习笔记 - SELECT语句

博客园 - 张亮java：https://www.cnblogs.com/zhangliang88/p/15910781.html