Mysql8 递归查询

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;

3.实测