Mysql8以上反对递归查问了
1.结构表
DROP TABLE IF EXISTS `t_area`;CREATE TABLE `t_area` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(64) NOT NULL DEFAULT 0 COMMENT '名称', `p_id` int(11) NOT NULL DEFAULT 0 COMMENT '父id', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='test';insert into t_area (id,name, p_id) VALUES (1,'中国',0),(2,'广西省',1),(3,'广东省',1),(4,'广州市',3),(5,'深圳市',3),(6,'白云区',4),(7,'越秀区',4),(8,'福田区',5),(9,'南山区',5);2.递归查问sql
with recursive t as( select * from t_area where id=3 union all select c.* from t_area c join t on c.p_id = t.id)select *from t;