开发过程中遇到查询所有父子集,不同数据库中实现方式也不同，本文使用Mysql数据库，版本为8.0

1、构件表

1）创建地址表，字段分别为id、父级id、名称

CREATE TABLE `t_address`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT ID,
  `parent_id` int NOT NULL COMMENT 父级ID,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 名称,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

2）数据初始化

INSERT INTO `t_address` VALUES (1, 0, 陕西省);
INSERT INTO `t_address` VALUES (2, 1, 西安市);
INSERT INTO `t_address` VALUES (3, 1, 咸阳市);
INSERT INTO `t_address` VALUES (4, 3, 秦都区);
INSERT INTO `t_address` VALUES (5, 2, 未央区);
INSERT INTO `t_address` VALUES (6, 2, 雁塔区);
INSERT INTO `t_address` VALUES (7, 2, 莲湖区);
INSERT INTO `t_address` VALUES (8, 6, 大雁塔);
INSERT INTO `t_address` VALUES (9, 6, 科技路);
INSERT INTO `t_address` VALUES (10, 7, 玉祥门);
INSERT INTO `t_address` VALUES (11, 5, 北客站);
INSERT INTO `t_address` VALUES (12, 4, 咸阳湖);

2、查询所有子集（父求子）

1）需求：查询出西安市底下所有区域

with recursive temp as
(	
	select * from t_address where name = 西安市
	union all
	select ta.* from t_address ta join temp on ta.parent_id = temp.id
)
select * from temp

2）结果如下

3、查询所有父集（子求父）

1）需求：查询出北客站所有父级

with recursive temp as
(	
	select * from t_address where `name` = 北客站
	union all
	select ta.* from t_address ta join temp on ta.id = temp.parent_id
)
select * from temp;

2）结果如下