MySql递归应用

原创

小哥 2年前 (2023-05-17) 阅读数 84 #大杂烩

MySql版本8.0.12

全国地区编码表

#创建表
CREATE TABLE region (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  area_code varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  area_parent_code 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(4) unsigned DEFAULT NULL COMMENT 区域类型（1国家2省3市4区5街道）,
  PRIMARY KEY (id) USING BTREE,
  UNIQUE KEY idx_area_code (area_code) USING BTREE,
  KEY idx_area_name (area_name) USING BTREE,
  KEY idx_area_parent_code (area_parent_code)
) ENGINE=InnoDB AUTO_INCREMENT=65536 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

#插入数据（部分）
INSERT INTO region VALUES (2, 110000, 1, 1, 北京, 2);
INSERT INTO region VALUES (3, 110100, 110000, 110000,1, 北京市, 3);
INSERT INTO region VALUES (4, 110101, 110100, 110100,110000,1, 东城区, 4);
INSERT INTO region VALUES (5, 110102, 110100, 110100,110000,1, 西城区, 4);
INSERT INTO region VALUES (6, 110105, 110100, 110100,110000,1, 朝阳区, 4);
INSERT INTO region VALUES (7, 110106, 110100, 110100,110000,1, 丰台区, 4);
INSERT INTO region VALUES (8, 110107, 110100, 110100,110000,1, 石景山区, 4);
INSERT INTO region VALUES (9, 110108, 110100, 110100,110000,1, 海淀区, 4);
INSERT INTO region VALUES (10, 110109, 110100, 110100,110000,1, 门头沟区, 4);
INSERT INTO region VALUES (11, 110111, 110100, 110100,110000,1, 房山区, 4);
INSERT INTO region VALUES (12, 110112, 110100, 110100,110000,1, 通州区, 4);
INSERT INTO region VALUES (13, 110113, 110100, 110100,110000,1, 顺义区, 4);
INSERT INTO region VALUES (14, 110114, 110100, 110100,110000,1, 昌平区, 4);
INSERT INTO region VALUES (15, 110115, 110100, 110100,110000,1, 大兴区, 4);
INSERT INTO region VALUES (16, 110116, 110100, 110100,110000,1, 怀柔区, 4);
INSERT INTO region VALUES (17, 110117, 110100, 110100,110000,1, 平谷区, 4);
INSERT INTO region VALUES (18, 110118, 110100, 110100,110000,1, 密云区, 4);
INSERT INTO region VALUES (19, 110119, 110100, 110100,110000,1, 延庆区, 4);
INSERT INTO region VALUES (3660, 110101001, 110101, 110101,110100,110000,1, 东华门街道, 5);
INSERT INTO region VALUES (3661, 110101002, 110101, 110101,110100,110000,1, 景山街道, 5);
INSERT INTO region VALUES (3662, 110101003, 110101, 110101,110100,110000,1, 交道口街道, 5);
INSERT INTO region VALUES (3663, 110101004, 110101, 110101,110100,110000,1, 安定门街道, 5);
INSERT INTO region VALUES (3664, 110101005, 110101, 110101,110100,110000,1, 北新桥街道, 5);
INSERT INTO region VALUES (3665, 110101006, 110101, 110101,110100,110000,1, 东四街道, 5);
INSERT INTO region VALUES (3666, 110101007, 110101, 110101,110100,110000,1, 朝阳门街道, 5);
INSERT INTO region VALUES (3667, 110101008, 110101, 110101,110100,110000,1, 建国门街道, 5);
INSERT INTO region VALUES (3668, 110101009, 110101, 110101,110100,110000,1, 东直门街道, 5);
INSERT INTO region VALUES (3669, 110101010, 110101, 110101,110100,110000,1, 和平里街道, 5);
INSERT INTO region VALUES (3670, 110101011, 110101, 110101,110100,110000,1, 前门街道, 5);
INSERT INTO region VALUES (3671, 110101012, 110101, 110101,110100,110000,1, 崇文门外街道, 5);
INSERT INTO region VALUES (3672, 110101013, 110101, 110101,110100,110000,1, 东花市街道, 5);
INSERT INTO region VALUES (3673, 110101014, 110101, 110101,110100,110000,1, 龙潭街道, 5);
INSERT INTO region VALUES (3674, 110101015, 110101, 110101,110100,110000,1, 体育馆路街道, 5);
INSERT INTO region VALUES (3675, 110101016, 110101, 110101,110100,110000,1, 天坛街道, 5);
INSERT INTO region VALUES (3676, 110101017, 110101, 110101,110100,110000,1, 永定门外街道, 5);
INSERT INTO region VALUES (3677, 110102001, 110102, 110102,110100,110000,1, 西长安街街道, 5);
INSERT INTO region VALUES (3678, 110102003, 110102, 110102,110100,110000,1, 新街口街道, 5);
INSERT INTO region VALUES (3679, 110102007, 110102, 110102,110100,110000,1, 月坛街道, 5);
INSERT INTO region VALUES (3680, 110102009, 110102, 110102,110100,110000,1, 展览路街道, 5);
INSERT INTO region VALUES (3681, 110102010, 110102, 110102,110100,110000,1, 德胜街道, 5);
INSERT INTO region VALUES (3682, 110102011, 110102, 110102,110100,110000,1, 金融街街道, 5);
INSERT INTO region VALUES (3683, 110102012, 110102, 110102,110100,110000,1, 什刹海街道, 5);
INSERT INTO region VALUES (3684, 110102013, 110102, 110102,110100,110000,1, 大栅栏街道, 5);
INSERT INTO region VALUES (3685, 110102014, 110102, 110102,110100,110000,1, 天桥街道, 5);
INSERT INTO region VALUES (3686, 110102015, 110102, 110102,110100,110000,1, 椿树街道, 5);
INSERT INTO region VALUES (3687, 110102016, 110102, 110102,110100,110000,1, 陶然亭街道, 5);
INSERT INTO region VALUES (3688, 110102017, 110102, 110102,110100,110000,1, 广安门内街道, 5);
INSERT INTO region VALUES (3689, 110102018, 110102, 110102,110100,110000,1, 牛街街道, 5);
INSERT INTO region VALUES (3690, 110102019, 110102, 110102,110100,110000,1, 白纸坊街道, 5);
INSERT INTO region VALUES (3691, 110102020, 110102, 110102,110100,110000,1, 广安门外街道, 5);
INSERT INTO region VALUES (3692, 110105001, 110105, 110105,110100,110000,1, 建外街道, 5);
INSERT INTO region VALUES (3693, 110105002, 110105, 110105,110100,110000,1, 朝外街道, 5);
INSERT INTO region VALUES (3694, 110105003, 110105, 110105,110100,110000,1, 呼家楼街道, 5);
INSERT INTO region VALUES (3695, 110105004, 110105, 110105,110100,110000,1, 三里屯街道, 5);
INSERT INTO region VALUES (3696, 110105005, 110105, 110105,110100,110000,1, 左家庄街道, 5);
INSERT INTO region VALUES (3697, 110105006, 110105, 110105,110100,110000,1, 香河园街道, 5);
INSERT INTO region VALUES (3698, 110105007, 110105, 110105,110100,110000,1, 和平街街道, 5);
INSERT INTO region VALUES (3699, 110105008, 110105, 110105,110100,110000,1, 安贞街道, 5);
INSERT INTO region VALUES (3700, 110105009, 110105, 110105,110100,110000,1, 亚运村街道, 5);
INSERT INTO region VALUES (3701, 110105010, 110105, 110105,110100,110000,1, 小关街道, 5);
INSERT INTO region VALUES (3702, 110105011, 110105, 110105,110100,110000,1, 酒仙桥街道, 5);
INSERT INTO region VALUES (3703, 110105012, 110105, 110105,110100,110000,1, 麦子店街道, 5);
INSERT INTO region VALUES (3704, 110105013, 110105, 110105,110100,110000,1, 团结湖街道, 5);
INSERT INTO region VALUES (3705, 110105014, 110105, 110105,110100,110000,1, 六里屯街道, 5);
INSERT INTO region VALUES (3706, 110105015, 110105, 110105,110100,110000,1, 八里庄街道, 5);
INSERT INTO region VALUES (3707, 110105016, 110105, 110105,110100,110000,1, 双井街道, 5);
INSERT INTO region VALUES (3708, 110105017, 110105, 110105,110100,110000,1, 劲松街道, 5);
INSERT INTO region VALUES (3709, 110105018, 110105, 110105,110100,110000,1, 潘家园街道, 5);
INSERT INTO region VALUES (3710, 110105019, 110105, 110105,110100,110000,1, 垡头街道, 5);
INSERT INTO region VALUES (3711, 110105021, 110105, 110105,110100,110000,1, 南磨房镇, 5);
INSERT INTO region VALUES (3712, 110105022, 110105, 110105,110100,110000,1, 高碑店镇, 5);
INSERT INTO region VALUES (3713, 110105023, 110105, 110105,110100,110000,1, 将台镇, 5);
INSERT INTO region VALUES (3714, 110105024, 110105, 110105,110100,110000,1, 太阳宫镇, 5);
INSERT INTO region VALUES (3715, 110105025, 110105, 110105,110100,110000,1, 大屯街道, 5);
INSERT INTO region VALUES (3716, 110105026, 110105, 110105,110100,110000,1, 望京街道, 5);
INSERT INTO region VALUES (3717, 110105027, 110105, 110105,110100,110000,1, 小红门镇, 5);
INSERT INTO region VALUES (3718, 110105028, 110105, 110105,110100,110000,1, 十八里店镇, 5);
INSERT INTO region VALUES (3719, 110105029, 110105, 110105,110100,110000,1, 平房镇, 5);
INSERT INTO region VALUES (3720, 110105030, 110105, 110105,110100,110000,1, 东风镇, 5);
INSERT INTO region VALUES (3721, 110105031, 110105, 110105,110100,110000,1, 奥运村街道, 5);
INSERT INTO region VALUES (3722, 110105032, 110105, 110105,110100,110000,1, 来广营镇, 5);
INSERT INTO region VALUES (3723, 110105033, 110105, 110105,110100,110000,1, 常营镇, 5);
INSERT INTO region VALUES (3724, 110105034, 110105, 110105,110100,110000,1, 三间房镇, 5);
INSERT INTO region VALUES (3725, 110105035, 110105, 110105,110100,110000,1, 管庄镇, 5);
INSERT INTO region VALUES (3726, 110105036, 110105, 110105,110100,110000,1, 金盏镇, 5);
INSERT INTO region VALUES (3727, 110105037, 110105, 110105,110100,110000,1, 孙河镇, 5);
INSERT INTO region VALUES (3728, 110105038, 110105, 110105,110100,110000,1, 崔各庄镇, 5);
INSERT INTO region VALUES (3729, 110105039, 110105, 110105,110100,110000,1, 东坝镇, 5);
INSERT INTO region VALUES (3730, 110105040, 110105, 110105,110100,110000,1, 黑庄户镇, 5);
INSERT INTO region VALUES (3731, 110105041, 110105, 110105,110100,110000,1, 豆各庄镇, 5);
INSERT INTO region VALUES (3732, 110105042, 110105, 110105,110100,110000,1, 王四营镇, 5);
INSERT INTO region VALUES (3733, 110105043, 110105, 110105,110100,110000,1, 首都机场街道, 5);
INSERT INTO region VALUES (3734, 110105501, 110105, 110105,110100,110000,1, 东湖街道, 5);
INSERT INTO region VALUES (3735, 110106001, 110106, 110106,110100,110000,1, 右安门街道, 5);
INSERT INTO region VALUES (3736, 110106002, 110106, 110106,110100,110000,1, 太平桥街道, 5);
INSERT INTO region VALUES (3737, 110106003, 110106, 110106,110100,110000,1, 西罗园街道, 5);
INSERT INTO region VALUES (3738, 110106004, 110106, 110106,110100,110000,1, 大红门街道, 5);
INSERT INTO region VALUES (3739, 110106005, 110106, 110106,110100,110000,1, 南苑街道, 5);
INSERT INTO region VALUES (3740, 110106006, 110106, 110106,110100,110000,1, 东高地街道, 5);
INSERT INTO region VALUES (3741, 110106007, 110106, 110106,110100,110000,1, 东铁匠营街道, 5);
INSERT INTO region VALUES (3742, 110106008, 110106, 110106,110100,110000,1, 卢沟桥街道, 5);
INSERT INTO region VALUES (3743, 110106009, 110106, 110106,110100,110000,1, 丰台街道, 5);
INSERT INTO region VALUES (3744, 110106010, 110106, 110106,110100,110000,1, 新村街道, 5);
INSERT INTO region VALUES (3745, 110106011, 110106, 110106,110100,110000,1, 长辛店街道, 5);
INSERT INTO region VALUES (3746, 110106012, 110106, 110106,110100,110000,1, 云岗街道, 5);
INSERT INTO region VALUES (3747, 110106013, 110106, 110106,110100,110000,1, 方庄镇, 5);
INSERT INTO region VALUES (3748, 110106014, 110106, 110106,110100,110000,1, 宛平城镇, 5);
INSERT INTO region VALUES (3749, 110106015, 110106, 110106,110100,110000,1, 马家堡街道, 5);
INSERT INTO region VALUES (3750, 110106016, 110106, 110106,110100,110000,1, 和义街道, 5);
INSERT INTO region VALUES (3751, 110106017, 110106, 110106,110100,110000,1, 卢沟桥乡, 5);
INSERT INTO region VALUES (3752, 110106018, 110106, 110106,110100,110000,1, 花乡乡, 5);
INSERT INTO region VALUES (3753, 110106019, 110106, 110106,110100,110000,1, 南苑乡, 5);
INSERT INTO region VALUES (3754, 110106100, 110106, 110106,110100,110000,1, 长辛店镇, 5);
INSERT INTO region VALUES (3755, 110106101, 110106, 110106,110100,110000,1, 王佐镇, 5);
INSERT INTO region VALUES (3756, 110107001, 110107, 110107,110100,110000,1, 八宝山街道, 5);
INSERT INTO region VALUES (3757, 110107002, 110107, 110107,110100,110000,1, 老山街道, 5);
INSERT INTO region VALUES (3758, 110107003, 110107, 110107,110100,110000,1, 八角街道, 5);
INSERT INTO region VALUES (3759, 110107004, 110107, 110107,110100,110000,1, 古城街道, 5);
INSERT INTO region VALUES (3760, 110107005, 110107, 110107,110100,110000,1, 苹果园街道, 5);
INSERT INTO region VALUES (3761, 110107006, 110107, 110107,110100,110000,1, 金顶街街道, 5);
INSERT INTO region VALUES (3762, 110107009, 110107, 110107,110100,110000,1, 广宁街道, 5);
INSERT INTO region VALUES (3763, 110107010, 110107, 110107,110100,110000,1, 五里坨街道, 5);
INSERT INTO region VALUES (3764, 110107011, 110107, 110107,110100,110000,1, 鲁谷街道, 5);
INSERT INTO region VALUES (3765, 110108001, 110108, 110108,110100,110000,1, 万寿路街道, 5);
INSERT INTO region VALUES (3766, 110108002, 110108, 110108,110100,110000,1, 永定路街道, 5);
INSERT INTO region VALUES (3767, 110108003, 110108, 110108,110100,110000,1, 羊坊店街道, 5);
INSERT INTO region VALUES (3768, 110108004, 110108, 110108,110100,110000,1, 甘家口街道, 5);
INSERT INTO region VALUES (3769, 110108005, 110108, 110108,110100,110000,1, 八里庄街道, 5);
INSERT INTO region VALUES (3770, 110108006, 110108, 110108,110100,110000,1, 紫竹院街道, 5);
INSERT INTO region VALUES (3771, 110108007, 110108, 110108,110100,110000,1, 北下关街道, 5);
INSERT INTO region VALUES (3772, 110108008, 110108, 110108,110100,110000,1, 北太平庄街道, 5);
INSERT INTO region VALUES (3773, 110108010, 110108, 110108,110100,110000,1, 学院路街道, 5);
INSERT INTO region VALUES (3774, 110108011, 110108, 110108,110100,110000,1, 中关村街道, 5);
INSERT INTO region VALUES (3775, 110108012, 110108, 110108,110100,110000,1, 海淀街道, 5);
INSERT INTO region VALUES (3776, 110108013, 110108, 110108,110100,110000,1, 青龙桥街道, 5);
INSERT INTO region VALUES (3777, 110108014, 110108, 110108,110100,110000,1, 清华园街道, 5);
INSERT INTO region VALUES (3778, 110108015, 110108, 110108,110100,110000,1, 燕园街道, 5);
INSERT INTO region VALUES (3779, 110108016, 110108, 110108,110100,110000,1, 香山街道, 5);
INSERT INTO region VALUES (3780, 110108017, 110108, 110108,110100,110000,1, 清河街道, 5);
INSERT INTO region VALUES (3781, 110108018, 110108, 110108,110100,110000,1, 花园路街道, 5);
INSERT INTO region VALUES (3782, 110108019, 110108, 110108,110100,110000,1, 西三旗街道, 5);
INSERT INTO region VALUES (3783, 110108020, 110108, 110108,110100,110000,1, 马连洼街道, 5);
INSERT INTO region VALUES (3784, 110108021, 110108, 110108,110100,110000,1, 田村路街道, 5);
INSERT INTO region VALUES (3785, 110108022, 110108, 110108,110100,110000,1, 上地街道, 5);
INSERT INTO region VALUES (3786, 110108023, 110108, 110108,110100,110000,1, 万柳镇, 5);
INSERT INTO region VALUES (3787, 110108024, 110108, 110108,110100,110000,1, 东升镇, 5);
INSERT INTO region VALUES (3788, 110108025, 110108, 110108,110100,110000,1, 曙光街道, 5);
INSERT INTO region VALUES (3789, 110108026, 110108, 110108,110100,110000,1, 温泉镇, 5);
INSERT INTO region VALUES (3790, 110108027, 110108, 110108,110100,110000,1, 四季青镇, 5);
INSERT INTO region VALUES (3791, 110108028, 110108, 110108,110100,110000,1, 西北旺镇, 5);
INSERT INTO region VALUES (3792, 110108029, 110108, 110108,110100,110000,1, 苏家坨镇, 5);
INSERT INTO region VALUES (3793, 110108030, 110108, 110108,110100,110000,1, 上庄镇, 5);
INSERT INTO region VALUES (3794, 110109001, 110109, 110109,110100,110000,1, 大峪街道, 5);
INSERT INTO region VALUES (3795, 110109002, 110109, 110109,110100,110000,1, 城子街道, 5);
INSERT INTO region VALUES (3796, 110109003, 110109, 110109,110100,110000,1, 东辛房街道, 5);
INSERT INTO region VALUES (3797, 110109004, 110109, 110109,110100,110000,1, 大台街道, 5);
INSERT INTO region VALUES (3798, 110109005, 110109, 110109,110100,110000,1, 王平镇, 5);
INSERT INTO region VALUES (3799, 110109006, 110109, 110109,110100,110000,1, 永定镇, 5);
INSERT INTO region VALUES (3800, 110109007, 110109, 110109,110100,110000,1, 龙泉镇, 5);
INSERT INTO region VALUES (3801, 110109101, 110109, 110109,110100,110000,1, 潭柘寺镇, 5);
INSERT INTO region VALUES (3802, 110109104, 110109, 110109,110100,110000,1, 军庄镇, 5);
INSERT INTO region VALUES (3803, 110109105, 110109, 110109,110100,110000,1, 雁翅镇, 5);
INSERT INTO region VALUES (3804, 110109106, 110109, 110109,110100,110000,1, 斋堂镇, 5);
INSERT INTO region VALUES (3805, 110109107, 110109, 110109,110100,110000,1, 清水镇, 5);
INSERT INTO region VALUES (3806, 110109108, 110109, 110109,110100,110000,1, 妙峰山镇, 5);
INSERT INTO region VALUES (3807, 110111001, 110111, 110111,110100,110000,1, 城关街道, 5);
INSERT INTO region VALUES (3808, 110111002, 110111, 110111,110100,110000,1, 新镇街道, 5);
INSERT INTO region VALUES (3809, 110111004, 110111, 110111,110100,110000,1, 向阳街道, 5);
INSERT INTO region VALUES (3810, 110111005, 110111, 110111,110100,110000,1, 东风街道, 5);
INSERT INTO region VALUES (3811, 110111006, 110111, 110111,110100,110000,1, 迎风街道, 5);
INSERT INTO region VALUES (3812, 110111007, 110111, 110111,110100,110000,1, 星城街道, 5);
INSERT INTO region VALUES (3813, 110111008, 110111, 110111,110100,110000,1, 良乡镇, 5);
INSERT INTO region VALUES (3814, 110111009, 110111, 110111,110100,110000,1, 周口店镇, 5);
INSERT INTO region VALUES (3815, 110111010, 110111, 110111,110100,110000,1, 琉璃河镇, 5);
INSERT INTO region VALUES (3816, 110111011, 110111, 110111,110100,110000,1, 拱辰街道, 5);
INSERT INTO region VALUES (3817, 110111012, 110111, 110111,110100,110000,1, 西潞街道, 5);
INSERT INTO region VALUES (3818, 110111101, 110111, 110111,110100,110000,1, 阎村镇, 5);
INSERT INTO region VALUES (3819, 110111103, 110111, 110111,110100,110000,1, 窦店镇, 5);
INSERT INTO region VALUES (3820, 110111104, 110111, 110111,110100,110000,1, 石楼镇, 5);
INSERT INTO region VALUES (3821, 110111105, 110111, 110111,110100,110000,1, 长阳镇, 5);
INSERT INTO region VALUES (3822, 110111107, 110111, 110111,110100,110000,1, 河北镇, 5);
INSERT INTO region VALUES (3823, 110111108, 110111, 110111,110100,110000,1, 长沟镇, 5);
INSERT INTO region VALUES (3824, 110111109, 110111, 110111,110100,110000,1, 大石窝镇, 5);
INSERT INTO region VALUES (3825, 110111110, 110111, 110111,110100,110000,1, 张坊镇, 5);
INSERT INTO region VALUES (3826, 110111111, 110111, 110111,110100,110000,1, 十渡镇, 5);
INSERT INTO region VALUES (3827, 110111112, 110111, 110111,110100,110000,1, 青龙湖镇, 5);
INSERT INTO region VALUES (3828, 110111115, 110111, 110111,110100,110000,1, 韩村河镇, 5);
INSERT INTO region VALUES (3829, 110111208, 110111, 110111,110100,110000,1, 霞云岭乡, 5);
INSERT INTO region VALUES (3830, 110111209, 110111, 110111,110100,110000,1, 南窖乡, 5);
INSERT INTO region VALUES (3831, 110111210, 110111, 110111,110100,110000,1, 佛子庄乡, 5);
INSERT INTO region VALUES (3832, 110111211, 110111, 110111,110100,110000,1, 大安山乡, 5);
INSERT INTO region VALUES (3833, 110111212, 110111, 110111,110100,110000,1, 史家营乡, 5);
INSERT INTO region VALUES (3834, 110111213, 110111, 110111,110100,110000,1, 蒲洼乡, 5);
INSERT INTO region VALUES (3835, 110112001, 110112, 110112,110100,110000,1, 中仓街道, 5);
INSERT INTO region VALUES (3836, 110112002, 110112, 110112,110100,110000,1, 新华街道, 5);
INSERT INTO region VALUES (3837, 110112003, 110112, 110112,110100,110000,1, 北苑街道, 5);
INSERT INTO region VALUES (3838, 110112004, 110112, 110112,110100,110000,1, 玉桥街道, 5);
INSERT INTO region VALUES (3839, 110112005, 110112, 110112,110100,110000,1, 永顺镇, 5);
INSERT INTO region VALUES (3840, 110112006, 110112, 110112,110100,110000,1, 梨园镇, 5);
INSERT INTO region VALUES (3841, 110112104, 110112, 110112,110100,110000,1, 宋庄镇, 5);
INSERT INTO region VALUES (3842, 110112105, 110112, 110112,110100,110000,1, 张家湾镇, 5);
INSERT INTO region VALUES (3843, 110112106, 110112, 110112,110100,110000,1, 漷县镇, 5);
INSERT INTO region VALUES (3844, 110112109, 110112, 110112,110100,110000,1, 马驹桥镇, 5);
INSERT INTO region VALUES (3845, 110112110, 110112, 110112,110100,110000,1, 西集镇, 5);
INSERT INTO region VALUES (3846, 110112114, 110112, 110112,110100,110000,1, 台湖镇, 5);
INSERT INTO region VALUES (3847, 110112117, 110112, 110112,110100,110000,1, 永乐店镇, 5);
INSERT INTO region VALUES (3848, 110112119, 110112, 110112,110100,110000,1, 潞城镇, 5);
INSERT INTO region VALUES (3849, 110112209, 110112, 110112,110100,110000,1, 于家务回族乡, 5);
INSERT INTO region VALUES (3850, 110113001, 110113, 110113,110100,110000,1, 胜利街道, 5);
INSERT INTO region VALUES (3851, 110113002, 110113, 110113,110100,110000,1, 光明街道, 5);
INSERT INTO region VALUES (3852, 110113003, 110113, 110113,110100,110000,1, 仁和镇, 5);
INSERT INTO region VALUES (3853, 110113004, 110113, 110113,110100,110000,1, 后沙峪镇, 5);
INSERT INTO region VALUES (3854, 110113005, 110113, 110113,110100,110000,1, 天竺镇, 5);
INSERT INTO region VALUES (3855, 110113006, 110113, 110113,110100,110000,1, 杨镇镇, 5);
INSERT INTO region VALUES (3856, 110113007, 110113, 110113,110100,110000,1, 牛栏山镇, 5);
INSERT INTO region VALUES (3857, 110113008, 110113, 110113,110100,110000,1, 南法信镇, 5);
INSERT INTO region VALUES (3858, 110113009, 110113, 110113,110100,110000,1, 马坡镇, 5);
INSERT INTO region VALUES (3859, 110113010, 110113, 110113,110100,110000,1, 石园街道, 5);
INSERT INTO region VALUES (3860, 110113011, 110113, 110113,110100,110000,1, 空港街道, 5);
INSERT INTO region VALUES (3861, 110113012, 110113, 110113,110100,110000,1, 双丰街道, 5);
INSERT INTO region VALUES (3862, 110113013, 110113, 110113,110100,110000,1, 旺泉街道, 5);
INSERT INTO region VALUES (3863, 110113101, 110113, 110113,110100,110000,1, 高丽营镇, 5);
INSERT INTO region VALUES (3864, 110113104, 110113, 110113,110100,110000,1, 李桥镇, 5);
INSERT INTO region VALUES (3865, 110113105, 110113, 110113,110100,110000,1, 李遂镇, 5);
INSERT INTO region VALUES (3866, 110113106, 110113, 110113,110100,110000,1, 南彩镇, 5);
INSERT INTO region VALUES (3867, 110113108, 110113, 110113,110100,110000,1, 北务镇, 5);
INSERT INTO region VALUES (3868, 110113109, 110113, 110113,110100,110000,1, 大孙各庄镇, 5);
INSERT INTO region VALUES (3869, 110113110, 110113, 110113,110100,110000,1, 张镇, 5);
INSERT INTO region VALUES (3870, 110113111, 110113, 110113,110100,110000,1, 龙湾屯镇, 5);
INSERT INTO region VALUES (3871, 110113112, 110113, 110113,110100,110000,1, 木林镇, 5);
INSERT INTO region VALUES (3872, 110113113, 110113, 110113,110100,110000,1, 北小营镇, 5);
INSERT INTO region VALUES (3873, 110113115, 110113, 110113,110100,110000,1, 北石槽镇, 5);
INSERT INTO region VALUES (3874, 110113116, 110113, 110113,110100,110000,1, 赵全营镇, 5);
INSERT INTO region VALUES (3875, 110114001, 110114, 110114,110100,110000,1, 城北街道, 5);
INSERT INTO region VALUES (3876, 110114002, 110114, 110114,110100,110000,1, 南口镇, 5);
INSERT INTO region VALUES (3877, 110114003, 110114, 110114,110100,110000,1, 马池口镇, 5);
INSERT INTO region VALUES (3878, 110114004, 110114, 110114,110100,110000,1, 沙河镇, 5);
INSERT INTO region VALUES (3879, 110114005, 110114, 110114,110100,110000,1, 城南街道, 5);
INSERT INTO region VALUES (3880, 110114006, 110114, 110114,110100,110000,1, 回龙观街道, 5);
INSERT INTO region VALUES (3881, 110114007, 110114, 110114,110100,110000,1, 东小口镇, 5);
INSERT INTO region VALUES (3882, 110114008, 110114, 110114,110100,110000,1, 天通苑北街道, 5);
INSERT INTO region VALUES (3883, 110114009, 110114, 110114,110100,110000,1, 天通苑南街道, 5);
INSERT INTO region VALUES (3884, 110114010, 110114, 110114,110100,110000,1, 霍营街道, 5);
INSERT INTO region VALUES (3885, 110114011, 110114, 110114,110100,110000,1, 史各庄街道, 5);
INSERT INTO region VALUES (3886, 110114012, 110114, 110114,110100,110000,1, 龙泽园街道, 5);
INSERT INTO region VALUES (3887, 110114104, 110114, 110114,110100,110000,1, 阳坊镇, 5);
INSERT INTO region VALUES (3888, 110114110, 110114, 110114,110100,110000,1, 小汤山镇, 5);
INSERT INTO region VALUES (3889, 110114111, 110114, 110114,110100,110000,1, 南邵镇, 5);
INSERT INTO region VALUES (3890, 110114112, 110114, 110114,110100,110000,1, 崔村镇, 5);
INSERT INTO region VALUES (3891, 110114113, 110114, 110114,110100,110000,1, 百善镇, 5);
INSERT INTO region VALUES (3892, 110114115, 110114, 110114,110100,110000,1, 北七家镇, 5);
INSERT INTO region VALUES (3893, 110114116, 110114, 110114,110100,110000,1, 兴寿镇, 5);
INSERT INTO region VALUES (3894, 110114118, 110114, 110114,110100,110000,1, 流村镇, 5);
INSERT INTO region VALUES (3895, 110114119, 110114, 110114,110100,110000,1, 十三陵镇, 5);
INSERT INTO region VALUES (3896, 110114120, 110114, 110114,110100,110000,1, 延寿镇, 5);
INSERT INTO region VALUES (3897, 110115001, 110115, 110115,110100,110000,1, 兴丰街道, 5);
INSERT INTO region VALUES (3898, 110115002, 110115, 110115,110100,110000,1, 林校路街道, 5);
INSERT INTO region VALUES (3899, 110115003, 110115, 110115,110100,110000,1, 清源街道, 5);
INSERT INTO region VALUES (3900, 110115004, 110115, 110115,110100,110000,1, 亦庄镇, 5);
INSERT INTO region VALUES (3901, 110115005, 110115, 110115,110100,110000,1, 黄村镇, 5);
INSERT INTO region VALUES (3902, 110115006, 110115, 110115,110100,110000,1, 旧宫镇, 5);
INSERT INTO region VALUES (3903, 110115007, 110115, 110115,110100,110000,1, 西红门镇, 5);
INSERT INTO region VALUES (3904, 110115008, 110115, 110115,110100,110000,1, 瀛海镇, 5);
INSERT INTO region VALUES (3905, 110115009, 110115, 110115,110100,110000,1, 观音寺街道, 5);
INSERT INTO region VALUES (3906, 110115010, 110115, 110115,110100,110000,1, 天宫院街道, 5);
INSERT INTO region VALUES (3907, 110115103, 110115, 110115,110100,110000,1, 青云店镇, 5);
INSERT INTO region VALUES (3908, 110115104, 110115, 110115,110100,110000,1, 采育镇, 5);
INSERT INTO region VALUES (3909, 110115105, 110115, 110115,110100,110000,1, 安定镇, 5);
INSERT INTO region VALUES (3910, 110115106, 110115, 110115,110100,110000,1, 礼贤镇, 5);
INSERT INTO region VALUES (3911, 110115107, 110115, 110115,110100,110000,1, 榆垡镇, 5);
INSERT INTO region VALUES (3912, 110115108, 110115, 110115,110100,110000,1, 庞各庄镇, 5);
INSERT INTO region VALUES (3913, 110115109, 110115, 110115,110100,110000,1, 北臧村镇, 5);
INSERT INTO region VALUES (3914, 110115110, 110115, 110115,110100,110000,1, 魏善庄镇, 5);
INSERT INTO region VALUES (3915, 110115111, 110115, 110115,110100,110000,1, 长子营镇, 5);
INSERT INTO region VALUES (3916, 110115403, 110115, 110115,110100,110000,1, 北京经济技术开发区, 5);
INSERT INTO region VALUES (3917, 110115404, 110115, 110115,110100,110000,1, 中关村国家自主创新示范区大兴生物医药产业基地, 5);
INSERT INTO region VALUES (3918, 110115405, 110115, 110115,110100,110000,1, 国家新媒体产业基地, 5);
INSERT INTO region VALUES (3919, 110116001, 110116, 110116,110100,110000,1, 泉河街道, 5);
INSERT INTO region VALUES (3920, 110116002, 110116, 110116,110100,110000,1, 龙山街道, 5);
INSERT INTO region VALUES (3921, 110116003, 110116, 110116,110100,110000,1, 怀柔镇, 5);
INSERT INTO region VALUES (3922, 110116004, 110116, 110116,110100,110000,1, 雁栖镇, 5);
INSERT INTO region VALUES (3923, 110116005, 110116, 110116,110100,110000,1, 庙城镇, 5);
INSERT INTO region VALUES (3924, 110116102, 110116, 110116,110100,110000,1, 北房镇, 5);
INSERT INTO region VALUES (3925, 110116103, 110116, 110116,110100,110000,1, 杨宋镇, 5);
INSERT INTO region VALUES (3926, 110116105, 110116, 110116,110100,110000,1, 桥梓镇, 5);
INSERT INTO region VALUES (3927, 110116106, 110116, 110116,110100,110000,1, 怀北镇, 5);
INSERT INTO region VALUES (3928, 110116107, 110116, 110116,110100,110000,1, 汤河口镇, 5);
INSERT INTO region VALUES (3929, 110116108, 110116, 110116,110100,110000,1, 渤海镇, 5);
INSERT INTO region VALUES (3930, 110116109, 110116, 110116,110100,110000,1, 九渡河镇, 5);
INSERT INTO region VALUES (3931, 110116110, 110116, 110116,110100,110000,1, 琉璃庙镇, 5);
INSERT INTO region VALUES (3932, 110116111, 110116, 110116,110100,110000,1, 宝山镇, 5);
INSERT INTO region VALUES (3933, 110116211, 110116, 110116,110100,110000,1, 长哨营满族乡, 5);
INSERT INTO region VALUES (3934, 110116213, 110116, 110116,110100,110000,1, 喇叭沟门满族乡, 5);
INSERT INTO region VALUES (3935, 110116400, 110116, 110116,110100,110000,1, 北京雁栖经济开发区, 5);
INSERT INTO region VALUES (3936, 110117001, 110117, 110117,110100,110000,1, 滨河街道, 5);
INSERT INTO region VALUES (3937, 110117002, 110117, 110117,110100,110000,1, 兴谷街道, 5);
INSERT INTO region VALUES (3938, 110117003, 110117, 110117,110100,110000,1, 平谷镇, 5);
INSERT INTO region VALUES (3939, 110117004, 110117, 110117,110100,110000,1, 峪口镇, 5);
INSERT INTO region VALUES (3940, 110117005, 110117, 110117,110100,110000,1, 马坊镇, 5);
INSERT INTO region VALUES (3941, 110117006, 110117, 110117,110100,110000,1, 金海湖镇, 5);
INSERT INTO region VALUES (3942, 110117101, 110117, 110117,110100,110000,1, 东高村镇, 5);
INSERT INTO region VALUES (3943, 110117102, 110117, 110117,110100,110000,1, 山东庄镇, 5);
INSERT INTO region VALUES (3944, 110117104, 110117, 110117,110100,110000,1, 南独乐河镇, 5);
INSERT INTO region VALUES (3945, 110117105, 110117, 110117,110100,110000,1, 大华山镇, 5);
INSERT INTO region VALUES (3946, 110117106, 110117, 110117,110100,110000,1, 夏各庄镇, 5);
INSERT INTO region VALUES (3947, 110117108, 110117, 110117,110100,110000,1, 马昌营镇, 5);
INSERT INTO region VALUES (3948, 110117110, 110117, 110117,110100,110000,1, 王辛庄镇, 5);
INSERT INTO region VALUES (3949, 110117111, 110117, 110117,110100,110000,1, 大兴庄镇, 5);
INSERT INTO region VALUES (3950, 110117112, 110117, 110117,110100,110000,1, 刘家店镇, 5);
INSERT INTO region VALUES (3951, 110117114, 110117, 110117,110100,110000,1, 镇罗营镇, 5);
INSERT INTO region VALUES (3952, 110117213, 110117, 110117,110100,110000,1, 黄松峪乡, 5);
INSERT INTO region VALUES (3953, 110117214, 110117, 110117,110100,110000,1, 熊儿寨乡, 5);
INSERT INTO region VALUES (3954, 110118001, 110118, 110118,110100,110000,1, 鼓楼街道, 5);
INSERT INTO region VALUES (3955, 110118002, 110118, 110118,110100,110000,1, 果园街道, 5);
INSERT INTO region VALUES (3956, 110118003, 110118, 110118,110100,110000,1, 檀营镇, 5);
INSERT INTO region VALUES (3957, 110118100, 110118, 110118,110100,110000,1, 密云镇, 5);
INSERT INTO region VALUES (3958, 110118101, 110118, 110118,110100,110000,1, 溪翁庄镇, 5);
INSERT INTO region VALUES (3959, 110118102, 110118, 110118,110100,110000,1, 西田各庄镇, 5);
INSERT INTO region VALUES (3960, 110118103, 110118, 110118,110100,110000,1, 十里堡镇, 5);
INSERT INTO region VALUES (3961, 110118104, 110118, 110118,110100,110000,1, 河南寨镇, 5);
INSERT INTO region VALUES (3962, 110118105, 110118, 110118,110100,110000,1, 巨各庄镇, 5);
INSERT INTO region VALUES (3963, 110118106, 110118, 110118,110100,110000,1, 穆家峪镇, 5);
INSERT INTO region VALUES (3964, 110118107, 110118, 110118,110100,110000,1, 太师屯镇, 5);
INSERT INTO region VALUES (3965, 110118108, 110118, 110118,110100,110000,1, 高岭镇, 5);
INSERT INTO region VALUES (3966, 110118109, 110118, 110118,110100,110000,1, 不老屯镇, 5);
INSERT INTO region VALUES (3967, 110118110, 110118, 110118,110100,110000,1, 冯家峪镇, 5);
INSERT INTO region VALUES (3968, 110118111, 110118, 110118,110100,110000,1, 古北口镇, 5);
INSERT INTO region VALUES (3969, 110118112, 110118, 110118,110100,110000,1, 大城子镇, 5);
INSERT INTO region VALUES (3970, 110118113, 110118, 110118,110100,110000,1, 东邵渠镇, 5);
INSERT INTO region VALUES (3971, 110118114, 110118, 110118,110100,110000,1, 北庄镇, 5);
INSERT INTO region VALUES (3972, 110118115, 110118, 110118,110100,110000,1, 新城子镇, 5);
INSERT INTO region VALUES (3973, 110118116, 110118, 110118,110100,110000,1, 石城镇, 5);
INSERT INTO region VALUES (3974, 110118400, 110118, 110118,110100,110000,1, 北京密云经济开发区, 5);
INSERT INTO region VALUES (3975, 110119001, 110119, 110119,110100,110000,1, 百泉街道, 5);
INSERT INTO region VALUES (3976, 110119002, 110119, 110119,110100,110000,1, 香水园街道, 5);
INSERT INTO region VALUES (3977, 110119003, 110119, 110119,110100,110000,1, 儒林街道, 5);
INSERT INTO region VALUES (3978, 110119100, 110119, 110119,110100,110000,1, 延庆镇, 5);
INSERT INTO region VALUES (3979, 110119101, 110119, 110119,110100,110000,1, 康庄镇, 5);
INSERT INTO region VALUES (3980, 110119102, 110119, 110119,110100,110000,1, 八达岭镇, 5);
INSERT INTO region VALUES (3981, 110119103, 110119, 110119,110100,110000,1, 永宁镇, 5);
INSERT INTO region VALUES (3982, 110119104, 110119, 110119,110100,110000,1, 旧县镇, 5);
INSERT INTO region VALUES (3983, 110119105, 110119, 110119,110100,110000,1, 张山营镇, 5);
INSERT INTO region VALUES (3984, 110119106, 110119, 110119,110100,110000,1, 四海镇, 5);
INSERT INTO region VALUES (3985, 110119107, 110119, 110119,110100,110000,1, 千家店镇, 5);
INSERT INTO region VALUES (3986, 110119108, 110119, 110119,110100,110000,1, 沈家营镇, 5);
INSERT INTO region VALUES (3987, 110119109, 110119, 110119,110100,110000,1, 大榆树镇, 5);
INSERT INTO region VALUES (3988, 110119110, 110119, 110119,110100,110000,1, 井庄镇, 5);
INSERT INTO region VALUES (3989, 110119207, 110119, 110119,110100,110000,1, 大庄科乡, 5);
INSERT INTO region VALUES (3990, 110119209, 110119, 110119,110100,110000,1, 刘斌堡乡, 5);
INSERT INTO region VALUES (3991, 110119210, 110119, 110119,110100,110000,1, 香营乡, 5);
INSERT INTO region VALUES (3992, 110119214, 110119, 110119,110100,110000,1, 珍珠泉乡, 5);

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

