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

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)文章希望大家以后多多支持腳本之家!

  • mysql 5.7以上版本安裝配置方法圖文教程(mysql 5.7.12\mysql 5.7.13\mysql 5.7.14)

    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)

    本文主要介紹了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條記錄

    使用存儲(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不掉線程的原因

    MySQL kill不掉線程的原因

    這篇文章主要介紹了MySQL kill不掉線程的原因,幫助大家更好的理解和學(xué)習(xí)使用MySQL數(shù)據(jù)庫(kù),感興趣的朋友可以了解下
    2021-05-05
  • MySQL忘記密碼重置root密碼純步驟分享

    MySQL忘記密碼重置root密碼純步驟分享

    這篇文章主要給大家分享了MySQL忘記密碼重置root密碼純,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作有一定的參考價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧
    2023-12-12
  • MySQL使用Sequence創(chuàng)建唯一主鍵的實(shí)現(xiàn)示例

    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ù)指定表的方法

    用mysqldump備份和恢復(fù)指定表的方法

    用mysqldump備份和恢復(fù)指定表的方法...
    2007-07-07
  • MySQL server has gone away 問(wèn)題的解決方法

    MySQL server has gone away 問(wèn)題的解決方法

    MySQL server has gone away 問(wèn)題解決方法,需要的朋友可以參考下。
    2010-06-06
  • 最新評(píng)論