2021年最新省市县三级行政区划数据数据库sql文件,并标注拼音,拼音首字母
/*
Navicat Premium Data Transfer
Source Server : localhost_127.0.0.1
Source Server Type : MySQL
Source Server Version : 100410
Source Host : localhost:3306
Source Schema : code.cent123.com
Date: 01/06/2021 15:43:39
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for china_area_2021
-- ----------------------------
DROP TABLE IF EXISTS `china_area_2021`;
CREATE TABLE `china_area_2021` (
`index` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '索引',
`id` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '城市编号，三级用int类型，四级用long类型；省市区三级为统计局的编号经过去除后缀的0{3,6,8}得到的短编号，港澳台编号为民政部的编号；如果是添加的数据（国外），此编号为自定义编号；镇级主要为腾讯地图行政区划的编号，大部分和统计局的数据一致，约7.5%（约3000个）的镇级不一致；如果某级缺失(如：省直辖县级市、新增城市)，会用上级数据进行补齐，编号为上级结尾添加0{2,3}，注意如果要恢复长编号时（简单的补上00）已有的ID会和添加的ID产生冲突，比如4位恢复到6位将导致部分上下级ID冲突，恢复时这些新加的数据要进行特殊处理。',
`pid` int NOT NULL DEFAULT 0 COMMENT '上级ID',
`deep` tinyint NULL DEFAULT 0 COMMENT '层级深度；0：省，1：市，2：区，3：镇',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '城市名称；省市区三级为统计局的名称精简过后的，镇级主要为腾讯地图行政区划的名称精简过后的',
`pinyin_prefix` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT 'name的拼音前缀，取的是pinyin第一个字母，或港澳台、国外自定义前缀；用来排序时应当先根据拼音前缀的首字母来排序，相同的再根据前缀+名称进行排序',
`pinyin` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT 'name的完整拼音',
`ext_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '数据源原始的编号；如果是添加的数据，此编号为0',
`ext_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '数据源原始的名称，为未精简的名称',
`pinyin_prefix2` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'name的完整拼音',
PRIMARY KEY (`index`) USING BTREE,
UNIQUE INDEX `un_id`(`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 3638 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of china_area_2021
-- ----------------------------
INSERT INTO `china_area_2021` VALUES (1, 11, 0, 0, '北京', 'b', 'bei jing', '110000000000', '北京市', 'bj');
INSERT INTO `china_area_2021` VALUES (2, 1101, 11, 1, '北京', 'b', 'bei jing', '110100000000', '市辖区', 'bj');
INSERT INTO `china_area_2021` VALUES (3, 110101, 1101, 2, '东城', 'd', 'dong cheng', '110101000000', '东城区', 'dc');
INSERT INTO `china_area_2021` VALUES (4, 110102, 1101, 2, '西城', 'x', 'xi cheng', '110102000000', '西城区', 'xc');
INSERT INTO `china_area_2021` VALUES (5, 110105, 1101, 2, '朝阳', 'c', 'chao yang', '110105000000', '朝阳区', 'cy');
INSERT INTO `china_area_2021` VALUES (6, 110106, 1101, 2, '丰台', 'f', 'feng tai', '110106000000', '丰台区', 'ft');
INSERT INTO `china_area_2021` VALUES (7, 110107, 1101, 2, '石景山', 's', 'shi jing shan', '110107000000', '石景山区', 'sjs');
INSERT INTO `china_area_2021` VALUES (8, 110108, 1101, 2, '海淀', 'h', 'hai dian', '110108000000', '海淀区', 'hd');
INSERT INTO `china_area_2021` VALUES (9, 110109, 1101, 2, '门头沟', 'm', 'men tou gou', '110109000000', '门头沟区', 'mtg');
INSERT INTO `china_area_2021` VALUES (10, 110111, 1101, 2, '房山', 'f', 'fang shan', '110111000000', '房山区', 'fs');
INSERT INTO `china_area_2021` VALUES (11, 110112, 1101, 2, '通州', 't', 'tong zhou', '110112000000', '通州区', 'tz');
INSERT INTO `china_area_2021` VALUES (12, 110113, 1101, 2, '顺义', 's', 'shun yi', '110113000000', '顺义区', 'sy');
INSERT INTO `china_area_2021` VALUES (13, 110114, 1101, 2, '昌平', 'c', 'chang ping', '110114000000', '昌平区', 'cp');
INSERT INTO `china_area_2021` VALUES (14, 110115, 1101, 2, '大兴', 'd', 'da xing', '110115000000', '大兴区', 'dx');
INSERT INTO `china_area_2021` VALUES (15, 110116, 1101, 2, '怀柔', 'h', 'huai rou', '110116000000', '怀柔区', 'hr');
INSERT INTO `china_area_2021` VALUES (16, 110117, 1101, 2, '平谷', 'p', 'ping gu', '110117000000', '平谷区', 'pg');
INSERT INTO `china_area_2021` VALUES (17, 110118, 1101, 2, '密云', 'm', 'mi yun', '110118000000', '密云区', 'my');
INSERT INTO `china_area_2021` VALUES (18, 110119, 1101, 2, '延庆', 'y', 'yan qing', '110119000000', '延庆区', 'yq');
INSERT INTO `china_area_2021` VALUES (19, 12, 0, 0, '天津', 't', 'tian jin', '120000000000', '天津市', 'tj');
INSERT INTO `china_area_2021` VALUES (20, 1201, 12, 1, '天津', 't', 'tian jin', '120100000000', '市辖区', 'tj');
INSERT INTO `china_area_2021` VALUES (21, 120101, 1201, 2, '和平', 'h', 'he ping', '120101000000', '和平区', 'hp');
INSERT INTO `china_area_2021` VALUES (22, 120102, 1201, 2, '河东', 'h', 'he dong', '120102000000', '河东区', 'hd');
INSERT INTO `china_area_2021` VALUES (23, 120103, 1201, 2, '河西', 'h', 'he xi', '120103000000', '河西区', 'hx');
INSERT INTO `china_area_2021` VALUES (24, 120104, 1201, 2, '南开', 'n', 'nan kai', '120104000000', '南开区', 'nk');
INSERT INTO `china_area_2021` VALUES (25, 120105, 1201, 2, '河北', 'h', 'he bei', '120105000000', '河北区', 'hb');
INSERT INTO `china_area_2021` VALUES (26, 120106, 1201, 2, '红桥', 'h', 'hong qiao', '120106000000', '红桥区', 'hq');
INSERT INTO `china_area_2021` VALUES (27, 120110, 1201, 2, '东丽', 'd', 'dong li', '120110000000', '东丽区', 'dl');
INSERT INTO `china_area_2021` VALUES (28, 120111, 1201, 2, '西青', 'x', 'xi qing', '120111000000', '西青区', 'xq');
INSERT INTO `china_area_2021` VALUES (29, 120112, 1201, 2, '津南', 'j', 'jin nan', '120112000000', '津南区', 'jn');
INSERT INTO `china_area_2021` VALUES (30, 120113, 1201, 2, '北辰', 'b', 'bei chen', '120113000000', '北辰区', 'bc');
INSERT INTO `china_area_2021` VALUES (31, 120114, 1201, 2, '武清', 'w', 'wu qing', '120114000000', '武清区', 'wq');
INSERT INTO `china_area_2021` VALUES (32, 120115, 1201, 2, '宝坻', 'b', 'bao di', '120115000000', '宝坻区', 'bd');
INSERT INTO `china_area_2021` VALUES (33, 120116, 1201, 2, '滨海新区', 'b', 'bin hai xin qu', '120116000000', '滨海新区', 'bhxq');
INSERT INTO `china_area_2021` VALUES (34, 120117, 1201, 2, '宁河', 'n', 'ning he', '120117000000', '宁河区', 'nh');
INSERT INTO `china_area_2021` VALUES (35, 120118, 1201, 2, '静海', 'j', 'jing hai', '120118000000', '静海区', 'jh');
INSERT INTO `china_area_2021` VALUES (36, 120119, 1201, 2, '蓟州', 'j', 'ji zhou', '120119000000', '蓟州区', 'jz');
INSERT INTO `china_area_2021` VALUES (37, 13, 0, 0, '河北', 'h', 'he bei', '130000000000', '河北省', 'hb');
INSERT INTO `china_area_2021` VALUES (38, 1301, 13, 1, '石家庄', 's', 'shi jia zhuang', '130100000000', '石家庄市', 'sjz');
INSERT INTO `china_area_2021` VALUES (39, 130102, 1301, 2, '长安', 'c', 'chang an', '130102000000', '长安区', 'ca');
INSERT INTO `china_area_2021` VALUES (40, 130104, 1301, 2, '桥西', 'q', 'qiao xi', '130104000000', '桥西区', 'qx');
INSERT INTO `china_area_2021` VALUES (41, 130105, 1301, 2, '新华', 'x', 'xin hua', '130105000000', '新华区', 'xh');
INSERT INTO `china_area_2021` VALUES (42, 130107, 1301, 2, '井陉矿区', 'j', 'jing xing kuang qu', '130107000000', '井陉矿区', 'jxkq');
INSERT INTO `china_area_2021` VALUES (43, 130108, 1301, 2, '裕华', 'y', 'yu hua', '130108000000', '裕华区', 'yh');
INSERT INTO `china_area_2021` VALUES (44, 130109, 1301, 2, '藁城', 'g', 'gao cheng', '130109000000', '藁城区', 'gc');
INSERT INTO `china_area_2021` VALUES (45, 130110, 1301, 2, '鹿泉', 'l', 'lu quan', '130110000000', '鹿泉区', 'lq');
INSERT INTO `china_area_2021` VALUES (46, 130111, 1301, 2, '栾城', 'l', 'luan cheng', '130111000000', '栾城区', 'lc');
INSERT INTO `china_area_2021` VALUES (47, 130121, 1301, 2, '井陉', 'j', 'jing xing', '130121000000', '井陉县', 'jx');
INSERT INTO `china_area_2021` VALUES (48, 130123, 1301, 2, '正定', 'z', 'zheng ding', '130123000000', '正定县', 'zd');
INSERT INTO `china_area_2021` VALUES (49, 130125, 1301, 2, '行唐', 'x', 'xing tang', '130125000000', '行唐县', 'xt');
INSERT INTO `china_area_2021` VALUES (50, 130126, 1301, 2, '灵寿', 'l', 'ling shou', '130126000000', '灵寿县', 'ls');
INSERT INTO `china_area_2021` VALUES (51, 130127, 1301, 2, '高邑', 'g', 'gao yi', '130127000000', '高邑县', 'gy');
INSERT INTO `china_area_2021` VALUES (52, 130128, 1301, 2, '深泽', 's', 'shen ze', '130128000000', '深泽县', 'sz');
INSERT INTO `china_area_2021` VALUES (53, 130129, 1301, 2, '赞皇', 'z', 'zan huang', '130129000000', '赞皇县', 'zh');
INSERT INTO `china_area_2021` VALUES (54, 130130, 1301, 2, '无极', 'w', 'wu ji', '130130000000', '无极县', 'wj');
INSERT INTO `china_area_2021` VALUES (55, 130131, 1301, 2, '平山', 'p', 'ping shan', '130131000000', '平山县', 'ps');
INSERT INTO `china_area_2021` VALUES (56, 130132, 1301, 2, '元氏', 'y', 'yuan shi', '130132000000', '元氏县', 'ys');
INSERT INTO `china_area_2021` VALUES (57, 130133, 1301, 2, '赵县', 'z', 'zhao xian', '130133000000', '赵县', 'zx');
INSERT INTO `china_area_2021` VALUES (58, 130181, 1301, 2, '辛集', 'x', 'xin ji', '130181000000', '辛集市', 'xj');
INSERT INTO `china_area_2021` VALUES (59, 130183, 1301, 2, '晋州', 'j', 'jin zhou', '130183000000', '晋州市', 'jz');
INSERT INTO `china_area_2021` VALUES (60, 130184, 1301, 2, '新乐', 'x', 'xin le', '130184000000', '新乐市', 'xl');
INSERT INTO `china_area_2021` VALUES (61, 1302, 13, 1, '唐山', 't', 'tang shan', '130200000000', '唐山市', 'ts');
INSERT INTO `china_area_2021` VALUES (62, 130202, 1302, 2, '路南', 'l', 'lu nan', '130202000000', '路南区', 'ln');
INSERT INTO `china_area_2021` VALUES (63, 130203, 1302, 2, '路北', 'l', 'lu bei', '130203000000', '路北区', 'lb');
