省市区街道4级MySQL数据库：导入、查询与四级联动实战指南

简介一份面向MySQL开发者和数据分析师的省市区街道四级行政区域数据压缩包收录了全国省、市、区、街道的层级关系适用于地理信息系统、电商配送、市场分析和物流规划等场景。压缩包体积仅1.58MB共5个sql文件既提供省、市、区、街道四个独立的数据表也提供整合了全部层级的单表结构用户可根据实际业务选择导入方式。该数据在CSDN平台已有837人学习/下载适合需要快速获得行政区域基础数据、降低采集整理成本的开发测试项目。数据表字段结构清晰导入即可用于地址解析、区域筛选、配送范围划分等功能同时提示用户留意行政区划可能随政策调整建议定期更新及合规使用。整体来看这是一份小巧实用的数据资源能为涉及地理位置信息的应用快速搭建区域数据基础。1. 这份省市区街道4级MySQL数据解决的是四级联动和区域统计的重复劳动做物流、电商还是统计报表只要业务用到地区就绕不开一套完整的省市区街道4级MySQL数据。自己抓区划代码回来是PDF和Excel清洗对齐就得折腾一两天而rar形式的MySQL数据包解压后直接是SQL或CSV导入就能支撑四级联动和区域统计是最常见的落地方式。先说结论这套数据能不能用关键在导入前的编码体检和导入后的查询口径。很多人拿到包看到中文变成问号就放弃其实用对字符集和导入命令十分钟就能装进MySQL剩下的时间都花在确认街道级是否齐全、能不能覆盖“市下面直接挂街道”的特殊区划。它适合做省市区三级联动、订单汇总、运费配置的MySQL使用者。下面按“解压体检—导入—表结构校验—避坑—进阶查询”讲透整条链路。2. 解压与数据体检先搞清楚包里是什么再决定怎么导2.1 包里通常是什么一张region表还是多张分级表rar解压之后常见两种形态一种是单个SQL文件建表语句和INSERT都在一起另一种是CSV或TXT配一个建表SQL。无论哪种形态核心字段基本逃不出这几个code行政区划代码、name区划名称、level层级、parent_code父级编码。有的包把code统一补齐到12位有的从2位写到6位就停长短不一。数据量级方面全国目前约300多个地级行政区、2800多个县级行政区、3.8万个左右的乡镇和街道。如果你解压出来的SQL只有几千行那基本可以肯定只覆盖到区县级街道数据缺失第5章会专门讲怎么快速识别这种残缺包。这里要注意一个细节先确认包是一张表还是多张表。一张大表带level字段查询方便但数据冗余四张小表省、市、区、街道各一张结构清晰但关联麻烦。我一般推荐先转成一张大表再入库理由很简单行政区划代码本身就是前缀编码父子关系天然蕴含在code里不需要靠表之间的外键来维护。多张表的包在导入后还要做UNION合并多一步就多一个出错点。2.2 字符集和排序规则是第一个分水岭拿到SQL文件先别急着双击在命令行先跑两步。第一步用file看文件编码第二步用head看前几十行内容确认中文不是乱码file /data/region/region.sql head -n 50 /data/region/region.sqlfile输出像“ISO-8859 text”或者“UTF-8 Unicode text”时只能作为参考最终以head看到的中文为准。如果是Windows下导出的数据SQL文件大概率是GBK或GB2312编码在MySQL里直接跑会变成问号。常见做法是转成UTF-8再导入iconv -f GBK -t UTF-8 /data/region/region.sql /data/region/region_utf8.sqliconv的两个参数分别是源编码和目标编码。源编码拿不准时把文件拖进VS Code看右下角状态栏它会直接显示当前编码。转码完成后再执行一次head确认才能进入导入环节。如果rar解压后是多个SQL文件批量转码用一条for循环就行mkdir -p /data/region/utf8 for f in /data/region/*.sql; do iconv -f GBK -t UTF-8 $f /data/region/utf8/$(basename $f) done这条循环逐个读取目录下的SQL文件iconv转码后输出到utf8子目录文件名保持不变。好处是原文件不动转坏了还有后悔药。排序规则同样影响导入。MySQL 8.0默认排序规则是utf8mb4_0900_ai_ciMySQL 5.7最高只到utf8mb4_general_ci。如果SQL文件里写死了0900在5.7上直接报错。反过来老包用general_ci在5.7和8.0上都能导入但重新建库时建议统一升级到当前版本的默认值。这一点在排查“为什么5.7导不进去”时非常常见。2.3 SQL文件头三步检查法建库语句、DROP语句、字段定义考虑后续维护导入前再看三处细节。第一处是文件开头有没有CREATE DATABASE和USE有的话注意库里是否已有同名表第二处是有没有DROP TABLE一些年份包会在建表前先删掉旧表如果你导入的目标库里已有一份线上数据DROP会把老表直接清掉第三处是CREATE TABLE的字段定义重点看code字段是CHAR(12)、VARCHAR(12)还是VARCHAR(20)有没有主键和唯一索引。这三处决定了后面建表是否要手工调整。如果包内自带DROP TABLE我一般会先把它改成CREATE TABLE IF NOT EXISTS或者干脆把DROP行删掉。多花一分钟少踩一个“误删线上表”的坑。Navicat、Workbench这类可视化工具也不会帮你拦截DROP所以这一步跟用什么工具无关纯看文件内容。提示如果rar包在Linux下解压后中文文件名乱码可以在Windows下解压一次或者用支持中文编码的7-Zip处理。文件名乱码不影响SQL内容但会影响你区分不同年份的包。3. 把SQL真正导入MySQL命令行、source、可视化工具三条路3.1 用mysql命令行重定向导入最稳妥的一条路无论你是Linux直接装的MySQL、Docker容器还是Windows本地安装字符集设置逻辑完全一致。命令行导入是我最推荐的方式错误信息最直接进度也最透明。先创建目标库再通过重定向把SQL文件喂给mysql客户端mysql -uroot -p \ -e CREATE DATABASE IF NOT EXISTS region_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; mysql -uroot -p --default-character-setutf8mb4 region_db /data/region/region_utf8.sql第一条命令里“-uroot -p”表示以root身份连接密码在交互提示中输入避免明文写在命令里COLLATE选utf8mb4_general_ci是为了兼容MySQL 5.7和8.0如果你的服务端是8.0且不需要兼容旧版本用默认的utf8mb4_0900_ai_ci也成立。第二条命令“--default-character-setutf8mb4”是关键参数它让客户端按UTF-8解析SQL文件里的中文字节流否则MySQL会拿默认字符集去解读文件结果同样是乱码。如果SQL文件里已经带了CREATE DATABASE和USE第二条命令甚至可以省略库名。但我还是建议显式指定region_db因为有些数据包里的USE写的是“test”或“backup”会把表建到别的库里后续找半天。MySQL安装教程里常说“配置好字符集再启动”在这个场景完全适用。启动参数里没指定character_set_server时建库语句里的DEFAULT CHARACTER SET会兜底所以建库这条命令别省。Docker部署MySQL也一样SQL文件先进容器再执行“mysql file.sql”字符集参数不要落。3.2 用source命令在mysql客户端里跑适合边看日志边排查命令行重定向的缺点是如果SQL文件有一次语法错误整包数据会中途停掉错误信息不够直观。另一种方式是进入mysql客户端后用SOURCE命令逐段执行USE region_db; SET NAMES utf8mb4; SOURCE /data/region/region_utf8.sql;SOURCE是mysql客户端的内部命令不是SQL语句结尾不需要分号。执行前先“SET NAMES utf8mb4”把当前会话的客户端、连接和结果集字符集统一成UTF-8这一步和重定向方式的“--default-character-set”作用一致。SOURCE执行时会逐行打印每条SQL语句的结果看到ERROR 1064这类语法错误时能定位到具体行号方便回SQL文件里查问题。需要注意SOURCE里写绝对路径Windows下路径分隔符建议用左斜杠“/data/region/region.sql”而不是反斜杠避免转义麻烦。如果SQL文件体积在20MB以上SOURCE过程屏幕会快速滚动看起来像卡死其实是在正常执行耐心等提示符回到“mysql”即可。SOURCE执行到一半失败时不用急着从头跑先DROP掉半成品表再重新SOURCE比在原有数据上叠加重放要干净。3.3 Navicat和Workbench的可视化导入两个必须注意的设置许多人习惯用Navicat跑SQL右键目标数据库选“运行SQL文件”弹窗里把文件编码改成UTF-8再开始。Navicat这里有个坑如果SQL文件较长默认的“继续运行”模式遇到错误时会跳过报错继续执行结果就是导入完成但数据缺了一片。我一般会先把选项改成“遇到错误时停止”宁可先失败也不要带着残缺数据上线。MySQL官方Workbench路径不同它通过“Server → Data Import → Import from Self-Contained File”导入导入前需要先手工建好目标库。Workbench对几十MB的SQL文件处理效率不高进度条经常停在95%不动这不是死锁是慢。第二次导入时建议直接切命令行省得等。可视化工具只是入口不同到了服务器端SQL还是那份SQL所以排查问题永远回到命令行看错误日志。3.4 如果包里是CSV而不是SQLLOAD DATA导入有的rar包只有CSV加一份建表说明没有现成SQL。这种情况先建库再手工建表然后LOAD DATA导入CSVmysql -uroot -p --local-infile1 region_dbLOAD DATA LOCAL INFILE /data/region/region.csv INTO TABLE region CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (code, name, level, parent_code);“LOCAL”关键字表示文件在客户端机器上文件在服务器上则可以去掉LOCAL“FIELDS TERMINATED BY ,”定义CSV列分隔符“OPTIONALLY ENCLOSED BY ”处理被双引号包住的中文字段比如“滨江街道办事处”这种含逗号的名称“IGNORE 1 LINES”跳过表头。最后括号里的字段顺序必须和CSV列顺序一致引号里的字段名要和CREATE TABLE完全对得上。LOAD DATA是批量导入速度比INSERT快很多几万行街道数据几秒就完事。注意MySQL 8.0默认开启secure-file-priv限制LOAD DATA LOCAL会被拦。启动参数加“--local-infile1”或者在客户端session里先“SET GLOBAL local_infile1”再连接才能正常执行。这个报错信息是“ERROR 3948”看到这个错就知道是local_infile权限没开。3.5 导入完成后的三分钟体检导完别急着写接口先用三条SQL给数据做个体检USE region_db; SELECT level, COUNT(*) AS cnt FROM region GROUP BY level; SELECT COUNT(*) AS total, COUNT(DISTINCT code) AS uniq FROM region; SELECT * FROM region WHERE code LIKE %00 AND level 4 LIMIT 5;第一条统计每个层级的记录数正常情况四级都有数据且数量级符合前面说的“300地级、2800区县、3.8万街道”。第二条对比总行数和去重后的code数两者不一致说明有重复编码。第三条找code以00结尾的level4记录这类“XX00”结尾的条目多半是统计虚节点需要人工确认。三步走完导入环节才算真正收口。4. 导入后马上要做的三件事字段约束、索引、查询口径4.1 行政区划的code规则这张表的唯一秩序行政区划代码不是随便编的数字串它本身就带层级。省一级是2位比如33表示浙江地级市在省码后加2位3301就是杭州区县再加2位330108是滨江区乡镇街道再加3位330108001是街道一级。所以code长度天然标识层级2位省级、4位地市、6位区县、9位乡镇街道。正因为这种前缀编码规则查询完全不需要parent_code字段LIKE前缀匹配即可。查浙江所有地市“code LIKE 33% AND level2”就能全取出来查杭州市所有区县“code LIKE 3301% AND level3”。这种查询在MySQL里可以利用索引做前缀扫描几万行的表上毫秒级返回。很多包把code统一补齐成12位短的后面补0。碰到这种情况先判断用的是“补齐到12位”还是“自然长度”。我更习惯保留自然长度用LENGTH(code)配合CASE当层级口径SELECT code, name, level, CASE LENGTH(code) WHEN 2 THEN 省 WHEN 4 THEN 市 WHEN 6 THEN 区县 WHEN 9 THEN 乡镇街道 END AS level_name FROM region LIMIT 10;这条SQL里的CASE就是可执行的层级映射规则即使包里level字段标错也能靠code长度兜底。实际项目里我见过level字段标成“1省 2市 3区 4街道”和“0省 1市 2区 3乡镇”两种完全不同的口径用code长度做二次校验能避免很多口径不一致造成的返工。4.2 必加的字段、索引和类型选择如果数据包自带的建表语句字段定义比较简陋比如code直接写成VARCHAR(20)还没加索引建议导入后立刻调整。下面是我建议的最小建表模板CREATE TABLE region ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增ID, code VARCHAR(12) NOT NULL COMMENT 行政区划代码2/4/6/9位不等, name VARCHAR(100) NOT NULL COMMENT 区划名称, level TINYINT UNSIGNED NOT NULL COMMENT 1省 2市 3区县 4乡镇街道, status TINYINT NOT NULL DEFAULT 1 COMMENT 1启用 0停用, PRIMARY KEY (id), UNIQUE KEY uk_code (code), KEY idx_name (name), KEY idx_level_code (level, code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT省市区街道四级行政区划;字段选择上code用VARCHAR而不是CHAR主要考虑到包里可能是补零后的12位也可能是4位、6位自然长度VARCHAR按实际内容存储不用为定长补齐操心。name留到VARCHAR(100)因为“某某经济技术开发区管理委员会”这类名字不算短。level用TINYINT就够省得INT白占字节。索引是这张表性能的关键。uk_code唯一索引同时干两件事约束code重复、加速按code精确查idx_level_code组合索引覆盖“某层级下按编码排序”的查询也就是“WHERE level2 ORDER BY code”这种最常见的下拉框场景idx_name索引给按名称搜索预留路径。这张表是只读为主的基础数据表写入成本可以忽略索引建得越全越划算。注意如果原SQL文件里的表没有唯一索引导入后再加索引来得及。先查重有重复就去重否则UNIQUE KEY建不上去。这一步做完才算把“数据不会越用越脏”的底线守住。4.3 用status字段给区划变更留后路行政区划不是一成不变的撤县设区、镇改街道、合并村社每年都有微调。数据包里如果只有code、name、level三个字段一旦区划调整老code就只能物理删除历史的订单和报表会立刻失去关联。常见做法是保留status字段停用的区划置0查询默认带“status1”历史数据仍然能通过旧code查出来。第6章的版本化管理是这种思路的升级版。5. 省市区街道数据导入MySQL的避坑指南6个典型翻车现场5.1 导入后中文全是问号先查文件编码再查会话编码现象SELECT查出来的名称全是“????”或者“浣犲ソ”这类乱码count正常但显示乱。原因两层编码有一层对不上。第一层是SQL文件本身是GBK却被按UTF-8解析第二层是文件是UTF-8但mysql客户端会话用了latin1。很多人只改一层改完还是乱开始怀疑MySQL安装有问题其实问题不在安装。解决先filehead确认文件编码非UTF-8用iconv转导入时命令行带“--default-character-setutf8mb4”或客户端先执行“SET NAMES utf8mb4”再SOURCE。两层都对了乱码问题基本消失。如果已经导入成乱码不用重导但用CONVERT来回转换比重新导入还慢不如清空表重新走一遍导入流程。5.2 东莞、中山这种“不设区的市”四级联动中间断档现象做省市区三级联动选广东省、选东莞市下拉框里区县列表竟然是空的再一看街道直接挂在东莞市下面。原因东莞、中山、儋州等城市不设区县行政区划代码里市后面直接是乡镇和街道没有“区”这一级。如果联动的SQL写死“省→市→区→街道”到这直接就断档。解决查询时先判断市下面有没有区县记录若“SELECT COUNT(*) FROM region WHERE code LIKE 4419% AND level3”结果为0就把该市下面level4的记录直接当第三级返回。更通用的做法是前端展示时按层级动态伸缩city下面有district就下钻没有就把街道补上来。这种“市下面直接是街道”的情况不是脏数据是区划制度本身如此做接口时就要考虑进去。5.3 导入时外键约束卡死先关外键检测现象导入SQL时报“ERROR 1215 (HY000): Cannot add foreign key constraint”整表导入失败。原因rar包里的建表语句可能包含外键比如区表关联市表、街道表关联区表建表顺序稍有不对外键就建立失败。严格来说基础数据表根本不需要外键约束外键是给线上交易系统的字典表不需要。解决导入前执行“SET FOREIGN_KEY_CHECKS0”关掉外键检查导入完成后再恢复为1。命令行场景分解成三步SET FOREIGN_KEY_CHECKS 0; SOURCE /data/region/region_utf8.sql; SET FOREIGN_KEY_CHECKS 1;关闭外键后即使建表语句顺序颠倒也能把表建完。我一般不会给region表加外键理由很简单区划数据经常整体替换外键只会让替换过程多一层麻烦。加了外键反而是在给自己以后找事。5.4 同一个code出现两条多半是混入了不同年份的快照现象第3.5的体检里“COUNT(*)”和“COUNT(DISTINCT code)”对不上比如330108001出现两条一条叫“浦沿街道”一条叫“浦沿镇”。原因数据包制作者合并了不同年份的区划快照老版本里的镇还没改成街道新版本里已经改了。两个版本拼在一起同一个code就出现了两条。解决先定位重复范围再保留最新年份的条目SELECT code, name, level, COUNT(*) FROM region GROUP BY code HAVING COUNT(*) 1 ORDER BY code;确认哪些code重复后再用业务日期或者自增ID作为排序条件保留每组的最后一条。处理完务必把UK唯一索引建上去否则下次整体替换还会再犯。这里总结一句话加了唯一索引等于给表上了一层物理防盗门。5.5 MySQL 5.7导不进去“utf8mb4_0900_ai_ci”排序规则不兼容现象MySQL 5.7上执行建表语句报“Unknown collation: utf8mb4_0900_ai_ci”。原因MySQL 8.0引入的排序规则5.7不认识。现在的新包很多在MySQL 8.0里导出直接给5.7用就会翻车。反过来5.7导出的包在8.0里跑没这个问题。解决两条路。一是把SQL文件里的0900替换成5.7认识的utf8mb4_general_cised -i s/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g /data/region/region_utf8.sql二是干脆把数据导入MySQL 8.0再导出让开发、测试、生产环境都保持8.0版本一劳永逸。排序规则的差异提醒了建库时要明确版本Docker部署MySQL也一样容器镜像的版本直接影响SQL文件能不能跑起来。5.6 数据里夹着“市辖区”“省直辖县级行政区划”这类虚拟节点现象北京市下面多出一条“市辖区”海南省下面多出一条“省直辖县级行政区划”前端四级联动时感觉层级多了一层。原因统计口径里的虚拟父节点方便把“不直接归属某个地市”的县级单位挂起来。这类节点的code通常以00结尾比如110100、469000名字就叫“市辖区”或“省直辖县级行政区划”。解决处理前先看清业务要什么。如果做发货地址下拉框这些虚拟节点用户选不到过滤掉比较合理如果做报表统计这些节点的数据还要继续下钻不能简单删除。实操上可以用“name IN (市辖区,省直辖县级行政区划) OR code LIKE %00”配合白名单过滤但要注意普通城市的code也可能以00结尾准确做法是先看父级关系再确认。这条是最容易让新人跟产品来回扯皮的坑提前说清口径能省掉后面一大把沟通成本。6. 把四级数据用起来三级联动、前缀聚合和区划变更版本化6.1 不靠parent_id的三级联动一条LIKE走到底接手老系统时常见到region表带parent_id但这类字典表靠code前缀就能完成全部联动。后端接口只需要四个参数province_code、city_code、district_code、street_code每次查询都按“前缀层级”过滤-- 查浙江省所有地市 SELECT code, name FROM region WHERE code LIKE 33% AND level 2 ORDER BY code; -- 查杭州市所有区县 SELECT code, name FROM region WHERE code LIKE 3301% AND level 3 ORDER BY code; -- 查滨江区所有街道 SELECT code, name FROM region WHERE code LIKE 330108% AND level 4 ORDER BY code;LIKE 3301%配合idx_level_code索引扫描范围只有3301这一小段量级在个位到几十行性能没有压力。“ORDER BY code”排序正好按行政区划代码的自然顺序前端下拉框不需要再做额外排序。6.2 按省、市、县做区域聚合统计业务常见需求统计每个省份下面的街道数、每个地级市下面的区县数用来验证数据完整性。这时用LEFT截取前缀做GROUP BY一条SQL就能出报表SELECT LEFT(code, 2) AS province_code, COUNT(*) AS street_cnt FROM region WHERE level 4 GROUP BY province_code ORDER BY street_cnt DESC;这条语句把全国乡镇街道按省份聚合结果直接对得上官方发布的行政区划统计。LEFT(code, 2)截取省份前缀“WHERE level 4”把统计对象限定在街道级不会把省市区混进去。如果要统计每个地市下面的街道数把LEFT改成4位即可。这种写法在千万级业务流水表上同样适用用流水表里的地区码LEFT截取再JOIN region表就能按省市区汇总订单量。6.3 区划变更的版本化管理第4.3节提过status字段如果对历史数据要求更高可以在此基础上做版本化。增加一张region_version表每次拿到新的年份包version_id加1整包写入全部带effective_date。这样“2023年的订单属于哪个区”可以按订单时间回查而不是拿最新区划去解释历史数据CREATE TABLE region_version ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(12) NOT NULL COMMENT 区划代码, name VARCHAR(100) NOT NULL COMMENT 区划名称, level TINYINT UNSIGNED NOT NULL COMMENT 1省 2市 3区县 4乡镇街道, version_id INT UNSIGNED NOT NULL COMMENT 数据版本批次, effective_date DATE NOT NULL COMMENT 生效日期, expire_date DATE NULL COMMENT 失效日期NULL表示仍生效, UNIQUE KEY uk_code_ver (code, version_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT行政区划版本表;查询某笔历史订单的区划时带上日期条件SELECT * FROM region_version WHERE code 330108 AND effective_date 2024-01-01 AND (expire_date IS NULL OR expire_date 2024-01-01);“effective_date 订单日期 AND expire_date 订单日期”这种闭区间写法是版本表查询的标准姿势。每次更新数据包时先把上一版本的所有expire_date补齐再插入新版本这套逻辑稳定之后报表对账就不会再被“撤县设区”这类调整打乱。说一个我的习惯每次拿到新的省市区街道4级MySQL数据包导完不直接上线先用“name LIKE %街道街道% OR name LIKE %区区%”扫一遍拼接脏数据再抽查东莞、中山这类直筒子市最后把四级count和官方口径对一遍三件事做完才敢交付接口。这套检查做完才敢说这份数据是真的能用于生产。希望帮到你。本文还有配套的精品资源点击获取