mysql递归查询

/*
 Navicat Premium Data Transfer

 Source Server         : local
 Source Server Type    : MySQL
 Source Server Version : 80021
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80021
 File Encoding         : 65001

 Date: 02/11/2020 11:55:44
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for menu
-- ----------------------------
DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '菜单id',
  `parent_id` int(0) NULL DEFAULT NULL COMMENT '父节点id',
  `menu_name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单名称',
  `menu_url` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '菜单路径',
  `status` tinyint(0) NULL DEFAULT 1 COMMENT '菜单状态 1-有效；0-无效',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12212 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of menu
-- ----------------------------
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);
INSERT INTO `menu` VALUES (12212, NULL, '菜单0', ' ', 1);

SET FOREIGN_KEY_CHECKS = 1;

 

select id from (

              select t1.id,

              if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', t1.id), -1) as ischild

              from (

                   select id,parent_id from menu t where 1=1 order by parent_id, id

                  ) t1,

                  (select @pids :=0 id) t2

             ) t3 where ischild != -1

 (select @pids := 要查询的菜单节点 id) t2