方法1：使用 `WITH RECURSIVE` 递归（版本8.0以上支持）

WITH RECURSIVE county AS (
    SELECT
        region.area_code,
        region.area_parent_code,
        region.area_name 
    FROM
        region 
    WHERE
        region.area_code = 131125 UNION ALL
    SELECT
        region.area_code,
        region.area_parent_code,
        region.area_name 
    FROM region,county WHERE county.area_parent_code = region.area_code 
    )
SELECT * FROM county

region,county 可以换成 region JOIN county
region JOIN county WHERE county.area_parent_code = region.area_code 可以把 WHERE 换成 ON
可以把 * 改成 area_code,area_name ，只输出需要的字段

方法2：使用纯SQL语句

使用自定义变量 @

SELECT
    region.area_code,
    region.area_parent_code,
    region.area_name 
FROM
    (
    SELECT
        @area_codes AS area_codes,
        ( SELECT @area_codes := area_parent_code FROM region WHERE area_code = @area_codes ) t1 
    FROM
        region,
        ( SELECT @area_codes := 131125 ) t2 
    WHERE
        @area_codes IS NOT NULL 
    ) t3,
    region 
WHERE
    region.area_code = t3.area_codes

方法3：新增字段 `area_parent_path` （路径枚举）

生成area_parent_path值的PHP代码：

