首頁 > 軟體

MySQL遞迴查詢的3種實現方式範例

2022-04-15 16:00:45

1.建表指令碼

1.1.建表

DROP TABLE IF EXISTS `sys_region`;
CREATE TABLE `sys_region`  (
  `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '地區主鍵編號',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地區名稱',
  `short_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '簡稱',
  `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '行政地區編號',
  `parent_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父id',
  `level` int(2) NULL DEFAULT NULL COMMENT '1級：省、直轄市、自治區rn2級：地級市rn3級：市轄區、縣（旗）、縣級市、自治縣（自治旗）、特區、林區rn4級：鎮、鄉、民族鄉、縣轄區、街道rn5級：村、居委會',
  `flag` int(1) NULL DEFAULT NULL COMMENT '0:正常 1廢棄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 182 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '地區表' ROW_FORMAT = Dynamic;

1.2.插入資料

INSERT INTO `sys_region` VALUES (1, '山東省', '魯', '370000000000', NULL, 1, 0);
INSERT INTO `sys_region` VALUES (2, '濟南市', '濟南', '370100000000', '370000000000', 2, 0);
INSERT INTO `sys_region` VALUES (3, '市轄區', '市轄區', '370101000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (4, '歷下區', '歷下區', '370102000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (5, '市中區', '市中區', '370103000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (6, '槐蔭區', '槐蔭區', '370104000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (7, '天橋區', '天橋區', '370105000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (8, '歷城區', '歷城區', '370112000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (9, '長清區', '長清區', '370113000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (10, '章丘區', '章丘區', '370114000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (11, '濟陽區', '濟陽區', '370115000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (12, '萊蕪區', '萊蕪區', '370116000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (13, '鋼城區', '鋼城區', '370117000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (14, '平陰縣', '平陰縣', '370124000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (15, '商河縣', '商河縣', '370126000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (16, '濟南高新技術產業開發區', '高新區', '370171000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (17, '解放路街道', '解放路街道', '370102001000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (18, '千佛山街道', '千佛山街道', '370102002000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (19, '趵突泉街道', '趵突泉街道', '370102003000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (20, '泉城路街道', '泉城路街道', '370102004000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (21, '大明湖街道', '大明湖街道', '370102005000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (22, '東關街道', '東關街道', '370102006000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (23, '文東街道', '文東街道', '370102007000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (24, '建新街道', '建新街道', '370102008000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (25, '甸柳街道', '甸柳街道', '370102009000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (26, '燕山街道', '燕山街道', '370102010000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (27, '姚家街道', '姚家街道', '370102011000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (28, '龍洞街道', '龍洞街道', '370102012000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (29, '智遠街道', '智遠街道', '370102013000', '370102000000', 4, 0);
INSERT INTO `sys_region` VALUES (30, '大觀園街道', '大觀園街道', '370103002000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (31, '杆石橋街道', '杆石橋街道', '370103003000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (32, '四里村街道', '四里村街道', '370103004000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (33, '魏家莊街道', '魏家莊街道', '370103006000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (34, '二七街道', '二七街道', '370103008000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (35, '七裡山街道', '七裡山街道', '370103009000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (36, '六裡山街道', '六裡山街道', '370103010000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (37, '舜玉路街道', '舜玉路街道', '370103012000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (38, '濼源街道', '濼源街道', '370103014000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (39, '王官莊街道', '王官莊街道', '370103015000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (40, '舜耕街道', '舜耕街道', '370103016000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (41, '白馬山街道', '白馬山街道', '370103017000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (42, '七賢街道', '七賢街道', '370103018000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (43, '十六裡河街道', '十六裡河街道', '370103019000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (44, '興隆街道', '興隆街道', '370103020000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (45, '黨家街道', '黨家街道', '370103021000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (46, '陡溝街道', '陡溝街道', '370103022000', '370103000000', 4, 0);
INSERT INTO `sys_region` VALUES (47, '振興街街道', '振興街街道', '370104001000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (48, '中大槐樹街道', '中大槐樹街道', '370104002000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (49, '道德街街道', '道德街街道', '370104003000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (50, '西市場街道', '西市場街道', '370104004000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (51, '五里溝街道', '五里溝街道', '370104005000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (52, '營市街街道', '營市街街道', '370104006000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (53, '青年公園街道', '青年公園街道', '370104007000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (54, '南辛莊街道', '南辛莊街道', '370104008000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (55, '段店北路街道', '段店北路街道', '370104009000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (56, '張莊路街道', '張莊路街道', '370104010000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (57, '匡山街道', '匡山街道', '370104011000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (58, '美里湖街道', '美里湖街道', '370104012000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (59, '臘山街道', '臘山街道', '370104013000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (60, '興福街道', '興福街道', '370104014000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (61, '玉清湖街道', '玉清湖街道', '370104015000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (62, '吳家堡街道', '吳家堡街道', '370104016000', '370104000000', 4, 0);
INSERT INTO `sys_region` VALUES (63, '無影山街道', '無影山街道', '370105001000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (64, '天橋東街街道', '天橋東街街道', '370105003000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (65, '北村街道', '北村街道', '370105004000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (66, '南村街道', '南村街道', '370105005000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (67, '堤口路街道', '堤口路街道', '370105006000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (68, '北坦街道', '北坦街道', '370105007000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (69, '制錦市街道', '制錦市街道', '370105009000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (70, '寶華街道', '寶華街道', '370105010000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (71, '官紮營街道', '官紮營街道', '370105011000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (72, '緯北路街道', '緯北路街道', '370105012000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (73, '藥山街道', '藥山街道', '370105013000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (74, '北園街道', '北園街道', '370105014000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (75, '濼口街道', '濼口街道', '370105015000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (76, '桑梓店街道', '桑梓店街道', '370105016000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (77, '大橋街道', '大橋街道', '370105017000', '370105000000', 4, 0);
INSERT INTO `sys_region` VALUES (78, '山大路街道', '山大路街道', '370112001000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (79, '洪家樓街道', '洪家樓街道', '370112002000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (80, '東風街道', '東風街道', '370112003000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (81, '全福街道', '全福街道', '370112004000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (82, '華山街道', '華山街道', '370112007000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (83, '荷花路街道', '荷花路街道', '370112008000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (84, '王舍人街道', '王舍人街道', '370112009000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (85, '鮑山街道', '鮑山街道', '370112010000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (86, '郭店街道', '郭店街道', '370112011000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (87, '唐冶街道', '唐冶街道', '370112012000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (88, '港溝街道', '港溝街道', '370112013000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (89, '董家街道', '董家街道', '370112016000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (90, '彩石街道', '彩石街道', '370112017000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (91, '仲宮街道', '仲宮街道', '370112018000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (92, '柳埠街道', '柳埠街道', '370112019000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (93, '唐王街道', '唐王街道', '370112020000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (94, '西營街道', '西營街道', '370112021000', '370112000000', 4, 0);
INSERT INTO `sys_region` VALUES (95, '文昌街道', '文昌街道', '370113001000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (96, '崮雲湖街道', '崮雲湖街道', '370113002000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (97, '平安街道', '平安街道', '370113003000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (98, '五峰山街道', '五峰山街道', '370113004000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (99, '歸德街道', '歸德街道', '370113005000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (100, '張夏街道', '張夏街道', '370113006000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (101, '萬德街道', '萬德街道', '370113007000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (102, '孝裡鎮', '孝裡鎮', '370113102000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (103, '馬山鎮', '馬山鎮', '370113107000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (104, '雙泉鎮', '雙泉鎮', '370113108000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (105, '明水街道', '明水街道', '370114001000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (106, '雙山街道', '雙山街道', '370114002000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (107, '棗園街道', '棗園街道', '370114003000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (108, '龍山街道', '龍山街道', '370114004000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (109, '埠村街道', '埠村街道', '370114005000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (110, '聖井街道', '聖井街道', '370114006000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (111, '普集街道', '普集街道', '370114007000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (112, '繡惠街道', '繡惠街道', '370114008000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (113, '相公莊街道', '相公莊街道', '370114009000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (114, '文祖街道', '文祖街道', '370114010000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (115, '官莊街道', '官莊街道', '370114011000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (116, '高官寨街道', '高官寨街道', '370114012000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (117, '白雲湖街道', '白雲湖街道', '370114013000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (118, '寧家埠街道', '寧家埠街道', '370114014000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (119, '曹範街道', '曹範街道', '370114015000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (120, '刁鎮', '刁鎮', '370114100000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (121, '垛莊鎮', '垛莊鎮', '370114101000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (122, '黃河鎮', '黃河鎮', '370114102000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (123, '濟陽街道', '濟陽街道', '370115001000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (124, '濟北街道', '濟北街道', '370115002000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (125, '崔寨街道', '崔寨街道', '370115003000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (126, '孫耿街道', '孫耿街道', '370115004000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (127, '回河街道', '回河街道', '370115005000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (128, '太平街道', '太平街道', '370115006000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (129, '垛石鎮', '垛石鎮', '370115101000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (130, '曲堤鎮', '曲堤鎮', '370115103000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (131, '仁風鎮', '仁風鎮', '370115104000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (132, '新市鎮', '新市鎮', '370115110000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (133, '鳳城街道', '鳳城街道', '370116001000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (134, '張家窪街道', '張家窪街道', '370116002000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (135, '高莊街道', '高莊街道', '370116003000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (136, '鵬泉街道', '鵬泉街道', '370116004000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (137, '口鎮', '口鎮', '370116100000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (138, '羊裡鎮', '羊裡鎮', '370116101000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (139, '方下鎮', '方下鎮', '370116102000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (140, '牛泉鎮', '牛泉鎮', '370116103000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (141, '苗山鎮', '苗山鎮', '370116104000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (142, '雪野鎮', '雪野鎮', '370116105000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (143, '大王莊鎮', '大王莊鎮', '370116106000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (144, '寨裡鎮', '寨裡鎮', '370116107000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (145, '楊莊鎮', '楊莊鎮', '370116108000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (146, '茶業口鎮', '茶業口鎮', '370116109000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (147, '和莊鎮', '和莊鎮', '370116110000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (148, '艾山街道', '艾山街道', '370117001000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (149, '裡辛街道', '裡辛街道', '370117002000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (150, '汶源街道', '汶源街道', '370117003000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (151, '顏莊鎮', '顏莊鎮', '370117100000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (152, '辛莊鎮', '辛莊鎮', '370117103000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (153, '棋山國家森林公園', '棋山國家森林公園', '370117400000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (154, '高新技術開發區', '高新技術開發區', '370117401000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (155, '榆山街道', '榆山街道', '370124001000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (156, '錦水街道', '錦水街道', '370124002000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (157, '東阿鎮', '東阿鎮', '370124102000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (158, '孝直鎮', '孝直鎮', '370124103000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (159, '孔村鎮', '孔村鎮', '370124104000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (160, '洪範池鎮', '洪範池鎮', '370124105000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (161, '玫瑰鎮', '玫瑰鎮', '370124106000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (162, '安城鎮', '安城鎮', '370124107000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (163, '許商街道', '許商街道', '370126001000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (164, '殷巷鎮', '殷巷鎮', '370126101000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (165, '懷仁鎮', '懷仁鎮', '370126102000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (166, '龍桑寺鎮', '龍桑寺鎮', '370126104000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (167, '鄭路鎮', '鄭路鎮', '370126105000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (168, '賈莊鎮', '賈莊鎮', '370126106000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (169, '玉皇廟鎮', '玉皇廟鎮', '370126107000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (170, '白橋鎮', '白橋鎮', '370126108000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (171, '孫集鎮', '孫集鎮', '370126109000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (172, '韓廟鎮', '韓廟鎮', '370126110000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (173, '沙河鎮', '沙河鎮', '370126111000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (174, '張坊鎮', '張坊鎮', '370126112000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (175, '舜華路街道', '舜華路街道', '370171001000', '370171000000', 4, 0);
INSERT INTO `sys_region` VALUES (176, '孫村街道', '孫村街道', '370171002000', '370171000000', 4, 0);
INSERT INTO `sys_region` VALUES (177, '鉅野河街道', '鉅野河街道', '370171003000', '370171000000', 4, 0);
INSERT INTO `sys_region` VALUES (178, '遙牆街道', '遙牆街道', '370171004000', '370171000000', 4, 0);
INSERT INTO `sys_region` VALUES (179, '臨港街道', '臨港街道', '370171005000', '370171000000', 4, 0);
INSERT INTO `sys_region` VALUES (180, '創新谷街道辦事處', '創新谷街道辦事處', '370171400000', '370171000000', 4, 0);
INSERT INTO `sys_region` VALUES (181, '章錦街道', '章錦街道', '370171401000', '370171000000', 4, 0);

2.遞迴查詢三種實現方式

2.1. 方式一 建立自定義函數實現遞迴查詢

注意：

1.建立函數的時候，可能會報錯。This function has none of DETERMINISTIC 在MySQL安裝根目錄個人組態檔my.ini中新增一行：log_bin_trust_function_creators=1    然後重啟MySQL服務。

2.建立函數的時候用到了系統函數GROUP_CONCAT()，該函數預設長度是1024，在組態檔my.ini 中修改預設長度，新增一行語句：group_concat_max_len=102400    儲存後重啟MySQL服務。查詢sql SELECT @@global.group_concat_max_len;

3.這種方式實現之後，呼叫簡單，但是效率較慢，而且由於欄位 、函數、 長度的的限制，資料量大的時候可能查詢不全。適合小資料量下使用。

2.1.1. 查詢子節點的函數 查詢時  包含自身

建立函數： 

-- 查詢子節點的函數  查詢時包含自身
CREATE DEFINER=`root`@`localhost` FUNCTION `queryChildren_1_sys_region`(areaId VARCHAR(15)) RETURNS varchar(20000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(20000);
DECLARE sTempChd VARCHAR(20000);
 
SET sTemp='$';
SET sTempChd = areaId;
 
WHILE sTempChd IS NOT NULL DO
 
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(code) INTO sTempChd FROM sys_region WHERE FIND_IN_SET(parent_code,sTempChd)>0;
 
END WHILE;
RETURN sTemp;
END

查詢範例：

-- 子節點 含自己
SELECT * from sys_region where FIND_IN_SET(code,queryChildren_1_sys_region('370000000000'));

 2.1.2. 查詢子節點的函數 查詢時  不包含自身

建立函數： 

-- 查詢子節點的函數 查詢時  不包含自身
CREATE DEFINER=`root`@`localhost` FUNCTION `queryChildren_sys_region`(areaId VARCHAR(15)) RETURNS varchar(21840) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(21840);
DECLARE sTempChd VARCHAR(21840);
 
SET sTemp='$';
SET sTempChd = areaId;
 
WHILE sTempChd IS NOT NULL DO
 
if sTempChd != areaId then 
	SET sTemp= CONCAT(sTemp,',',sTempChd);
end if;
						
SELECT GROUP_CONCAT(code) INTO sTempChd FROM sys_region WHERE FIND_IN_SET(parent_code,sTempChd)>0;
 
END WHILE;
RETURN sTemp;
END

查詢範例：

SELECT * from  sys_region where FIND_IN_SET(code,queryChildren_sys_region('370000000000'));

 2.1.3.查詢父節點  查詢的時候  包含自身

建立函數： 

-- 查詢父節點  查詢的時候 包含自身
 
CREATE DEFINER=`root`@`localhost` FUNCTION `queryParent_1_sys_region`(areaId VARCHAR(15)) RETURNS varchar(21840) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(21840);
DECLARE sTempChd VARCHAR(21840);
 
SET sTemp='$';
SET sTempChd = areaId;
 
-- SET sTemp = CONCAT(sTemp,',',sTempChd);
-- SELECT IFNULL(parent_code,'') INTO sTempChd FROM tgyi.sys_region  WHERE code = sTempChd;
 
WHILE (sTempChd <> '' ) DO
 
SET sTemp = CONCAT(sTemp,',',sTempChd);
select ifnull((SELECT parent_code FROM tgyi.sys_region  WHERE code = sTempChd),'')  INTO sTempChd ;
 
END WHILE;
 
RETURN sTemp;
END

查詢範例：

SELECT * from sys_region where FIND_IN_SET(code,queryParent_1_sys_region('370171401000'));

 2.1.4.查詢父節點  查詢的時候  不包含自身

建立函數： 

-- 查詢父節點   查詢的時候  不包含自身
CREATE DEFINER=`root`@`localhost` FUNCTION `queryParent_sys_region`(areaId VARCHAR(15)) RETURNS varchar(21840) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(21840);
DECLARE sTempChd VARCHAR(21840);
 
SET sTemp='$';
SET sTempChd = areaId;
 
-- SET sTemp = CONCAT(sTemp,',',sTempChd);
-- SELECT IFNULL(parent_code,'') INTO sTempChd FROM tgyi.sys_region  WHERE code = sTempChd;
 
WHILE (sTempChd <> '' ) DO
 
if sTempChd != areaId then 
	SET sTemp = CONCAT(sTemp,',',sTempChd);
end if;
 
	
select ifnull((SELECT parent_code FROM tgyi.sys_region  WHERE code = sTempChd),'')  INTO sTempChd ;
 
END WHILE;
 
RETURN sTemp;
END

查詢範例：

SELECT * from sys_region where FIND_IN_SET(code,queryParent_sys_region('370171401000'));

 2.2. 方式二  單純使用sql  不建立函數  實現遞迴

注意：

寫法比較複雜，但是適合MySQL各版本，比較靈活。

2.2.1.查詢子節點  含自己

查詢SQL指令碼及效果截圖：

 
-- 單純使用SQL遞迴 查詢子節點  含自己
SELECT T2.level_, T3.* 
  FROM( 
	SELECT @codes as _ids, 
			( SELECT @codes := GROUP_CONCAT(code) 
				FROM sys_region 
			   WHERE FIND_IN_SET(parent_code, @codes) 
			) as T1, 
		   @l := @l+1 as level_ 
	  FROM sys_region, 
		  (SELECT @codes :='370000000000', @l := 0 ) T4
	 WHERE @codes IS NOT NULL 
	   ) T2, sys_region T3 
  WHERE FIND_IN_SET(T3.code, T2._ids) 
  ORDER BY level_, code
	;

2.2.2.查詢子節點  不含自己

查詢SQL指令碼及效果截圖：

-- 單純使用SQL遞迴 查詢子節點  不含自己
SELECT T2.level_, T3.* 
  FROM( 
	SELECT @codes as _ids, 
			( SELECT @codes := GROUP_CONCAT(code) 
				FROM sys_region 
			   WHERE FIND_IN_SET(parent_code, @codes) 
			) as T1, 
		   @l := @l+1 as level_ 
	  FROM sys_region, 
		  (SELECT @codes :='370000000000', @l := -1 ) T4 
	 WHERE @codes IS NOT NULL 
	   ) T2, sys_region T3 
  WHERE FIND_IN_SET(T3.code, T2._ids) 
	  and code !='370000000000'
  ORDER BY level_, code
	;

2.2.3.查詢父節點  含自己

查詢SQL指令碼及效果截圖：

-- 單純使用SQL遞迴 查詢父節點  含自己
SELECT T2.level_, T3.* 
  FROM( 
	SELECT @code as _code, 
			( SELECT @code := parent_code 
				FROM sys_region 
			   WHERE code = @code 
			) as T1, 
		   @l := @l+1 as level_ 
	  FROM sys_region, 
		  (SELECT @code := '370171401000', @l := 0 ) T4 
	 WHERE @code is not null
	) T2, sys_region T3
 WHERE T2._code = T3.code 
 ORDER BY level_
 ;

2.2.4.查詢父節點  不含自己

查詢SQL指令碼及效果截圖：

 -- 單純使用SQL遞迴 查詢父節點  不含自己
SELECT T2.level_, T3.* 
  FROM( 
	SELECT @code as _code, 
			( SELECT @code := parent_code 
				FROM sys_region 
			   WHERE code = @code 
			) as T1, 
		   @l := @l+1 as level_ 
	  FROM sys_region, 
		  (SELECT @code := '370171401000', @l := -1 ) T4 
	 WHERE @code is not null
	) T2, sys_region T3
 WHERE T2._code = T3.code 
   and T2._code != '370171401000' 
 ORDER BY level_
 ;

 2.3. 方式三   MySQL 8.0 版本以上 使用 WITH RECURSIVE 實現遞迴 注意：

寫法比較簡單，也比較靈活，但是隻適用於MySQL8.0及以上版本，這種寫法其實和  PostgreSQL 的寫法是一樣的。

2.3.1.查詢子節點  含自己

查詢SQL指令碼及效果截圖：

 -- 查詢子節點  含自己
 WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS
(
  SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
	  from sys_region T1
	 where T1.code='370000000000'
  UNION ALL
	
  SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag 
    from sys_region T2, recursion T3
	 WHERE T2.parent_code=T3.code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag  
  FROM recursion T
	;

2.3.2.查詢子節點  不含自己

查詢SQL指令碼及效果截圖：

 -- 查詢子節點   不含自己
 WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS
(
  SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
	  from sys_region T1
	 where T1.code='370000000000'
  UNION ALL
	
  SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag 
    from sys_region T2, recursion T3
	 WHERE T2.parent_code=T3.code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag  
  FROM recursion T
 where T.code!='370000000000'
;

2.3.3.查詢父節點  含自己

查詢SQL指令碼及效果截圖：

 -- 查詢父節點  含自己
 WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS
(
  SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
	  from sys_region T1
	 where T1.code='370171401000'
  UNION ALL
	
  SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag 
    from sys_region T2, recursion T3
	 WHERE T2.code=T3.parent_code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag  
  FROM recursion T
	;

2.3.4.查詢父節點  不含自己

查詢SQL指令碼及效果截圖：

 WITH RECURSIVE recursion (id, name, short_name, code, parent_code, level, flag) AS
(
  SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag  
	  from sys_region T1
	 where T1.code='370171401000'
  UNION ALL
	
  SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag 
    from sys_region T2, recursion T3
	 WHERE T2.code=T3.parent_code
)
SELECT T.id, T.name, T.short_name, T.code, T.parent_code, T.level, T.flag  
  FROM recursion T
	where T.code!='370171401000'
	;

總結

到此這篇關於MySQL遞迴查詢的3種實現方式的文章就介紹到這了,更多相關MySQL遞迴查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！


相關文章

  • 德漢姆中學就讀條件 德漢姆中學就讀條件

    <em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011

    2021-06-01 09:32:01

  • 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列

    综合看Anker超能充系列的性价比很高，并且与不仅和iPhone12/苹果<em>Mac</em>Book很配，而且适合多设备充电需求的日常使用或差旅场景，不管是安卓还是Switch同样也能用得上它，希望这次分享能给准备购入充电器的小伙伴们有所

    2021-06-01 09:31:42

  • 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！ 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！

    除了L4WUDU与吴亦凡已经多次共事，成为了明面上的厂牌成员，吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节，这也是20XXCLUB首次全员合照，王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让

    2021-06-01 09:31:34

  • IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？ IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？

    目前应用IPFS的机构：1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆，历史资料永久保存在 IPFS 6 加

    2021-06-01 09:31:24

  • 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊

    开拓者的车机是兼容苹果和<em>安卓</em>，虽然我不怎么用，但确实兼顾了我家人的很多需求：副驾的门板还配有解锁开关，有的时候老婆开车，下车的时候偶尔会忘记解锁，我在副驾驶可以自己开门：第二排设计很好，不仅配置了一个很大的

    2021-06-01 09:30:48

  • iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？ iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？

    不仅是<em>安卓</em>手机，苹果手机的降价力度也是前所未有了，iPhone12也“跳水价”了，发布价是6799元，如今已经跌至5308元，降价幅度超过1400元，最新定价确认了。iPhone12是苹果首款5G手机，同时也是全球首款5nm芯片的智能机，它

    2021-06-01 09:30:45

IT145.com E-mail:sddin#qq.com