递归获取数据库中树节点的所有子节点

背景：

 在项目中遇到了这样的一个需求，需要复制某个树节点A下的所有子节点，因而需要得到A下的所有子节点，网上搜罗了一圈，大部分都是用的存储过程，因为不允许使用存储过程，最终还是找到了一篇可用的博客

建表：

CREATE TABLE `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `parent_id` int(11) DEFAULT NULL COMMENT '父节点id',
  `menu_name` varchar(128) DEFAULT NULL COMMENT '菜单名称',
  `menu_url` varchar(128) DEFAULT '' COMMENT '菜单路径',
  `status` tinyint(3) DEFAULT '1' COMMENT '菜单状态 1-有效；0-无效',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12212 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO `menu` VALUES ('0', null, '菜单0', ' ', '1');
INSERT INTO `menu` VALUES ('1', '0', '菜单1', '', '1');
INSERT INTO `menu` VALUES ('11', '1', '菜单11', '', '1');
INSERT INTO `menu` VALUES ('12', '1', '菜单12', '', '1');
INSERT INTO `menu` VALUES ('13', '1', '菜单13', '', '1');
INSERT INTO `menu` VALUES ('111', '11', '菜单111', '', '1');
INSERT INTO `menu` VALUES ('121', '12', '菜单121', '', '1');
INSERT INTO `menu` VALUES ('122', '12', '菜单122', '', '1');
INSERT INTO `menu` VALUES ('1221', '122', '菜单1221', '', '1');
INSERT INTO `menu` VALUES ('1222', '122', '菜单1222', '', '1');
INSERT INTO `menu` VALUES ('12211', '1222', '菜单12211', '', '1');

SELECT
	id,
	parent_id 
FROM
	(
SELECT
	t1.id,
IF
	( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild,
	t1.parent_id 
FROM
	( SELECT id, parent_id FROM menu t WHERE t.STATUS = 1 ORDER BY parent_id, id ) t1,
	( SELECT @pids := 12 ) t2 
	) t3 
WHERE
	ischild != 0

查询完得到的结果：

解析：

if(表达式,1,0) 类似于三目运算符，当表达式为true时，输出为1，表达式为false时，输出为0

再看一下 find_in_set(str,strList)函数，该函数的作用是查询str在list中出现的位置，其中strList的格式为"a,b,c,d"，英文逗号格式分隔(可以参见https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set)，所以我们才会给这个@pids去动态拼接，且使用英文逗号。

最后看一下@pids := concat( @pids, ',', id )，具体解释可以参考(https://blog.csdn.net/qq_37155959/article/details/81005287)

• =
  • 只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=
• :=
  • 不只在set和update时时赋值的作用，在select也是赋值的作用

最终isChild字段会记录出该节点下的所有子节点。如果某条不是其子节点则isChild字段为0。

( SELECT @pids := 要查询的菜单节点id ) t2 它的作用是用来输入我们要查找的目标节点id

本文参考了https://www.cnblogs.com/rainydayfmb/p/8028868.html

和https://blog.csdn.net/qq_37155959/article/details/81005287

感谢二位的分享