简介面向需要行政区划地理数据的开发者与数据分析人员这份资源提供全国省、市、区三级经纬度信息的 MySQL 数据表可直接导入数据库用于地址解析、地图打点、区域统计等场景省去逐级手工整理坐标的繁琐。压缩包共 1 个文件为单个 sql 脚本体积约 127KB导入后即可得到结构化的省市区层级与对应经纬度字段便于按层级关联查询或与业务表做区域维度关联。已有 2998 人学习下载说明其在同类数据需求中具备一定参考价值。数据以 SQL 形式组织字段清晰、层级完整适合直接建表使用也可作为二次加工的基础数据源帮助读者快速搭建区域维度表、支撑按省市区的聚合分析与可视化展示。1. 一份能直接灌进 MySQL 的全国行政区划经纬度数据做本地生活、门店选址、物流分单这类项目绕不开一个基础问题给一个省市区名称怎么快速拿到它的中心经纬度或者反过来给一个坐标怎么判断落在哪个区。很多人第一反应是调地图开放接口但接口有配额、有网络依赖、有商用授权问题离线跑批的时候尤其难受。这份资源就是冲着这个痛点来的——一份覆盖全国省、市、区三级行政区划的经纬度数据整理成可以直接导入 MySQL 的结构建表、导入、写 SQL 查询三步就能在自己的库里跑起来。适合正在做地址库、区域统计、地图打点、配送范围计算的开发者尤其是那种不想在项目里塞一个几百 MB 的 GIS 依赖、只想用一张表解决问题的人。下面我按拿到数据后真实的处理顺序把表结构、导入、查询和二次改造讲一遍。2. 数据结构拆解省市区三级怎么落成一张表2.1 先看清字段编码、名称、层级、经纬度这类数据最常见的组织方式是一张自关联的行政区划表核心字段就那么几个行政区划编码、名称、父级编码、层级、经度、纬度。编码一般遵循国家标准的分段规则省级两位、市级四位、区县级六位前几位是父级前缀这个规律在后面做模糊匹配和层级推导时非常有用。经纬度字段要注意精度用DECIMAL(10,6)存比较稳妥六位小数大约到 0.1 米级别对行政区中心点来说绰绰有余用FLOAT也能跑但做距离计算和聚合统计时容易出现尾差血泪经验是这种基础数据别省那点存储。下面是我一般会用的建表语句字段命名保持直白方便后面写查询的人一眼看懂CREATE TABLE region ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, code CHAR(6) NOT NULL COMMENT 行政区划编码不足6位左补0, name VARCHAR(64) NOT NULL COMMENT 行政区名称, parent_code CHAR(6) NOT NULL DEFAULT 0 COMMENT 父级编码省级为0, level TINYINT NOT NULL COMMENT 层级1省 2市 3区县, lng DECIMAL(10,6) DEFAULT NULL COMMENT 中心点经度, lat DECIMAL(10,6) DEFAULT NULL COMMENT 中心点纬度, PRIMARY KEY (id), UNIQUE KEY uk_code (code), KEY idx_parent (parent_code), KEY idx_level (level) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT全国省市区经纬度;几个参数值得说明。code用CHAR(6)而不是VARCHAR因为长度固定定长在索引里更紧凑统一左补零到六位是为了让省、市、区在同一列里可以直接比较和做前缀匹配。parent_code省级填0这样查顶级节点就是一句WHERE parent_code 0不用额外判断level。level单独冗余一列看着有点重复但实际查询里按层级过滤的频率极高比如只取市级列表做下拉框有这个索引能省掉一次前缀计算。经纬度允许NULL是因为原始数据里个别区县可能缺中心点导入时不要强行填 0否则地图上会冒出一堆落在几内亚湾的点这个坑后面还会提。2.2 层级关系与编码前缀的对应这张表本质是一棵三层的树但用编码前缀就能把树结构推导出来不一定非要递归查询。省级编码形如110000市级110100区县110101前两位是省、前四位是市。这意味着你可以用LEFT(code, 2)拿到省、LEFT(code, 4)拿到市做分组统计时非常顺手。比如统计每个省有多少个区县不需要 join 两次直接对区县行做前缀分组即可。不过要注意直辖市和部分特殊行政区的层级划分跟普通省份不完全一样有的数据里直辖市下面直接挂区县中间没有市级节点或者市级节点和省级同名。导入前最好先跑一遍分布检查看看每个level的行数是否符合预期全国省级三十多个、市级三百多个、区县两千八百个左右数量级对不上就说明数据里有脏行或者层级标错了。我一般会先建一张临时表全量导入用几条聚合 SQL 验一遍再决定要不要清洗比直接往正式表里灌安全得多。-- 导入后先做体检各层级数量分布 SELECT level, COUNT(*) AS cnt FROM region GROUP BY level; -- 检查有没有父级编码找不到对应父节点的孤儿行 SELECT c.code, c.name, c.parent_code FROM region c LEFT JOIN region p ON p.code c.parent_code WHERE c.parent_code 0 AND p.code IS NULL;第一条查层级分布数量明显偏离常识就回去看原始文件是不是混进了街道级数据或者重复行。第二条查孤儿节点parent_code指向一个不存在的编码这种行在后续做级联查询时会凭空消失属于典型的静默错误越早发现越好。真出现了要么是父级数据缺失要么是编码补零没做对比如原始文件里省级编码是11而不是110000导入时没统一格式就会对不上。2.3 索引怎么建才够用这张表的数据量不大三千行上下理论上全表扫描也就毫秒级但真实项目里它经常被高频调用比如每次下单都要根据收货地址反查区域这时候索引就有意义了。上面建表语句里给了三个索引uk_code保证编码唯一同时支撑按编码精确查idx_parent支撑「查某省下所有市」这类级联查询idx_level支撑按层级拉列表。经纬度字段我没建索引因为这张表不是拿来做范围检索的主力真要按坐标找最近区域三千行全扫加一次距离排序完全扛得住为它建空间索引反而增加维护成本。如果业务里确实有大量「给坐标找区域」的请求可以考虑加一个POINT类型的生成列配合空间索引但那是另一个量级的改造普通项目用不上。我的判断标准很简单这张表一天被查多少次、单次能不能接受 5 毫秒。绝大多数场景下三千行的表加三个普通索引QPS 上千都不会成为瓶颈别过度设计。3. 导入与查询实战从 CSV 到可用的地址库3.1 导入前的格式对齐拿到数据文件后第一件事不是急着LOAD DATA而是先确认分隔符、编码和字段顺序。这类数据常见的是 CSV 或 SQL 导出文件CSV 要注意有没有 BOM 头有 BOM 的话第一列字段名会带一个不可见字符导入时报「未知列」错误排查半天以为是编码问题。用utf8mb4建库建表文件本身也确认是 UTF-8能避开绝大多数乱码。字段顺序如果和建表顺序不一致导入时显式列出列名别依赖默认顺序。# 看一眼文件头确认分隔符和是否有 BOM head -n 3 region.csv | cat -Acat -A会把不可见字符显示出来行尾的^M说明是 Windows 换行BOM 会显示成M-oM-;M-?之类。发现^M就在导入语句里加LINES TERMINATED BY \r\n或者先dos2unix转一遍省得后面每行末尾多一个回车导致最后一列解析失败。这个检查花十秒钟能省掉半小时的玄学调试。3.2 用 LOAD DATA 批量灌数据数据量不大但用LOAD DATA LOCAL INFILE仍然是最省事的方式比写脚本逐行 insert 快得多也不用担心事务太大。前提是 MySQL 客户端和服务端都开了local_infile没开的话会报The used command is not allowed with this MySQL version去配置文件里把local_infile1打开重启即可。LOAD DATA LOCAL INFILE /path/to/region.csv INTO TABLE region CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (code, name, parent_code, level, lng, lat) SET code LPAD(code, 6, 0);逐段解释一下。CHARACTER SET utf8mb4明确告诉 MySQL 文件编码避免它按服务端默认字符集猜。OPTIONALLY ENCLOSED BY 处理名称里带逗号的情况比如某些区县名称含标点不加这个会被错误切分。IGNORE 1 LINES跳过表头。最后的SET code LPAD(code, 6, 0)是关键一步原始文件里编码可能是11、1101这种不补零的写法导入时统一补齐到六位保证前缀匹配和父子关联都能对上。如果原始数据编码本来就是六位这句加了也无害。导入完成后跑一遍第 2 章那两条体检 SQL数量对、没有孤儿行基本就可以用了。有重复编码的话uk_code会直接报错中断这其实是好事逼你回去看数据源是不是混了多份文件。3.3 三类高频查询写法数据进库之后日常用得最多的查询就三类我把它们整理成可以直接抄的形式。第一类是级联下拉先查所有省再根据省查市再根据市查区。-- 查所有省级 SELECT code, name FROM region WHERE level 1 ORDER BY code; -- 根据省编码查下辖市 SELECT code, name FROM region WHERE parent_code 110000 AND level 2 ORDER BY code; -- 根据市编码查下辖区县 SELECT code, name FROM region WHERE parent_code 110100 AND level 3 ORDER BY code;第二类是名称反查坐标用户输入「某区」要拿到经纬度做地图打点。名称可能重名比如多个城市都有「城关区」这类名字所以查询要带上父级上下文或者返回多条让上层做选择。-- 按名称模糊查带出父级名称辅助判断 SELECT r.code, r.name, r.lng, r.lat, p.name AS city_name, gp.name AS province_name FROM region r LEFT JOIN region p ON p.code r.parent_code LEFT JOIN region gp ON gp.code p.parent_code WHERE r.name LIKE %城关% LIMIT 20;这里用了两次自关联把市名和省名带出来方便调用方在重名时区分。LIMIT 20是防御性的模糊查询不加限制在名称特别短的时候可能扫出大量结果。第三类是坐标反查区域给定经纬度找最近的区县中心点用简单的球面距离近似即可三千行数据没必要上 GIS 函数。-- 找距离给定坐标最近的区县球面距离近似单位公里 SELECT code, name, 6371 * ACOS( COS(RADIANS(30.5)) * COS(RADIANS(lat)) * COS(RADIANS(lng) - RADIANS(120.1)) SIN(RADIANS(30.5)) * SIN(RADIANS(lat)) ) AS distance_km FROM region WHERE level 3 AND lng IS NOT NULL AND lat IS NOT NULL ORDER BY distance_km LIMIT 1;6371是地球平均半径公里数RADIANS把角度转弧度公式是标准的 haversine 简化版。注意WHERE里过滤了经纬度为NULL的行否则ACOS参数越界会返回NULL或者报错。这个查询在三千行上跑一次大概几毫秒如果调用频率高可以在应用层把结果缓存起来按坐标网格做一层本地缓存命中率会很高。4. 避坑与排查导入和使用中最容易翻车的几处4.1 经纬度填 0 导致地图打点全跑到海上现象是地图上大量标记点集中在赤道附近某片海域或者距离计算全部返回几千公里。原因是原始数据里缺失经纬度的行被导入脚本默认填了0而(0,0)在几内亚湾所有缺失点都堆在那里。解决办法是导入时缺失值保持NULL查询时用WHERE lng IS NOT NULL过滤或者在应用层对NULL做兜底展示。我一般会在导入后专门跑一条SELECT COUNT(*) FROM region WHERE lng 0 OR lat 0结果不为零就说明有脏数据混进来了。4.2 编码没补零导致父子关联断裂现象是级联查询到某一级就查不出下级或者孤儿行检查扫出一堆记录。原因是原始文件里省级编码写成11、市级写成1101而父级字段里存的又是补零后的六位两边对不上。解决方式就是导入语句里那句LPAD(code, 6, 0)同时确认parent_code也做了同样处理。如果数据已经导进去了用一条UPDATE region SET code LPAD(code,6,0), parent_code LPAD(parent_code,6,0)补救但要注意先删唯一索引或者确认不会冲突。4.3 名称里的特殊字符把 CSV 切错列现象是导入报列数不匹配或者某些行的名称字段串到了经纬度列里。原因是名称里含英文逗号、引号而导入语句没加OPTIONALLY ENCLOSED BY 或者文件本身引号转义不规范。解决方式是先加ENCLOSED BY参数重导如果文件引号本身有问题用脚本预处理一遍把字段内的引号转义成两个引号。这类问题在含少数民族语言地名或者带括号的名称里出现频率不低。4.4 字符集不统一导致中文变问号现象是导入后名称列显示成???或者乱码。原因通常是文件是 GBK 编码而表是utf8mb4导入时没声明字符集MySQL 按默认字符集解析。解决办法是导入语句里显式写CHARACTER SET文件是 GBK 就写gbk让 MySQL 做转换或者导入前用iconv把文件转成 UTF-8。建库建表、连接串、文件编码三处都统一成utf8mb4是最省心的做法。4.5 直辖市层级特殊导致下拉框空白现象是选了某个直辖市之后市级下拉框是空的。原因是这类数据里直辖市可能没有独立的市级节点区县直接挂在省级下面parent_code指向省级编码而不是市级。解决方式是在应用层做兼容查下级时先按parent_code查查不到再按LEFT(code, 2)前缀查一次或者在导入阶段给直辖市补一个虚拟市级节点。我倾向于在查询层兼容不动原始数据保持数据本身的真实性。5. 二次开发把静态表变成可维护的地址服务这份数据最大的价值不是「有」而是「能改」。真实项目里行政区划会调整新增区县、改名、合并都发生过静态 CSV 用一年就可能过时。所以二次开发的第一件事是给这张表加一套可维护的更新机制而不是每次变更都重新导全量。我的做法是加两个字段updated_at记录最后更新时间status标记启用/停用这样历史编码可以保留避免老订单里的区域编码突然查不到。更新时按code做 upsert用INSERT ... ON DUPLICATE KEY UPDATE一条语句搞定增量同步。INSERT INTO region (code, name, parent_code, level, lng, lat, updated_at) VALUES (110101, 某区, 110100, 3, 116.4, 39.9, NOW()) ON DUPLICATE KEY UPDATE name VALUES(name), lng VALUES(lng), lat VALUES(lat), updated_at VALUES(updated_at);VALUES()函数在较新版本里被标记为待废弃可以换成别名写法AS new ON DUPLICATE KEY UPDATE name new.name看你的 MySQL 版本决定。这套 upsert 配合一个定时任务把上游变更文件按code比对后逐条同步就能做到不停机更新。第二个改造方向是把这张表包成一个轻量服务对外提供「名称转坐标」「坐标转区域」「按父级列下级」三个接口内部加一层本地缓存。三千行数据全量加载进内存也就几百 KB用HashMap按编码索引、按名称建倒排查询完全不走数据库QPS 轻松上万。缓存刷新用版本号或者定时重载数据变更频率低一天刷一次都够。这样数据库只作为持久化存储查询压力被挡在应用层是这类基础数据最经济的用法。第三个方向是补全边界数据。中心点经纬度只能做「大概在哪个区」的判断如果业务需要精确的配送范围或者行政区边界得另外引入边界多边形数据用点在多边形内的算法做判断。这时候中心点表可以作为第一层粗筛先用最近中心点缩小候选范围再对候选区做精确的边界判断性能比直接遍历所有多边形好一个量级。这个组合思路在门店归属、配送分区这类场景里很实用。验证改造是否成功我一般会准备一组固定的测试用例几个知名区县的名称查坐标、几个边界坐标查区域、几个重名区县查歧义处理每次更新数据后跑一遍结果和预期比对。这套用例不用多二三十条覆盖典型场景就够关键是每次变更都强制走一遍。从那以后我每次动这类基础数据表都先把体检 SQL 和测试用例跑完再上线吃过一次编码没补零导致线上级联查询全空的亏不想再吃第二次。希望帮到你。本文还有配套的精品资源点击获取