RegionModel::query()->chunkById(100, function ($items) {
    foreach ($items as $item) {
        $areaParentPath = $item->area_parent_path;
        if (!empty($item->area_parent_code)) {
            $areaParentPath .= , . $item->area_parent_code;

            $item4 = RegionModel::query()->where(area_code, $item->area_parent_code)->first();
            if (!empty($item4) && !empty($item4->area_parent_code)) {
                $areaParentPath .= , . $item4->area_parent_code;

                $item3 = RegionModel::query()->where(area_code, $item4->area_parent_code)->first();
                if (!empty($item3) && !empty($item3->area_parent_code)) {
                    $areaParentPath .= , . $item3->area_parent_code;

                    $item2 = RegionModel::query()->where(area_code, $item3->area_parent_code)->first();
                    if (!empty($item2) && !empty($item2->area_parent_code)) {
                        $areaParentPath .= , . $item2->area_parent_code;
                    }
                }
            }
        }

        $areaParentPath = trim($areaParentPath, ,);
        var_dump($areaParentPath);
        if (!empty($areaParentPath)) {
            $item->update([area_parent_path => $areaParentPath]);
        }
    }
});

例如：

这样一来查询SQL就简单了，利用内置函数 FIND_IN_SET(str,strlist) ，而且效率比纯SQL更好

