MySQL with Recursive是一种基于递归思想的MySQL查询方式，可以实现对数据的递归查询和处理，返回符合条件的数据，在MySQL 8.0版本中，该功能被正式引入。

1
2
3
4
5
6
7
8
WITH RECURSIVE cte_name (column_list) AS (
    SELECT initial_query_result
    UNION [ALL]
    SELECT recursive_query
    FROM cte_name
    WHERE condition
)
SELECT * FROM cte_name;
1
2
3
4
5
6
7
8
9
10
11
12
-- 创建表
DROP TABLE IF EXISTS `sys_region`;
CREATE TABLE sys_region  (
  id int NOT NULL AUTO_INCREMENT COMMENT '地区主键编号',
  name varchar(50) COMMENT '地区名称',
  short_name varchar(50) COMMENT '简称',
  code varchar(50) COMMENT '行政地区编号',
  parent_code varchar(50) COMMENT '父id',
  level int(2)  COMMENT '1级：省、直辖市、自治区\r\n2级：地级市\r\n3级：市辖区、县（旗）、县级市、自治县（自治旗）、特区、林区\r\n4级：镇、乡、民族乡、县辖区、街道\r\n5级：村、居委会',
  flag int(1) 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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
-- 插入数据
INSERT INTO sys_region 
VALUES 
(1, "山东省", "鲁", "370000000000", NULL, 1, 0),
(2, "济南市", "济南", "370100000000", "370000000000", 2, 0),
(3, "市辖区", "市辖区", "370101000000", "370100000000", 3, 0),
(4, "历下区", "历下区", "370102000000", "370100000000", 3, 0),
(5, "市中区", "市中区", "370103000000", "370100000000", 3, 0),
(6, "槐荫区", "槐荫区", "370104000000", "370100000000", 3, 0),
(7, "天桥区", "天桥区", "370105000000", "370100000000", 3, 0),
(8, "历城区", "历城区", "370112000000", "370100000000", 3, 0),
(9, "长清区", "长清区", "370113000000", "370100000000", 3, 0),
(10, "章丘区", "章丘区", "370114000000", "370100000000", 3, 0),
(11, "济阳区", "济阳区", "370115000000", "370100000000", 3, 0),
(12, "莱芜区", "莱芜区", "370116000000", "370100000000", 3, 0),
(13, "钢城区", "钢城区", "370117000000", "370100000000", 3, 0),
(14, "平阴县", "平阴县", "370124000000", "370100000000", 3, 0),
(15, "商河县", "商河县", "370126000000", "370100000000", 3, 0),
(16, "济南高新技术产业开发区", "高新区", "370171000000", "370100000000", 3, 0),
(17, "解放路街道", "解放路街道", "370102001000", "370102000000", 4, 0),
(18, "千佛山街道", "千佛山街道", "370102002000", "370102000000", 4, 0),
(19, "趵突泉街道", "趵突泉街道", "370102003000", "370102000000", 4, 0),
(20, "泉城路街道", "泉城路街道", "370102004000", "370102000000", 4, 0),
(21, "大明湖街道", "大明湖街道", "370102005000", "370102000000", 4, 0),
(22, "东关街道", "东关街道", "370102006000", "370102000000", 4, 0),
(23, "文东街道", "文东街道", "370102007000", "370102000000", 4, 0),
(24, "建新街道", "建新街道", "370102008000", "370102000000", 4, 0),
(25, "甸柳街道", "甸柳街道", "370102009000", "370102000000", 4, 0),
(26, "燕山街道", "燕山街道", "370102010000", "370102000000", 4, 0),
(27, "姚家街道", "姚家街道", "370102011000", "370102000000", 4, 0),
(28, "龙洞街道", "龙洞街道", "370102012000", "370102000000", 4, 0),
(29, "智远街道", "智远街道", "370102013000", "370102000000", 4, 0),
(30, "大观园街道", "大观园街道", "370103002000", "370103000000", 4, 0),
(31, "杆石桥街道", "杆石桥街道", "370103003000", "370103000000", 4, 0),
(32, "四里村街道", "四里村街道", "370103004000", "370103000000", 4, 0),
(33, "魏家庄街道", "魏家庄街道", "370103006000", "370103000000", 4, 0),
(34, "二七街道", "二七街道", "370103008000", "370103000000", 4, 0),
(35, "七里山街道", "七里山街道", "370103009000", "370103000000", 4, 0),
(36, "六里山街道", "六里山街道", "370103010000", "370103000000", 4, 0),
(37, "舜玉路街道", "舜玉路街道", "370103012000", "370103000000", 4, 0),
(38, "泺源街道", "泺源街道", "370103014000", "370103000000", 4, 0),
(39, "王官庄街道", "王官庄街道", "370103015000", "370103000000", 4, 0),
(40, "舜耕街道", "舜耕街道", "370103016000", "370103000000", 4, 0),
(41, "白马山街道", "白马山街道", "370103017000", "370103000000", 4, 0),
(42, "七贤街道", "七贤街道", "370103018000", "370103000000", 4, 0),
(43, "十六里河街道", "十六里河街道", "370103019000", "370103000000", 4, 0),
(44, "兴隆街道", "兴隆街道", "370103020000", "370103000000", 4, 0),
(45, "党家街道", "党家街道", "370103021000", "370103000000", 4, 0),
(46, "陡沟街道", "陡沟街道", "370103022000", "370103000000", 4, 0),
(47, "振兴街街道", "振兴街街道", "370104001000", "370104000000", 4, 0),
(48, "中大槐树街道", "中大槐树街道", "370104002000", "370104000000", 4, 0),
(49, "道德街街道", "道德街街道", "370104003000", "370104000000", 4, 0),
(50, "西市场街道", "西市场街道", "370104004000", "370104000000", 4, 0),
(51, "五里沟街道", "五里沟街道", "370104005000", "370104000000", 4, 0),
(52, "营市街街道", "营市街街道", "370104006000", "370104000000", 4, 0),
(53, "青年公园街道", "青年公园街道", "370104007000", "370104000000", 4, 0),
(54, "南辛庄街道", "南辛庄街道", "370104008000", "370104000000", 4, 0),
(55, "段店北路街道", "段店北路街道", "370104009000", "370104000000", 4, 0),
(56, "张庄路街道", "张庄路街道", "370104010000", "370104000000", 4, 0),
(57, "匡山街道", "匡山街道", "370104011000", "370104000000", 4, 0),
(58, "美里湖街道", "美里湖街道", "370104012000", "370104000000", 4, 0),
(59, "腊山街道", "腊山街道", "370104013000", "370104000000", 4, 0),
(60, "兴福街道", "兴福街道", "370104014000", "370104000000", 4, 0),
(61, "玉清湖街道", "玉清湖街道", "370104015000", "370104000000", 4, 0),
(62, "吴家堡街道", "吴家堡街道", "370104016000", "370104000000", 4, 0),
(63, "无影山街道", "无影山街道", "370105001000", "370105000000", 4, 0),
(64, "天桥东街街道", "天桥东街街道", "370105003000", "370105000000", 4, 0),
(65, "北村街道", "北村街道", "370105004000", "370105000000", 4, 0),
(66, "南村街道", "南村街道", "370105005000", "370105000000", 4, 0),
(67, "堤口路街道", "堤口路街道", "370105006000", "370105000000", 4, 0),
(68, "北坦街道", "北坦街道", "370105007000", "370105000000", 4, 0),
(69, "制锦市街道", "制锦市街道", "370105009000", "370105000000", 4, 0),
(70, "宝华街道", "宝华街道", "370105010000", "370105000000", 4, 0),
(71, "官扎营街道", "官扎营街道", "370105011000", "370105000000", 4, 0),
(72, "纬北路街道", "纬北路街道", "370105012000", "370105000000", 4, 0),
(73, "药山街道", "药山街道", "370105013000", "370105000000", 4, 0),
(74, "北园街道", "北园街道", "370105014000", "370105000000", 4, 0),
(75, "泺口街道", "泺口街道", "370105015000", "370105000000", 4, 0),
(76, "桑梓店街道", "桑梓店街道", "370105016000", "370105000000", 4, 0),
(77, "大桥街道", "大桥街道", "370105017000", "370105000000", 4, 0),
(78, "山大路街道", "山大路街道", "370112001000", "370112000000", 4, 0),
(79, "洪家楼街道", "洪家楼街道", "370112002000", "370112000000", 4, 0),
(80, "东风街道", "东风街道", "370112003000", "370112000000", 4, 0),
(81, "全福街道", "全福街道", "370112004000", "370112000000", 4, 0),
(82, "华山街道", "华山街道", "370112007000", "370112000000", 4, 0),
(83, "荷花路街道", "荷花路街道", "370112008000", "370112000000", 4, 0),
(84, "王舍人街道", "王舍人街道", "370112009000", "370112000000", 4, 0),
(85, "鲍山街道", "鲍山街道", "370112010000", "370112000000", 4, 0),
(86, "郭店街道", "郭店街道", "370112011000", "370112000000", 4, 0),
(87, "唐冶街道", "唐冶街道", "370112012000", "370112000000", 4, 0),
(88, "港沟街道", "港沟街道", "370112013000", "370112000000", 4, 0),
(89, "董家街道", "董家街道", "370112016000", "370112000000", 4, 0),
(90, "彩石街道", "彩石街道", "370112017000", "370112000000", 4, 0),
(91, "仲宫街道", "仲宫街道", "370112018000", "370112000000", 4, 0),
(92, "柳埠街道", "柳埠街道", "370112019000", "370112000000", 4, 0),
(93, "唐王街道", "唐王街道", "370112020000", "370112000000", 4, 0),
(94, "西营街道", "西营街道", "370112021000", "370112000000", 4, 0),
(95, "文昌街道", "文昌街道", "370113001000", "370113000000", 4, 0),
(96, "崮云湖街道", "崮云湖街道", "370113002000", "370113000000", 4, 0),
(97, "平安街道", "平安街道", "370113003000", "370113000000", 4, 0),
(98, "五峰山街道", "五峰山街道", "370113004000", "370113000000", 4, 0),
(99, "归德街道", "归德街道", "370113005000", "370113000000", 4, 0),
(100, "张夏街道", "张夏街道", "370113006000", "370113000000", 4, 0),
(101, "万德街道", "万德街道", "370113007000", "370113000000", 4, 0),
(102, "孝里镇", "孝里镇", "370113102000", "370113000000", 4, 0),
(103, "马山镇", "马山镇", "370113107000", "370113000000", 4, 0),
(104, "双泉镇", "双泉镇", "370113108000", "370113000000", 4, 0),
(105, "明水街道", "明水街道", "370114001000", "370114000000", 4, 0),
(106, "双山街道", "双山街道", "370114002000", "370114000000", 4, 0),
(107, "枣园街道", "枣园街道", "370114003000", "370114000000", 4, 0),
(108, "龙山街道", "龙山街道", "370114004000", "370114000000", 4, 0),
(109, "埠村街道", "埠村街道", "370114005000", "370114000000", 4, 0),
(110, "圣井街道", "圣井街道", "370114006000", "370114000000", 4, 0),
(111, "普集街道", "普集街道", "370114007000", "370114000000", 4, 0),
(112, "绣惠街道", "绣惠街道", "370114008000", "370114000000", 4, 0),
(113, "相公庄街道", "相公庄街道", "370114009000", "370114000000", 4, 0),
(114, "文祖街道", "文祖街道", "370114010000", "370114000000", 4, 0),
(115, "官庄街道", "官庄街道", "370114011000", "370114000000", 4, 0),
(116, "高官寨街道", "高官寨街道", "370114012000", "370114000000", 4, 0),
(117, "白云湖街道", "白云湖街道", "370114013000", "370114000000", 4, 0),
(118, "宁家埠街道", "宁家埠街道", "370114014000", "370114000000", 4, 0),
(119, "曹范街道", "曹范街道", "370114015000", "370114000000", 4, 0),
(120, "***镇", "***镇", "370114100000", "370114000000", 4, 0),
(121, "垛庄镇", "垛庄镇", "370114101000", "370114000000", 4, 0),
(122, "黄河镇", "黄河镇", "370114102000", "370114000000", 4, 0),
(123, "济阳街道", "济阳街道", "370115001000", "370115000000", 4, 0),
(124, "济北街道", "济北街道", "370115002000", "370115000000", 4, 0),
(125, "崔寨街道", "崔寨街道", "370115003000", "370115000000", 4, 0),
(126, "孙耿街道", "孙耿街道", "370115004000", "370115000000", 4, 0),
(127, "回河街道", "回河街道", "370115005000", "370115000000", 4, 0),
(128, "太平街道", "太平街道", "370115006000", "370115000000", 4, 0),
(129, "垛石镇", "垛石镇", "370115101000", "370115000000", 4, 0),
(130, "曲堤镇", "曲堤镇", "370115103000", "370115000000", 4, 0),
(131, "仁风镇", "仁风镇", "370115104000", "370115000000", 4, 0),
(132, "新市镇", "新市镇", "370115110000", "370115000000", 4, 0),
(133, "凤城街道", "凤城街道", "370116001000", "370116000000", 4, 0),
(134, "张家洼街道", "张家洼街道", "370116002000", "370116000000", 4, 0),
(135, "高庄街道", "高庄街道", "370116003000", "370116000000", 4, 0),
(136, "鹏泉街道", "鹏泉街道", "370116004000", "370116000000", 4, 0),
(137, "口镇", "口镇", "370116100000", "370116000000", 4, 0),
(138, "羊里镇", "羊里镇", "370116101000", "370116000000", 4, 0),
(139, "方下镇", "方下镇", "370116102000", "370116000000", 4, 0),
(140, "牛泉镇", "牛泉镇", "370116103000", "370116000000", 4, 0),
(141, "苗山镇", "苗山镇", "370116104000", "370116000000", 4, 0),
(142, "雪野镇", "雪野镇", "370116105000", "370116000000", 4, 0),
(143, "大王庄镇", "大王庄镇", "370116106000", "370116000000", 4, 0),
(144, "寨里镇", "寨里镇", "370116107000", "370116000000", 4, 0),
(145, "杨庄镇", "杨庄镇", "370116108000", "370116000000", 4, 0),
(146, "茶业口镇", "茶业口镇", "370116109000", "370116000000", 4, 0),
(147, "和庄镇", "和庄镇", "370116110000", "370116000000", 4, 0),
(148, "艾山街道", "艾山街道", "370117001000", "370117000000", 4, 0),
(149, "里辛街道", "里辛街道", "370117002000", "370117000000", 4, 0),
(150, "汶源街道", "汶源街道", "370117003000", "370117000000", 4, 0),
(151, "颜庄镇", "颜庄镇", "370117100000", "370117000000", 4, 0),
(152, "辛庄镇", "辛庄镇", "370117103000", "370117000000", 4, 0),
(153, "棋山国家森林公园", "棋山国家森林公园", "370117400000", "370117000000", 4, 0),
(154, "高新技术开发区", "高新技术开发区", "370117401000", "370117000000", 4, 0),
(155, "榆山街道", "榆山街道", "370124001000", "370124000000", 4, 0),
(156, "锦水街道", "锦水街道", "370124002000", "370124000000", 4, 0),
(157, "东阿镇", "东阿镇", "370124102000", "370124000000", 4, 0),
(158, "孝直镇", "孝直镇", "370124103000", "370124000000", 4, 0),
(159, "孔村镇", "孔村镇", "370124104000", "370124000000", 4, 0),
(160, "洪范池镇", "洪范池镇", "370124105000", "370124000000", 4, 0),
(161, "玫瑰镇", "玫瑰镇", "370124106000", "370124000000", 4, 0),
(162, "安城镇", "安城镇", "370124107000", "370124000000", 4, 0),
(163, "许商街道", "许商街道", "370126001000", "370126000000", 4, 0),
(164, "殷巷镇", "殷巷镇", "370126101000", "370126000000", 4, 0),
(165, "怀仁镇", "怀仁镇", "370126102000", "370126000000", 4, 0),
(166, "龙桑寺镇", "龙桑寺镇", "370126104000", "370126000000", 4, 0),
(167, "郑路镇", "郑路镇", "370126105000", "370126000000", 4, 0),
(168, "贾庄镇", "贾庄镇", "370126106000", "370126000000", 4, 0),
(169, "玉皇庙镇", "玉皇庙镇", "370126107000", "370126000000", 4, 0),
(170, "白桥镇", "白桥镇", "370126108000", "370126000000", 4, 0),
(171, "孙集镇", "孙集镇", "370126109000", "370126000000", 4, 0),
(172, "韩庙镇", "韩庙镇", "370126110000", "370126000000", 4, 0),
(173, "沙河镇", "沙河镇", "370126111000", "370126000000", 4, 0),
(174, "张坊镇", "张坊镇", "370126112000", "370126000000", 4, 0),
(175, "舜华路街道", "舜华路街道", "370171001000", "370171000000", 4, 0),
(176, "孙村街道", "孙村街道", "370171002000", "370171000000", 4, 0),
(177, "巨野河街道", "巨野河街道", "370171003000", "370171000000", 4, 0),
(178, "遥墙街道", "遥墙街道", "370171004000", "370171000000", 4, 0),
(179, "临港街道", "临港街道", "370171005000", "370171000000", 4, 0),
(180, "创新谷街道办事处", "创新谷街道办事处", "370171400000", "370171000000", 4, 0),
(181, "章锦街道", "章锦街道", "370171401000", "370171000000", 4, 0);
1
2
3
4
5
6
7
8
9
10
11
-- 查询子节点  含自己
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;
1
2
3
4
5
6
7
8
9
10
11
12
-- 查询子节点 不含自己
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';
1
2
3
4
5
6
7
8
9
10
11
-- 查询父节点  含自己
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;