MySQL8新特性实现无限层级依赖SQL查询

前言

看IT老齐视频，学到了一招MySQL8的新特性，特此记录一下，大家可以去看原视频：
【IT老齐173】学到就是赚到，利用MySQL8新特性实现无限层级依赖SQL查询

准备

MySQL至少需要8以上的版本哦！

1.创建表

CREATE TABLE `city` (
  `id` bigint NOT NULL,
  `p_id` bigint DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `demo`.`city` (`id`, `p_id`, `name`) VALUES (1, 0, '四川省');
INSERT INTO `demo`.`city` (`id`, `p_id`, `name`) VALUES (2, 0, '黑龙江省');
INSERT INTO `demo`.`city` (`id`, `p_id`, `name`) VALUES (3, 1, '广元市');
INSERT INTO `demo`.`city` (`id`, `p_id`, `name`) VALUES (4, 3, '剑阁县');
INSERT INTO `demo`.`city` (`id`, `p_id`, `name`) VALUES (5, 4, '普安镇');
INSERT INTO `demo`.`city` (`id`, `p_id`, `name`) VALUES (6, 5, '田家乡');
INSERT INTO `demo`.`city` (`id`, `p_id`, `name`) VALUES (7, 6, '石庆村二组');
INSERT INTO `demo`.`city` (`id`, `p_id`, `name`) VALUES (8, 2, '哈尔滨市');
INSERT INTO `demo`.`city` (`id`, `p_id`, `name`) VALUES (9, 8, '江北大学城');

2.执行SQL

WITH recursive r as (
	SELECT id,p_id,name from city c WHERE id = 1
UNION ALL
	SELECT c.id,c.p_id,CONCAT(r.name,'>',c.name) as name FROM city c,r WHERE r.id = c.p_id
)
SELECT id,p_id,name from r WHERE r.id = 7;

3.执行结果