SELECT 
    area_code,
    area_parent_code,
    area_name
FROM region
WHERE area_code = 131125 OR
    FIND_IN_SET(
        area_code,
        ( SELECT area_parent_path FROM region WHERE area_code = 131125 )
    );

可以改成 UNION ALL ，比 OR 效率更好

SELECT
    area_code,
    area_parent_code,
    area_name 
FROM
    region 
WHERE
    area_code = 131125 UNION ALL
SELECT
    area_code,
    area_parent_code,
    area_name 
FROM
    region 
WHERE
    FIND_IN_SET( area_code, ( SELECT area_parent_path FROM region WHERE area_code = 131125 ) );

需求2：查询‘襄阳市’全部下级地区

方法1：使用 `WITH RECURSIVE` 递归

WITH recursive county AS (
    SELECT
        region.area_code,
        region.area_parent_code,
        region.area_name,
        region.area_type 
    FROM
        region 
    WHERE
        region.area_code = 420600 UNION ALL
    SELECT
        region.area_code,
        region.area_parent_code,
        region.area_name,
        region.area_type 
    FROM region JOIN county ON county.area_code = region.area_parent_code 
    ) SELECT
    * 
FROM
    county

方法2：使用纯SQL语句

SELECT
    T3.area_code,
    T3.area_parent_code,
    T3.area_name,
    T3.area_type 
