MySQL遞歸查詢的3種實現(xiàn)方式實例

更新時間：2022年04月15日 11:43:33 作者：十&年

在項目中會遇到同一個表中保存著父子關(guān)系的數(shù)據(jù),最常見的就是處理樹形結(jié)構(gòu)資源,下面這篇文章主要給大家介紹了關(guān)于MySQL遞歸查詢的3種實現(xiàn)方式,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下

1.建表腳本

1.1.建表

DROP TABLE IF EXISTS `sys_region`;
CREATE TABLE `sys_region`  (
  `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '地區(qū)主鍵編號',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地區(qū)名稱',
  `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 '行政地區(qū)編號',
  `parent_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父id',
  `level` int(2) NULL DEFAULT NULL COMMENT '1級：省、直轄市、自治區(qū)\r\n2級：地級市\(zhòng)r\n3級：市轄區(qū)、縣（旗）、縣級市、自治縣（自治旗）、特區(qū)、林區(qū)\r\n4級：鎮(zhèn)、鄉(xiāng)、民族鄉(xiāng)、縣轄區(qū)、街道\r\n5級：村、居委會',
  `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 = '地區(qū)表' ROW_FORMAT = Dynamic;

1.2.插入數(shù)據(jù)

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, '市轄區(qū)', '市轄區(qū)', '370101000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (4, '歷下區(qū)', '歷下區(qū)', '370102000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (5, '市中區(qū)', '市中區(qū)', '370103000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (6, '槐蔭區(qū)', '槐蔭區(qū)', '370104000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (7, '天橋區(qū)', '天橋區(qū)', '370105000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (8, '歷城區(qū)', '歷城區(qū)', '370112000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (9, '長清區(qū)', '長清區(qū)', '370113000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (10, '章丘區(qū)', '章丘區(qū)', '370114000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (11, '濟陽區(qū)', '濟陽區(qū)', '370115000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (12, '萊蕪區(qū)', '萊蕪區(qū)', '370116000000', '370100000000', 3, 0);
INSERT INTO `sys_region` VALUES (13, '鋼城區(qū)', '鋼城區(qū)', '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, '濟南高新技術(shù)產(chǎn)業(yè)開發(fā)區(qū)', '高新區(qū)', '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, '東關(guān)街道', '東關(guān)街道', '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, '東風(fēng)街道', '東風(fēng)街道', '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, '孝里鎮(zhèn)', '孝里鎮(zhèn)', '370113102000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (103, '馬山鎮(zhèn)', '馬山鎮(zhèn)', '370113107000', '370113000000', 4, 0);
INSERT INTO `sys_region` VALUES (104, '雙泉鎮(zhèn)', '雙泉鎮(zhèn)', '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, '刁鎮(zhèn)', '刁鎮(zhèn)', '370114100000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (121, '垛莊鎮(zhèn)', '垛莊鎮(zhèn)', '370114101000', '370114000000', 4, 0);
INSERT INTO `sys_region` VALUES (122, '黃河鎮(zhèn)', '黃河鎮(zhèn)', '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, '垛石鎮(zhèn)', '垛石鎮(zhèn)', '370115101000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (130, '曲堤鎮(zhèn)', '曲堤鎮(zhèn)', '370115103000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (131, '仁風(fēng)鎮(zhèn)', '仁風(fēng)鎮(zhèn)', '370115104000', '370115000000', 4, 0);
INSERT INTO `sys_region` VALUES (132, '新市鎮(zhèn)', '新市鎮(zhèn)', '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, '口鎮(zhèn)', '口鎮(zhèn)', '370116100000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (138, '羊里鎮(zhèn)', '羊里鎮(zhèn)', '370116101000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (139, '方下鎮(zhèn)', '方下鎮(zhèn)', '370116102000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (140, '牛泉鎮(zhèn)', '牛泉鎮(zhèn)', '370116103000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (141, '苗山鎮(zhèn)', '苗山鎮(zhèn)', '370116104000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (142, '雪野鎮(zhèn)', '雪野鎮(zhèn)', '370116105000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (143, '大王莊鎮(zhèn)', '大王莊鎮(zhèn)', '370116106000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (144, '寨里鎮(zhèn)', '寨里鎮(zhèn)', '370116107000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (145, '楊莊鎮(zhèn)', '楊莊鎮(zhèn)', '370116108000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (146, '茶業(yè)口鎮(zhèn)', '茶業(yè)口鎮(zhèn)', '370116109000', '370116000000', 4, 0);
INSERT INTO `sys_region` VALUES (147, '和莊鎮(zhèn)', '和莊鎮(zhèn)', '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, '顏莊鎮(zhèn)', '顏莊鎮(zhèn)', '370117100000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (152, '辛莊鎮(zhèn)', '辛莊鎮(zhèn)', '370117103000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (153, '棋山國家森林公園', '棋山國家森林公園', '370117400000', '370117000000', 4, 0);
INSERT INTO `sys_region` VALUES (154, '高新技術(shù)開發(fā)區(qū)', '高新技術(shù)開發(fā)區(qū)', '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, '東阿鎮(zhèn)', '東阿鎮(zhèn)', '370124102000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (158, '孝直鎮(zhèn)', '孝直鎮(zhèn)', '370124103000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (159, '孔村鎮(zhèn)', '孔村鎮(zhèn)', '370124104000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (160, '洪范池鎮(zhèn)', '洪范池鎮(zhèn)', '370124105000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (161, '玫瑰鎮(zhèn)', '玫瑰鎮(zhèn)', '370124106000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (162, '安城鎮(zhèn)', '安城鎮(zhèn)', '370124107000', '370124000000', 4, 0);
INSERT INTO `sys_region` VALUES (163, '許商街道', '許商街道', '370126001000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (164, '殷巷鎮(zhèn)', '殷巷鎮(zhèn)', '370126101000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (165, '懷仁鎮(zhèn)', '懷仁鎮(zhèn)', '370126102000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (166, '龍桑寺鎮(zhèn)', '龍桑寺鎮(zhèn)', '370126104000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (167, '鄭路鎮(zhèn)', '鄭路鎮(zhèn)', '370126105000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (168, '賈莊鎮(zhèn)', '賈莊鎮(zhèn)', '370126106000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (169, '玉皇廟鎮(zhèn)', '玉皇廟鎮(zhèn)', '370126107000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (170, '白橋鎮(zhèn)', '白橋鎮(zhèn)', '370126108000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (171, '孫集鎮(zhèn)', '孫集鎮(zhèn)', '370126109000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (172, '韓廟鎮(zhèn)', '韓廟鎮(zhèn)', '370126110000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (173, '沙河鎮(zhèn)', '沙河鎮(zhèn)', '370126111000', '370126000000', 4, 0);
INSERT INTO `sys_region` VALUES (174, '張坊鎮(zhèn)', '張坊鎮(zhèn)', '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, '創(chuàng)新谷街道辦事處', '創(chuàng)新谷街道辦事處', '370171400000', '370171000000', 4, 0);
INSERT INTO `sys_region` VALUES (181, '章錦街道', '章錦街道', '370171401000', '370171000000', 4, 0);

2.遞歸查詢?nèi)N實現(xiàn)方式

2.1. 方式一創(chuàng)建自定義函數(shù)實現(xiàn)遞歸查詢

注意：

1.創(chuàng)建函數(shù)的時候，可能會報錯。This function has none of DETERMINISTIC 在MySQL安裝根目錄個人配置文件my.ini中添加一行：log_bin_trust_function_creators=1 然后重啟MySQL服務(wù)。

2.創(chuàng)建函數(shù)的時候用到了系統(tǒng)函數(shù)GROUP_CONCAT()，該函數(shù)默認長度是1024，在配置文件my.ini 中修改默認長度，添加一行語句：group_concat_max_len=102400 保存后重啟MySQL服務(wù)。查詢sql SELECT @@global.group_concat_max_len;

3.這種方式實現(xiàn)之后，調(diào)用簡單，但是效率較慢，而且由于字段、函數(shù)、長度的的限制，數(shù)據(jù)量大的時候可能查詢不全。適合小數(shù)據(jù)量下使用。

2.1.1. 查詢子節(jié)點的函數(shù) 查詢時包含自身

創(chuàng)建函數(shù)：

-- 查詢子節(jié)點的函數(shù)  查詢時包含自身
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

查詢示例：

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

2.1.2. 查詢子節(jié)點的函數(shù) 查詢時不包含自身

創(chuàng)建函數(shù)：

-- 查詢子節(jié)點的函數(shù) 查詢時  不包含自身
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.查詢父節(jié)點查詢的時候包含自身

創(chuàng)建函數(shù)：

-- 查詢父節(jié)點  查詢的時候 包含自身
 
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.查詢父節(jié)點查詢的時候不包含自身

創(chuàng)建函數(shù)：

-- 查詢父節(jié)點   查詢的時候  不包含自身
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 不創(chuàng)建函數(shù) 實現(xiàn)遞歸

注意：

寫法比較復(fù)雜，但是適合MySQL各版本，比較靈活。

2.2.1.查詢子節(jié)點含自己

查詢SQL腳本及效果截圖：

 
-- 單純使用SQL遞歸 查詢子節(jié)點  含自己
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.查詢子節(jié)點不含自己

查詢SQL腳本及效果截圖：

-- 單純使用SQL遞歸 查詢子節(jié)點  不含自己
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.查詢父節(jié)點含自己

查詢SQL腳本及效果截圖：

-- 單純使用SQL遞歸 查詢父節(jié)點  含自己
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.查詢父節(jié)點不含自己

查詢SQL腳本及效果截圖：

 -- 單純使用SQL遞歸 查詢父節(jié)點  不含自己
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 實現(xiàn)遞歸注意：

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

2.3.1.查詢子節(jié)點含自己

查詢SQL腳本及效果截圖：

 -- 查詢子節(jié)點  含自己
 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.查詢子節(jié)點不含自己

查詢SQL腳本及效果截圖：

 -- 查詢子節(jié)點   不含自己
 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.查詢父節(jié)點含自己

查詢SQL腳本及效果截圖：

 -- 查詢父節(jié)點  含自己
 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.查詢父節(jié)點不含自己

查詢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'
	;

總結(jié)

到此這篇關(guān)于MySQL遞歸查詢的3種實現(xiàn)方式的文章就介紹到這了,更多相關(guān)MySQL遞歸查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL遞歸查詢的3種實現(xiàn)方式實例

目錄

1.建表腳本

1.1.建表

2.遞歸查詢?nèi)N實現(xiàn)方式

2.1. 方式一創(chuàng)建自定義函數(shù)實現(xiàn)遞歸查詢

2.1.1. 查詢子節(jié)點的函數(shù) 查詢時包含自身

2.1.2. 查詢子節(jié)點的函數(shù) 查詢時不包含自身

2.1.3.查詢父節(jié)點查詢的時候包含自身

2.1.4.查詢父節(jié)點查詢的時候不包含自身

2.2. 方式二單純使用sql 不創(chuàng)建函數(shù) 實現(xiàn)遞歸

2.2.1.查詢子節(jié)點含自己

2.2.2.查詢子節(jié)點不含自己

2.2.3.查詢父節(jié)點含自己

2.2.4.查詢父節(jié)點不含自己

2.3. 方式三 MySQL 8.0 版本以上使用 WITH RECURSIVE 實現(xiàn)遞歸注意：

2.3.1.查詢子節(jié)點含自己

2.3.2.查詢子節(jié)點不含自己

2.3.3.查詢父節(jié)點含自己

2.3.4.查詢父節(jié)點不含自己

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL遞歸查詢的3種實現(xiàn)方式實例

目錄

1.建表腳本

1.1.建表

2.遞歸查詢?nèi)N實現(xiàn)方式

2.1. 方式一 創(chuàng)建自定義函數(shù)實現(xiàn)遞歸查詢

2.1.1. 查詢子節(jié)點的函數(shù) 查詢時 包含自身

2.1.2. 查詢子節(jié)點的函數(shù) 查詢時 不包含自身

2.1.3.查詢父節(jié)點 查詢的時候 包含自身

2.1.4.查詢父節(jié)點 查詢的時候 不包含自身

2.2. 方式二 單純使用sql 不創(chuàng)建函數(shù) 實現(xiàn)遞歸

2.2.1.查詢子節(jié)點 含自己

2.2.2.查詢子節(jié)點 不含自己

2.2.3.查詢父節(jié)點 含自己

2.2.4.查詢父節(jié)點 不含自己

2.3. 方式三 MySQL 8.0 版本以上 使用 WITH RECURSIVE 實現(xiàn)遞歸 注意：

2.3.1.查詢子節(jié)點 含自己

2.3.2.查詢子節(jié)點 不含自己

2.3.3.查詢父節(jié)點 含自己

2.3.4.查詢父節(jié)點 不含自己

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

2.1. 方式一創(chuàng)建自定義函數(shù)實現(xiàn)遞歸查詢

2.1.1. 查詢子節(jié)點的函數(shù) 查詢時包含自身

2.1.2. 查詢子節(jié)點的函數(shù) 查詢時不包含自身

2.1.3.查詢父節(jié)點查詢的時候包含自身

2.1.4.查詢父節(jié)點查詢的時候不包含自身

2.2. 方式二單純使用sql 不創(chuàng)建函數(shù) 實現(xiàn)遞歸

2.2.1.查詢子節(jié)點含自己

2.2.2.查詢子節(jié)點不含自己

2.2.3.查詢父節(jié)點含自己

2.2.4.查詢父節(jié)點不含自己

2.3. 方式三 MySQL 8.0 版本以上使用 WITH RECURSIVE 實現(xiàn)遞歸注意：

2.3.1.查詢子節(jié)點含自己

2.3.2.查詢子節(jié)點不含自己

2.3.3.查詢父節(jié)點含自己

2.3.4.查詢父節(jié)點不含自己