MySql 递归应用

已于 2022-07-14 10:52:18 修改
阅读量531 收藏 5
点赞数 2
分类专栏： MySQL 文章标签： mysql 数据库
于 2022-06-24 15:00:08 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Gan_1314/article/details/125445471
版权

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)

在这里插入图片描述

总结

可以发现：方法1查询效率最好；方法3查询效率次之；方法2查询效率最差还有可能丢失数据。
所以在MySQL版本支持WITH RECURSIVE语法的条件下尽量使用该语法吧。若不支持就选择FIND_IN_SET

Gan_1314
关注
  • 2
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql数据库地区表 递归查询 省市区
weixin_41692833的博客
02-27 3109
直接上sql，简单粗暴！！ SELECT t1.area_name AS '省', t2.area_name AS '市', t3.area_name AS '区' FROM ( SELECT * FROM base_area_new WHERE area_type = 1 AND version = 2 AND data_status = 1 ) AS t1 INNER JOIN ( SELECT * FROM base_area_new WHERE area_type = 2 AND ver
mysql递归查询省市区
weixin_44967580的博客
12-13 4759
mysql递归查询
mysql利用函数递归查询所有的下级以及下级处于的层级
wl_6219的博客
09-25 2152
直接看sql begin declare childs longtext; declare tempchildids longtext; declare tempparentids longtext; declare otempc int(11); set childs = ''; set tempchildids=''; set tempparentids=concat(pid,''); se...
Mysql 递归查询
weixin_42537012的博客
04-03 1945
Mysql 递归查询
MySQL8.0递归
2301_78137387的博客
11-16 212
MySQL with Recursive是一种基于递归思想的MySQL查询方式，可以实现对数据的和处理，返回符合条件的数据。在MySQL 8.0版本中，该功能被正式引入。
mysql递归查询子部门
jattxgt的博客
07-22 671
mysql递归查询子部门
mysql省市区递归查询_mysql 递归查询
weixin_28877505的博客
01-28 1461
1、创建表：DROP TABLE IF EXISTS `t_areainfo`;CREATE TABLE `t_areainfo` (`id` int(11) NOT '' AUTO_INCREMENT,`level` int(11) DEFAULT '',`name` varchar(255) DEFAULT '',`parentId` int(11) DEFAULT '',`status` i...
MySQL递归查询超详细--保姆级别讲解
djdjfjf1的博客
12-05 4442
保证每个学子都能理解的文章
mysql存储过程 递归_MySQL递归查找存储过程
weixin_39744240的博客
01-18 544
依然是SQL问题，是上篇博客(http://my.oschina.net/u/1991646/blog/731996)的升级版需求(上篇是查询3层，此次是查询所有)：无可避免的最终还是使用了MySQL存储过程。先说明业务场景：查询出某个节点中的所有下属节点。表结构和数据为：id superior_id57adca6415a414043183326f57...
mysql 部门名称递归_mysql 部门 递归 新方法
weixin_39667041的博客
02-05 287
SELECT * FROM w_department/*查询市场部 下 所以 部门 的部门名称*/SELECT node.department_id,node.department_nameFROM w_department AS node,w_department AS parentWHERE node.lft BETWEEN parent.lft AND parent.rgtAND pa...
Node+Express+mysql实现CRUD
05-26
Node+Express+mysql实现CRUD
php+mysql实现无限分类实例详解
01-20
1、数据库通过设置父类ID来进行唯一索引,然后使用函数的递归调用实现无限分类； 2、数据库设计通过特定格式进行排列,然后使用mysql查询关键函数:concat,程序实现比较简单,首先我们假设有这样的一个三级分类,新闻→...
MYSQL无限分类算法文档
07-27
不解MYSQL高级数据管理的相关技巧，包括了一些必要的SQL语句和高级数据管理的讲解，像邻接表模型、检索整树、检索所有叶子节点、检索单一路径、邻接表模型的局限性、嵌套集合(Nested Set)模型、检索子树的深度、嵌套...
PHP和MySQL Web开发第4版
08-13
5.10 实现递归 5.10.1 名称空间 5.11 进一步学习 5.12 下一章 第6章 面向对象的PHP 6.1 理解面向对象的概念 6.1.1 类和对象 6.1.2 多态性 6.1.3 继承 6.2 在PHP中创建类、属性和操作 6.2.1 类的结构 ...
PHP和MySQL Web开发第4版pdf以及源码
10-13
5.10 实现递归 5.10.1 名称空间 5.11 进一步学习 5.12 下一章 第6章 面向对象的PHP 6.1 理解面向对象的概念 6.1.1 类和对象 6.1.2 多态性 6.1.3 继承 6.2 在PHP中创建类、属性和操作 6.2.1 类的结构 ...
mysql支持的存储引擎有哪些
JustinMars的博客
05-28 176
选择存储引擎时应根据具体应用场景和需求来决定。例如： - 如果需要事务和高数据完整性，选择InnoDB。 - 如果读操作频繁且不需要事务，选择MyISAM。 - 如果需要快速的临时数据存储，选择MEMORY。 - 如果需要数据压缩和存档，选择ARCHIVE。
chat4-Server端保存聊天消息到mysql
最新发布
gulanga5的博客
06-02 468
本文档描述了Server端接收到Client的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql服务端为当前客户端创建一个线程，此线程接收当前客户端的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql本文档主要总结了将聊天消息保存到mysql!!!),;import;import;import;/**用druid连接池来连接数据库的工具类*/DBUtil。
【MySQL】索引
cefler的博客
05-31 780
【MySQL】索引
MySQL库操作
2301_76197086的博客
05-28 371
MySQL库操作
mysql递归查询语言
08-11
### 回答1： MySQL 递归查询语言主要是通过使用 `WITH RECURSIVE` 关键字来实现的，类似于其他数据库管理系统中的 `WITH RECURSIVE` 关键字。 下面是一个使用 MySQL 递归查询语言的例子，假设我们有一个表格 `employee` 包含员工的 ID、姓名和上级 ID： ```sql CREATE TABLE `employee` ( `id` INT NOT NULL, `name` VARCHAR(45) NOT NULL, `manager_id` INT NULL, PRIMARY KEY (`id`) ); INSERT INTO `employee` (`id`, `name`, `manager_id`) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 2), (4, 'David', 2), (5, 'Eve', 1), (6, 'Frank', 5), (7, 'Grace', 5), (8, 'Henry', 7); ``` 现在我们想要查询每个员工的直接上级以及所有上级的姓名，可以使用递归查询语言来实现： ```sql WITH RECURSIVE cte (id, name, manager_id, level) AS ( SELECT id, name, manager_id, 0 FROM employee WHERE id = 6 -- 员工 ID UNION ALL SELECT e.id, e.name, e.manager_id, c.level + 1 FROM employee e JOIN cte c ON e.id = c.manager_id ) SELECT CONCAT(REPEAT(' ', level), name) AS name, level FROM cte ORDER BY level; ``` 这个查询语句会输出以下结果： ``` Frank 0 Eve 1 Grace 2 Henry 3 Alice 1 ``` 这个结果表示 Frank 的直接上级是 Eve，Eve 的直接上级是 Alice，而 Grace 和 Henry 是 Eve 的上级，Alice 没有上级。 ### 回答2： MySQL递归查询语言是指在MySQL数据库中使用递归查询来实现对层次结构数据的查询。递归查询可以用于处理带有父子关系的数据，例如组织机构、分类目录等。 MySQL递归查询语言的实现方式是通过使用WITH RECURSIVE子句来定义递归查询。这个子句包含两部分：递归部分和终止条件部分。递归部分定义了每一次递归查询时如何从上一次的结果中获取下一级的数据，而终止条件部分定义了递归查询何时停止。 在递归查询中，通过使用UNION操作符将递归部分和终止条件部分连接在一起。递归部分的查询语句中使用了上一次递归查询的结果作为输入，并且在结果中筛选出下一级的数据。终止条件部分的查询语句用于获取最顶层的数据。 递归查询语言的基本语法如下： ``` WITH RECURSIVE cte_name (column1, column2, ...) AS ( SELECT initial_query UNION ALL SELECT recursive_query ) SELECT * FROM cte_name; ``` 递归查询语言的应用场景很多，例如可以用来查询组织机构的层级结构、获取分类目录的嵌套关系等。通过递归查询，可以轻松地遍历和操作具有层次结构的数据。 ### 回答3： MySQL并没有专门的递归查询语言。但是可以通过使用存储过程和临时表来实现递归查询。 在MySQL中，可以通过编写存储过程来实现递归查询。存储过程是一种预先编译的SQL语句集合，可以在MySQL数据库上进行执行。在存储过程中，可以使用循环和条件语句来进行递归查询。通过逐步迭代和条件判断，可以实现对具有层级结构的数据进行递归查询。 此外，临时表也是实现递归查询的一种方法。临时表是一种临时存储数据的表，它只存在于当前会话中，并在会话结束后自动被删除。通过创建多个临时表和通过循环插入数据到临时表，可以模拟递归查询的过程。 总的来说，虽然MySQL并没有原生的递归查询语言，但是可以通过存储过程和临时表等方法来实现递归查询。递归查询在处理具有层级结构的数据时非常有用，可以实现对树形结构数据的遍历和查询。但是需要注意的是，递归查询可能会导致性能问题，所以在使用时要谨慎考虑。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值