Mysql递归测试

MySql版本8.0.12

全国地区编码表：

CREATE TABLE `region` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `area_code` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `area_parent_id` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `area_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `area_type` tinyint(11) DEFAULT NULL COMMENT '区域类型（1国家2省3市4区5街道）',
  `create_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_area_code` (`area_code`),
  KEY `idx_area_name` (`area_name`)
) ENGINE=InnoDB AUTO_INCREMENT=49584 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
1
2
3
4
5
6
7
8
9
10
11

需求：查出‘安平县’及其上级区域的编码和名称

WITH recursive county AS (
	SELECT
		region.area_code,
		region.area_parent_id,
		region.area_name 
	FROM
		region 
	WHERE
		region.area_name = '安平县' UNION ALL
	SELECT
		region.area_code,
		region.area_parent_id,
		region.area_name 
	FROM region,county WHERE county.area_parent_id = region.area_code 
	)
SELECT * FROM county
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

查询结果：

+-----------+----------------+----------------+
| area_code | area_parent_id | area_name      |
+-----------+----------------+----------------+
| 131125    | 131100         | 安平县         |
| 131100    | 130000         | 衡水市         |
| 130000    | 1              | 河北省         |
| 1         | NULL           | 中华人民共和国 |
+-----------+----------------+----------------+
1
2
3
4
5
6
7
8

region,county可以换成region JOIN county

region JOIN county WHERE county.area_parent_id = region.area_code 可以把WHERE换成ON

可以把*改成area_code,area_name，只输出需要的字段