FROM
    (
    SELECT
        @codes AS _ids,
        ( SELECT @codes := GROUP_CONCAT( area_code ) FROM region WHERE FIND_IN_SET( area_parent_code, @codes ) ) AS T1 
    FROM
        region,
        ( SELECT @codes := 420600 ) T4 
    WHERE
        @codes IS NOT NULL 
    ) T2,
    region T3 
WHERE
    FIND_IN_SET( T3.area_code, T2._ids );

注意：数据量非常大时，第二种方法会丢失一些数据，暂时还没找到原因和解决办法。
可能是 @code := GROUP_CONCAT( area_code ) 的长度超出了临时表的限制吧

方法3：使用内置函数 `FIND_IN_SET`

SELECT
    region.area_code,
    region.area_parent_code,
    region.area_name,
    region.area_type 
FROM
    region 
WHERE
    area_code = 420600 
    OR FIND_IN_SET(420600, area_parent_path)

可以改成 UNION ALL ，比 OR 效率更好

SELECT
    region.area_code,
    region.area_parent_code,
    region.area_name,
    region.area_type 
FROM
    region 
WHERE
    area_code = 420600 UNION ALL
SELECT
    region.area_code,
    region.area_parent_code,
    region.area_name,
    region.area_type 
FROM
    region 
WHERE
    FIND_IN_SET(
    420600,
    area_parent_path)