MySQL遞歸查詢的3種實(shí)現(xiàn)方式實(shí)例
更新時(shí)間：2022年04月15日 11:43:33 作者：十&年
在項(xiàng)目中會(huì)遇到同一個(gè)表中保存著父子關(guān)系的數(shù)據(jù),最常見(jiàn)的就是處理樹(shù)形結(jié)構(gòu)資源,下面這篇文章主要給大家介紹了關(guān)于MySQL遞歸查詢的3種實(shí)現(xiàn)方式,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下
1.建表腳本
1.1.建表
DROP TABLE IF EXISTS `sys_region`; CREATE TABLE `sys_region` ( `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '地區(qū)主鍵編號(hào)', `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 '簡(jiǎn)稱', `code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '行政地區(qū)編號(hào)', `parent_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父id', `level` int(2) NULL DEFAULT NULL COMMENT '1級(jí)：省、直轄市、自治區(qū)\r\n2級(jí)：地級(jí)市\(zhòng)r\n3級(jí)：市轄區(qū)、縣（旗）、縣級(jí)市、自治縣（自治旗）、特區(qū)、林區(qū)\r\n4級(jí)：鎮(zhèn)、鄉(xiāng)、民族鄉(xiāng)、縣轄區(qū)、街道\r\n5級(jí)：村、居委會(huì)', `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, '濟(jì)南市', '濟(jì)南', '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, '長(zhǎng)清區(qū)', '長(zhǎng)清區(qū)', '370113000000', '370100000000', 3, 0); INSERT INTO `sys_region` VALUES (10, '章丘區(qū)', '章丘區(qū)', '370114000000', '370100000000', 3, 0); INSERT INTO `sys_region` VALUES (11, '濟(jì)陽(yáng)區(qū)', '濟(jì)陽(yáng)區(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, '濟(jì)南高新技術(shù)產(chǎn)業(yè)開(kāi)發(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, '智遠(yuǎn)街道', '智遠(yuǎn)街道', '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, '中大槐樹(shù)街道', '中大槐樹(shù)街道', '370104002000', '370104000000', 4, 0); INSERT INTO `sys_region` VALUES (49, '道德街街道', '道德街街道', '370104003000', '370104000000', 4, 0); INSERT INTO `sys_region` VALUES (50, '西市場(chǎng)街道', '西市場(chǎng)街道', '370104004000', '370104000000', 4, 0); INSERT INTO `sys_region` VALUES (51, '五里溝街道', '五里溝街道', '370104005000', '370104000000', 4, 0); INSERT INTO `sys_region` VALUES (52, '營(yíng)市街街道', '營(yíng)市街街道', '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, '無(wú)影山街道', '無(wú)影山街道', '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, '官扎營(yíng)街道', '官扎營(yíng)街道', '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, '西營(yíng)街道', '西營(yíng)街道', '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, '萬(wàn)德街道', '萬(wàn)德街道', '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, '濟(jì)陽(yáng)街道', '濟(jì)陽(yáng)街道', '370115001000', '370115000000', 4, 0); INSERT INTO `sys_region` VALUES (124, '濟(jì)北街道', '濟(jì)北街道', '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, '棋山國(guó)家森林公園', '棋山國(guó)家森林公園', '370117400000', '370117000000', 4, 0); INSERT INTO `sys_region` VALUES (154, '高新技術(shù)開(kāi)發(fā)區(qū)', '高新技術(shù)開(kāi)發(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實(shí)現(xiàn)方式
2.1. 方式一 創(chuàng)建自定義函數(shù)實(shí)現(xiàn)遞歸查詢
注意：
1.創(chuàng)建函數(shù)的時(shí)候，可能會(huì)報(bào)錯(cuò)。This function has none of DETERMINISTIC 在MySQL安裝根目錄個(gè)人配置文件my.ini中添加一行：log_bin_trust_function_creators=1 然后重啟MySQL服務(wù)。
2.創(chuàng)建函數(shù)的時(shí)候用到了系統(tǒng)函數(shù)GROUP_CONCAT()，該函數(shù)默認(rèn)長(zhǎng)度是1024，在配置文件my.ini 中修改默認(rèn)長(zhǎng)度，添加一行語(yǔ)句：group_concat_max_len=102400 保存后重啟MySQL服務(wù)。查詢sql SELECT @@global.group_concat_max_len;
3.這種方式實(shí)現(xiàn)之后，調(diào)用簡(jiǎn)單，但是效率較慢，而且由于字段 、函數(shù)、 長(zhǎng)度的的限制，數(shù)據(jù)量大的時(shí)候可能查詢不全。適合小數(shù)據(jù)量下使用。
2.1.1. 查詢子節(jié)點(diǎn)的函數(shù) 查詢時(shí) 包含自身
創(chuàng)建函數(shù)：
-- 查詢子節(jié)點(diǎn)的函數(shù) 查詢時(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é)點(diǎn) 含自己
SELECT * from sys_region where FIND_IN_SET(code,queryChildren_1_sys_region('370000000000'));
2.1.2. 查詢子節(jié)點(diǎn)的函數(shù) 查詢時(shí) 不包含自身
創(chuàng)建函數(shù)：
-- 查詢子節(jié)點(diǎn)的函數(shù) 查詢時(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é)點(diǎn) 查詢的時(shí)候 包含自身
創(chuàng)建函數(shù)：
-- 查詢父節(jié)點(diǎn) 查詢的時(shí)候 包含自身 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é)點(diǎn) 查詢的時(shí)候 不包含自身
創(chuàng)建函數(shù)：
-- 查詢父節(jié)點(diǎn) 查詢的時(shí)候 不包含自身 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ù) 實(shí)現(xiàn)遞歸
注意：
寫(xiě)法比較復(fù)雜，但是適合MySQL各版本，比較靈活。
2.2.1.查詢子節(jié)點(diǎn) 含自己
查詢SQL腳本及效果截圖：
-- 單純使用SQL遞歸 查詢子節(jié)點(diǎn) 含自己 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é)點(diǎn) 不含自己
查詢SQL腳本及效果截圖：
-- 單純使用SQL遞歸 查詢子節(jié)點(diǎn) 不含自己 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é)點(diǎn) 含自己
查詢SQL腳本及效果截圖：
-- 單純使用SQL遞歸 查詢父節(jié)點(diǎn) 含自己 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é)點(diǎn) 不含自己
查詢SQL腳本及效果截圖：
-- 單純使用SQL遞歸 查詢父節(jié)點(diǎn) 不含自己 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 實(shí)現(xiàn)遞歸 注意：
寫(xiě)法比較簡(jiǎn)單，也比較靈活，但是只適用于MySQL8.0及以上版本，這種寫(xiě)法其實(shí)和 PostgreSQL 的寫(xiě)法是一樣的。
2.3.1.查詢子節(jié)點(diǎn) 含自己
查詢SQL腳本及效果截圖：
-- 查詢子節(jié)點(diǎn) 含自己
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é)點(diǎn) 不含自己
查詢SQL腳本及效果截圖：
-- 查詢子節(jié)點(diǎn) 不含自己
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é)點(diǎn) 含自己
查詢SQL腳本及效果截圖：
-- 查詢父節(jié)點(diǎn) 含自己
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é)點(diǎn) 不含自己
查詢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種實(shí)現(xiàn)方式的文章就介紹到這了,更多相關(guān)MySQL遞歸查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要為大家分享了mysql 5.7.10 安裝配置方法圖文教程，感興趣的小伙伴們可以參考一下2016-06-06
mysql 5.7以上版本安裝配置方法圖文教程（mysql 5.7.12\mysql 5.7.13\mysql 5.7.
這篇文章主要為大家分享了MySQL 5.7以上縮版本安裝配置方法圖文教程，包括mysql5.7.12、mysql5.7.13、mysql5.7.14安裝教程，包括感興趣的朋友可以參考一下
2016-08-08 
MySQL將CSV文件快速導(dǎo)入MySQL中的實(shí)現(xiàn)
本文主要介紹了MySQL將CSV文件快速導(dǎo)入MySQL中的實(shí)現(xiàn),主要包括navicat、workbench等軟件中的導(dǎo)入向?qū)謩?dòng)導(dǎo)入或使用load data infile命令導(dǎo)入,感興趣的可以了解一下
2023-11-11 
使用存儲(chǔ)過(guò)程實(shí)現(xiàn)循環(huán)插入100條記錄
本節(jié)主要介紹了使用存儲(chǔ)過(guò)程實(shí)現(xiàn)循環(huán)插入100條記錄的具體實(shí)現(xiàn)，需要的朋友可以參考下
2014-07-07 
這篇文章主要介紹了MySQL kill不掉線程的原因，幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下
2021-05-05 
這篇文章主要給大家分享了MySQL忘記密碼重置root密碼純,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作有一定的參考價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧
2023-12-12 
MySQL使用Sequence創(chuàng)建唯一主鍵的實(shí)現(xiàn)示例
Sequence提供了更多的靈活性,本文主要介紹了MySQL使用Sequence創(chuàng)建唯一主鍵的實(shí)現(xiàn)示例,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
2024-05-05 
用mysqldump備份和恢復(fù)指定表的方法...
2007-07-07 
MySQL server has gone away 問(wèn)題的解決方法
MySQL server has gone away 問(wèn)題解決方法，需要的朋友可以參考下。
2010-06-06