5步搞定中国行政区划MySQL数据库：零基础搭建五级联动系统-编程实验室

5步搞定中国行政区划MySQL数据库：零基础搭建五级联动系统

【免费下载链接】Administrative-divisions-of-China中华人民共和国行政区划：省级（省份）、地级（城市）、县级（区县）、乡级（乡镇街道）、村级（村委会居委会），中国省市区镇村二级三级四级五级联动地址数据。项目地址: https://gitcode.com/gh_mirrors/ad/Administrative-divisions-of-China

想要快速部署一个完整的中国行政区划数据库吗？这套终极解决方案将带你从零开始，在MySQL中搭建包含省、市、县、乡、村的五级联动数据系统。无需复杂技术背景，只需简单几步就能完成！🚀

🎯 快速开始：环境配置与项目初始化

第一步：获取项目源码并安装依赖

git clone https://gitcode.com/gh_mirrors/ad/Administrative-divisions-of-China cd Administrative-divisions-of-China npm install

第二步：了解项目结构

项目主要包含以下关键模块：

数据抓取模块：lib/crawler.js- 负责从官方数据源获取最新行政区划信息
数据格式化模块：lib/format.js- 对原始数据进行清洗和标准化处理
导出工具：lib/export.js- 生成多种格式的数据文件
SQLite数据库：lib/sqlite.js- 提供本地数据存储和查询功能

📊 数据库设计与表结构规划

创建MySQL数据库

CREATE DATABASE china_administrative_divisions; USE china_administrative_divisions;

设计五级数据表

-- 省级行政区划表 CREATE TABLE provinces ( code VARCHAR(6) PRIMARY KEY, name VARCHAR(50) NOT NULL ); -- 地级市表 CREATE TABLE cities ( code VARCHAR(6) PRIMARY KEY, name VARCHAR(50) NOT NULL, province_code VARCHAR(6), FOREIGN KEY (province_code) REFERENCES provinces(code) ); -- 区县级表 CREATE TABLE counties ( code VARCHAR(6) PRIMARY KEY, name VARCHAR(50) NOT NULL, city_code VARCHAR(6), province_code VARCHAR(6), FOREIGN KEY (city_code) REFERENCES cities(code), FOREIGN KEY (province_code) REFERENCES provinces(code) ); -- 乡镇街道表 CREATE TABLE towns ( code VARCHAR(9) PRIMARY KEY, name VARCHAR(50) NOT NULL, county_code VARCHAR(6), city_code VARCHAR(6), province_code VARCHAR(6), FOREIGN KEY (county_code) REFERENCES counties(code), FOREIGN KEY (city_code) REFERENCES cities(code), FOREIGN KEY (province_code) REFERENCES provinces(code) ); -- 村级表 CREATE TABLE villages ( code VARCHAR(12) PRIMARY KEY, name VARCHAR(50) NOT NULL, town_code VARCHAR(9), county_code VARCHAR(6), city_code VARCHAR(6), province_code VARCHAR(6), FOREIGN KEY (town_code) REFERENCES towns(code), FOREIGN KEY (county_code) REFERENCES counties(code), FOREIGN KEY (city_code) REFERENCES cities(code), FOREIGN KEY (province_code) REFERENCES provinces(code) );

🔄 数据导出与迁移流程

生成CSV格式数据

使用项目提供的导出脚本：

./export_csv.sh

这个脚本会生成五个层级的CSV文件，存储在dist/目录下：

provinces.csv- 省级行政区划数据
cities.csv- 地级市数据
counties.csv- 区县级数据
towns.csv- 乡镇街道数据
villages.csv- 村级数据

批量导入MySQL数据

-- 导入省级数据 LOAD DATA LOCAL INFILE 'dist/provinces.csv' INTO TABLE provinces FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; -- 导入地级市数据 LOAD DATA LOCAL INFILE 'dist/cities.csv' INTO TABLE cities FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; -- 导入区县级数据 LOAD DATA LOCAL INFILE 'dist/counties.csv' INTO TABLE counties FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; -- 导入乡镇数据 LOAD DATA LOCAL INFILE 'dist/towns.csv' INTO TABLE towns FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; -- 导入村级数据 LOAD DATA LOCAL INFILE 'dist/villages.csv' INTO TABLE villages FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

✅ 数据验证与完整性检查

验证导入结果

执行数据统计查询：

SELECT '省级' as level, COUNT(*) as count FROM provinces UNION ALL SELECT '地级', COUNT(*) FROM cities UNION ALL SELECT '县级', COUNT(*) FROM counties UNION ALL SELECT '乡级', COUNT(*) FROM towns UNION ALL SELECT '村级', COUNT(*) FROM villages;

预期数据规模：

📍 省级：34个行政单位
🏙️ 地级：334个城市
🏘️ 县级：2851个区县
🏡 乡级：约4万个乡镇街道
🏠 村级：约66万个村居委会

🚀 实战应用场景

场景一：地址联动选择

-- 获取北京市的所有区县 SELECT * FROM counties WHERE city_code = '1101'; -- 获取朝阳区的所有街道 SELECT * FROM towns WHERE county_code = '110105'; -- 获取某个街道下的所有社区 SELECT * FROM villages WHERE town_code = '110105001';

场景二：数据统计分析

-- 统计各省份城市数量排名 SELECT p.name, COUNT(c.code) as city_count FROM provinces p LEFT JOIN cities c ON p.code = c.province_code GROUP BY p.code, p.name ORDER BY city_count DESC;

⚡ 性能优化策略

创建关键索引

-- 为常用查询字段创建索引 CREATE INDEX idx_cities_province ON cities(province_code); CREATE INDEX idx_counties_city ON counties(city_code); CREATE INDEX idx_towns_county ON towns(county_code); CREATE INDEX idx_villages_town ON villages(town_code);