Mysql8递归查询树状菜单

目录

数据表准备

​编辑

递归事例

根据父亲查儿子

根据儿子查父亲

---

数据表准备

DROP TABLE IF EXISTS `kind_tree_menu`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `kind_tree_menu` (
  `id` int NOT NULL AUTO_INCREMENT,
  `menu_name` varchar(50) DEFAULT NULL COMMENT '当前菜单名字',
  `parent_id` int DEFAULT NULL COMMENT '父菜单的id',
  `menu_path` varchar(100) DEFAULT NULL COMMENT '菜单资源路径',
  `status` int DEFAULT NULL COMMENT '状态1为启用0为禁用',
  `is_delete` int DEFAULT NULL,
  `order_by` int DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='分类树状菜单';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `kind_tree_menu`
--

LOCK TABLES `kind_tree_menu` WRITE;
/*!40000 ALTER TABLE `kind_tree_menu` DISABLE KEYS */;
INSERT INTO `kind_tree_menu` VALUES (1,'前端',0,'1',1,0,1),(2,'js框架',1,'21',1,0,1),(3,'vue',2,'132',1,0,2),(4,'reacte',2,'243re',1,0,4),(5,'UI框架',1,'BootStrap',1,0,5),(6,'后端',0,'后端',1,0,1),(7,'java',6,'JVAAa',1,0,3),(8,'运维',0,'运维',1,0,NULL),(9,'网络安全',0,'网络安全',1,0,NULL),(10,'移动开发',0,'移动开发',1,0,NULL),(11,'嵌入式',0,'嵌入式',1,0,NULL),(12,'人工智能',0,'人工智能',1,0,NULL),(13,'算法',0,'算法',1,0,NULL);
/*!40000 ALTER TABLE `kind_tree_menu` ENABLE KEYS */;
UNLOCK TABLES;

递归事例

根据父亲查儿子

with recursive t1 as(
select a.* from kind_tree_menu a where id=1
union all
select t2.* from kind_tree_menu t2 inner join t1 on t1.id = t2.parent_id
)
select * from t1
order by t1.parent_id

根据儿子查父亲

with recursive t1 as(
select a.* from kind_tree_menu a where id=4
union all
select t2.* from kind_tree_menu t2 inner join t1 on t1.parent_id = t2.id
)
select * from t1
order by t1.parent_id