MySQL8-WITH RECURSIVE递归查询父子集
开发过程中遇到查询所有父子集,不同数据库中实现方式也不同,本文使用
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;