中国省市区MySQL数据表：行政区划码与经纬度设计实战

简介一份面向网站开发、移动应用与数据分析场景的中国行政区划MySQL数据表资源旨在解决省市区基础数据缺失、手工录入耗时、行政区划码与经纬度信息不全等常见问题适合后端工程师、数据运营者及地理信息系统爱好者直接使用。压缩包内共1个SQL文件大小仅约103KB提供可直接执行的建表语句与全量数据覆盖全国省级、地级、县级三级行政区划导入MySQL即可生成包含省份、城市、区县名称、行政区划码及经纬度的数据表并兼容命令行、Navicat、phpMyAdmin等常见导入方式无需二次转换。已有1928人浏览学习。数据表结构清晰、字段规范行政区划码采用国家统一编码便于与订单、人口、门店等业务数据关联经纬度字段支持地图打点、距离计算、地理围栏等操作可直接用于地图导航、物流配送、电商收货地址自动补全、区域统计、附近服务查询等场景帮助开发者大幅缩短数据准备周期快速搭建地理功能模块提升项目交付效率与数据准确性。1. 中国省市区城市列表 MySQL 数据表为什么值得直接入库中国省市区城市列表 MySQL 数据表是我在过去几个项目里反复用到的基础数据。做电商结算、CRM 地址字典、后台管理系统省市区联动的时候最常见的翻车方式就是每次都要调第三方接口拿省市区结果接口一限流整个注册页面就卡住更麻烦的是不同接口返回字段不一致今天叫 area_name明天叫 region_name前端根本没安全感。后来我换了一种做法把一份带行政区划码和经纬度的 SQL 表直接导入 MySQL地址字典全部走本地缓存前端需要 JSON 时再由 Python 脚本从表里转换。你得到的不只是一张表而是一个不依赖网络、可验证、可二次加工的行政区划基础服务。这份笔记适合正在做地图、订单、权限数据字典的后端和数据开发也适合想彻底甩掉第三方接口写死数据的团队。2. 为什么离线省市区表比实时接口更合适行政区划码与经纬度的字段设计2.1 行政区划码6 位编码的层级规律绝大多数省市区数据集都带行政区划码简称 areaCode。国内行政区划码采用 6 位数字前两位代表省级中间两位代表地市级后两位代表区县级例如市辖区、县、县级市的编码都能从这一位序列里拆出父级。这个规律决定了设计表结构时不能把行政区划码当成普通数字字段乱存它是天然的层级主键。正因为如此离线表比在线接口靠谱得多接口可能返回一个 label、一个 value但不一定给你标准编码而有了 areaCode你可以自己拼省市区树也可以和身份证前 6 位、统计口径、地图 GeoJSON 的 id 对起来。我一般会在建表时把 areaCode 明确标成CHAR(6)而不是INT。原因很简单很多下游系统要求“字符串”形态而且 Excel 导出的编码可能自带前导空格或文本格式如果源头是VARCHAR导入 MySQL 时能少一步清洗。需要区分城市层级时再加一个level_type用province / city / district或1 / 2 / 3都可以看你的项目习惯。用数字更省空间但字符串可读性更好我倾向于level_type存字符串因为数据量才几千行没必要为了几个字节牺牲排查效率。字段设计上一张靠谱的省市区表至少要有父级字段。父级字段直接决定了你能不能递归出树形 JSON。很多数据源为了省事故意省掉 parent_id只给你一套完整编码让前端自己去截字符串。这种数据做硬编码查询没问题但做“按市级查区县”“按省级聚合”就会很别扭。强烈建议拿到数据后先补一个parent_id它的值指向当前记录的上级记录。对省级记录可以把parent_id设成 0再把 0 当成虚拟根节点。表格一张可落地的 table 字段参考字段类型说明idBIGINT自增主键供程序内部引用parent_idBIGINT父级 id省级为 0nameVARCHAR(64)官方名称如“北京市”short_nameVARCHAR(64)简称如“北京”area_codeCHAR(6)行政区划码必须唯一level_typeVARCHAR(16)province / city / districtpinyinVARCHAR(64)全拼或简拼用于搜索lngDECIMAL(10, 6)经度中心点latDECIMAL(9, 6)纬度中心点sortINT同级排序建议与 area_code 一致2.2 经纬度字段精度、坐标系与实际精度陷阱经纬度字段看起来简单实际是最容易踩坑的地方。第一是精度DECIMAL(9, 6)足够存中国范围内的纬度经度用DECIMAL(10, 6)6 位小数折算到地面大概是 0.1 米左右做省市区中心点绰绰有余。不要把经纬度存成FLOAT因为浮点取整后会出很多尾差存一些重复计算时对不上的数据排查起来很费劲。第二是坐标系。这也是黑匣子最多的地方。同一个城市的经纬度在 WGS-84、GCJ-02、BD-09 三种坐标系下可能相差几百米甚至上公里。市面上的省市区城市列表数据很多来源是电子地图导出的 GCJ-02 坐标如果你拿去和 GPS 原始轨迹做距离计算就会看到位置漂移而如果拿 ArcGIS Pro 直接去叠加又需要投影转换。见到“带经纬度”的 SQL 文件时一定要先确认文本里有没有coord_type字段。源文件没有的话默认按 GCJ-02 处理通常更安全因为国内主流地图前端基本都用 GCJ-02。你可以在表里加一列coord_type默认填GCJ-02以后出接口时候告诉前端“这批坐标是 GCJ-02”省得两边互相甩锅。2.3 建表 SQL字段类型与索引设计建表 SQL 可以直接落在业务库里。这里我建议把数据库字符集统一成utf8mb4因为部分行政区划名称中会出现生僻字只靠utf8在某些字符集组合下存在风险。索引上重点覆盖两个查询路径一是按父级查子级二是按行政区划码精确查记录。所以parent_id和area_code都要有索引。CREATE TABLE IF NOT EXISTS region ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 内部主键, parent_id BIGINT NOT NULL DEFAULT 0 COMMENT 父级id省级为0, name VARCHAR(64) NOT NULL COMMENT 行政区划名称, short_name VARCHAR(64) DEFAULT NULL COMMENT 简称, area_code CHAR(6) NOT NULL COMMENT 行政区划码, level_type VARCHAR(16) NOT NULL COMMENT province/city/district, pinyin VARCHAR(128) DEFAULT NULL COMMENT 拼音/简拼, lng DECIMAL(10, 6) DEFAULT NULL COMMENT 经度, lat DECIMAL(9, 6) DEFAULT NULL COMMENT 纬度, sort INT NOT NULL DEFAULT 0 COMMENT 同级排序, PRIMARY KEY (id), UNIQUE KEY uk_area_code (area_code), KEY idx_parent_id (parent_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT中国省市区城市列表;这条建表语句把area_code设成了唯一键好处是导入时就能拦截重复编码把parent_id设成普通索引是为了支持按父级查询子节点的常用操作。sort字段不是给程序硬编码用的而是给外部导入脚本一个覆盖排序入口后面增量更新时可以直接更新这一列不影响业务表主键。提示建表后先别急着导数据。先确认你的 MySQL 版本默认字符集如果库本身是latin1即使建表写了utf8mb4后续从外部文件导入时也可能因为连接层转换出问题。3. 把 SQL 倒入 MySQL导入前调整、命令与数据校验3.1 先检查字符集再执行 source 导入从手工下载的.sql文件导入时最怕的不是 SQL 语法而是字符集和连接方式不对。先看你的登录命令用mysql -u root -p登录时MySQL 客户端默认连接字符集可能是latin1如果 SQL 文件本身是 UTF-8导入后中文名称会变成一排问号。如果你第一次在本机做先不说 mysql 安装配置教程只要服务能起来、能连上就行重点是用--default-character-setutf8mb4强制确认字符集。mysql -uroot -p --default-character-setutf8mb4 mydb region.sql在上面的命令里mydb是你业务数据库名region.sql是包含建表语句和 INSERT 语句的文件。加--default-character-setutf8mb4能同时解决两层问题一是让客户端读取文件时按 UTF-8 解析二是让连接层把语句按 UTF-8 发给服务端。如果你用的是 MySQL Workbench也可以用File - Open SQL Script打开文件后执行不过执行前一定要在 Session 里执行SET NAMES utf8mb4;否则同样会乱码。还有一类常见登录错误是ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock。这不是表的问题而是localhost走了 socket 文件但你的 MySQL 服务端没监听对应的 socket。这时可以改成走 TCP 协议命令里加-h 127.0.0.1 -P 3306。这样也方便后续从 Python 脚本按同一套 127.0.0.1 连接参数去读数据。3.2 用三条 SQL 确认数据没有“黑匣子”导入完不要急着SELECT *爱看多少看多少而是要面向数据质量控制去写查询。我会先执行三条 SQL第一条统计各级别记录数第二条检查孤儿节点第三条检查行政区划码重复。这三条能覆盖掉 90% 的“脏数据”问题。SELECT level_type, COUNT(*) AS cnt FROM region GROUP BY level_type; SELECT r.id, r.name, r.parent_id FROM region r LEFT JOIN region p ON r.parent_id p.id WHERE r.parent_id ! 0 AND p.id IS NULL; SELECT area_code, COUNT(*) AS cnt FROM region GROUP BY area_code HAVING cnt 1;第一条 SQL 用来确认省、市、区县三个层级的数量是否和预期一致。如果你导入的是“三级联动”结构省的数量应该在 30 出头但直辖市情况特殊很多数据集会把“市辖区”当成市级节点这个数量前后对不上不代表数据是坏的先看level_type的分布再下结论。第二条 SQL 是经典的孤儿节点检查它用LEFT JOIN把每条记录的parent_id和它上级的id做关联如果关联不上说明父级记录缺失。正常情况下结果应该是 0 行一旦有行后端递归生成 JSON 时就会直接 KeyError。第三条 SQL 检查重复编码重复的area_code会让下游在按编码合并数据时出现一对多结果不可信。3.3 处理重复区划分码与父节点缺失如果第二条 SQL 查出孤儿节点不要手动猜父级。常见原因是数据源里把“省直辖县级行政单位”处理成了直属省级的区县它们没有市级父级。此时最稳妥的做法是保留这些记录并把它们的parent_id指向省而不是强行插一个不存在的市。如果你需要前端三级联动可以在展示层临时把“市辖区”或“省直辖县级行政单位”自动补充为二级节点而不是去改源表。如果第三条 SQL 查出重复编码通常是因为同一行政区域在数据源里出现了两次比如一个level_type是province另一个level_type是city但area_code完全一样。处理办法不是直接删除而是先看重复记录的parent_id和nameSELECT area_code, name, parent_id, COUNT(*) AS cnt FROM region GROUP BY area_code, name, parent_id HAVING cnt 1;这个查询结果如果多条记录同编码同父级说明是纯重复导入可以保留一条如果同编码不同名字说明是行政区划改名造成的版本冲突需要按“名称 生效时间”来做版本管理而不是简单删掉。我见过太多因为强行去重把“市辖区”删掉最后前端三级联动缺节点的例子所以删数据前一定先备份原表。4. 从 MySQL 表转成 JSON手工导出与生成两层嵌套结构4.1 先用 SQL 拉平数据再拼接 JSON还是直接写脚本把省市区表转成 JSON是前后端联调里最常见的一步。很多技术人员第一反应是“直接在 MySQL 里用 JSON_OBJECT 函数拼”, 但实际做下来会发现如果只导出两层级联数据SQL 里拼 JSON 还能应付一旦要生成省、市、区县三层嵌套 JSONSQL 的递归查询在 MySQL 8.0 之前非常难写8.0 之后虽然有WITH RECURSIVE但要在递归里同时维护 JSON 嵌套结构代码可读性和调试成本都会上升。数据量只有几千行用 Python 脚本读取全表在内存里构建父子关系是最稳的做法。我一般不用 MySQL 直接输出最终 JSONMySQL 只负责把字段拉平Python 负责构建树。这个分工有一个明确的好处改字段名、加别名、转换 Decimal 类型都可以在 Python 里统一处理不需要反复改 SQL。而且生成的结果可以直接存成region.json给前端静态引用也让业务查询少一次数据库访问。4.2 用 Python 生成省市区 JSON 的完整脚本这里用pymysql连接 MySQL。先安装依赖然后执行下面脚本。你只需要把连接参数改成你自己的库名和密码脚本会输出一个省市区三级嵌套的 JSON 数组。import json import pymysql conn pymysql.connect( host127.0.0.1, port3306, userroot, passwordyour_password, databasemydb, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) rows [] with conn.cursor() as cursor: cursor.execute( SELECT id, parent_id, name, short_name, area_code, level_type, lng, lat FROM region ORDER BY parent_id, sort ) rows cursor.fetchall() conn.close() # 建立 id 到节点对象的映射 nodes {} for row in rows: row[children] [] row[areaCode] row.pop(area_code) row[level] row.pop(level_type) row[lng] float(row[lng]) if row[lng] is not None else None row[lat] float(row[lat]) if row[lat] is not None else None nodes[row[id]] row # 顶层虚拟节点 root {id: 0, name: 中国, children: [], level: root} nodes[0] root for row in rows: pid row[parent_id] if pid not in nodes: raise KeyError(fparent_id 缺失: {row[id]} - {pid}) nodes[pid][children].append(row) def clean(node): 去掉程序内部字段保留前端需要的 JSON 结构 safe { name: node[name], areaCode: node[areaCode], level: node[level], } if node.get(lng) is not None and node.get(lat) is not None: safe[lng] node[lng] safe[lat] node[lat] if node.get(children): safe[children] [clean(c) for c in node[children]] return safe result [clean(c) for c in root[children]] print(json.dumps(result, ensure_asciiFalse, indent2))这段脚本的逻辑分三步先把每行记录从area_code改成areaCode这种前端友好命名再把所有节点放进nodes字典里最后遍历每一行把节点挂到对应父级的children列表里。rows从 MySQL 查出后是DictCursor字典对象所以row[id]可以直接当作数组下标使用。需要注意两个参数连接里的charsetutf8mb4负责把中文从 MySQL 正确读出来json.dumps里的ensure_asciiFalse防止中文被转成\uXXXXindent2只是让人读起来方便生产环境可以把它改成紧凑输出文件体积更小。脚本里还有一个 KeyError 抛出逻辑如果数据里有孤儿节点脚本会直接告诉你编号不会静默产出缺父级的 JSON。4.3 输出 JSON 的字段约定areaCode、name、lng、lat、children最终 JSON 的顶层是一组省份对象每个对象里通过children挂市级市级再挂区县。示例如下[ { name: 北京市, areaCode: 110000, level: province, lng: 116.4074, lat: 39.9042, children: [ { name: 北京市, areaCode: 110100, level: city, children: [ { name: 东城区, areaCode: 110101, level: district, lng: 116.4188, lat: 39.9175 } ] } ] } ]前端拿到这个结构后三级联动可以直接递归渲染。串成 JSON 时建议保留level字段不要只看有没有children。因为很多“省直辖县级行政单位”没有市一级节点父级下直接挂区县如果前端用“有 children 就是市级”去判断会把县当成市。加了level之后前端可以根据level决定点击时的行为不容易受数据形态变化影响。5. 省市区表落地避坑5 个让我翻过车的排查记录5.1 导入后中文名称全是问号现象SELECT name FROM region查出来是???或ð¸这类乱码但数条数没问题。原因SQL 文件本身是 UTF-8但导入命令没指定--default-character-setutf8mb4客户端按latin1解析中文入库时已经变成乱码另一种可能是建表语句里字符集被建库时的默认值覆盖成了latin1。解决删掉表重新建执行导入命令时强制带上--default-character-setutf8mb4已经导入乱码的数据不要尝试 UPDATE直接重建表后重新导入因为数据库里存的已经是错误字节靠 SQL 改不回来。5.2 经纬度导成 null 或者 0前端地图上全落在非洲西海岸现象本地查数据看起来正常但前端地图组件把坐标画到经纬度 0,0 的位置也就是几内亚湾附近。原因源数据里没有为所有省市区补齐经纬度坐标。很多老数据集会漏掉“省直辖县级行政单位”或新版功能区这些记录在 SQL 文件中lng/lat为空或填了0。解决先执行SELECT area_code, name, lng, lat FROM region WHERE lng 0 OR lat 0 OR lng IS NULL OR lat IS NULL;找出缺失项。能接受人工补录的就按县域政府驻地坐标补不能接受的就明确告诉前端缺失坐标的记录不展示地图只展示名称。5.3 同一区划码出现两次count 比预期多几百行现象数据总量和官方公布数量对不上查area_code时同一个编码对应了两条记录且名字接近。原因数据集合并了历史快照和当前版本。比如某个区改名后新的区划码沿用了旧编码但数据源把旧名称也保留了下来造成重复。解决不要按GROUP BY area_code后直接删记录。先对比两条记录的parent_id、level_type、name如果是纯重复保留id较小的一条如果是新旧名称并存给表加version字段区分版本让业务查询时只取最新版本。5.4 同级城市排序每次都不一样输出了 JSON 也没稳定顺序现象前端请求省市区列表同一城市有时排前面有时排后面尤其是增量更新之后。原因查询时用了ORDER BY sort但新插入记录的sort默认是 0导致大量新记录并列数据库磁盘上读取顺序又受索引、并发写入影响查询结果不稳定。解决把排序字段从单一sort改成 “area_code优先 sort兜底”。因为area_code本身就有层级顺序同一个父级下子节点按编码排顺序是稳定的。查询 SQL 改成ORDER BY parent_id, area_code或ORDER BY parent_id, sort, area_code前端每次拿到的顺序可预期也方便后端缓存 JSON。5.5 Python 生成 JSON 时报 TypeErrorObject of type Decimal is not JSON serializable现象脚本读出的lng/lat在json.dumps时直接报错程序中断。原因MySQL 里lng是DECIMAL类型pymysql查出来就是 Python 的Decimal对象而标准库json.dumps不认这个类型不会自动转成数字。解决在构建 JSON 前统一做类型转换。脚本里我已经写了float(row[lng]) if row[lng] is not None else None如果不想用 float也可以str()但前端通常会期望数字类型所以更推荐float()。如果你不想在代码里逐个转换也可以在json.dumps里写defaultlambda obj: float(obj)但前提是确认对象真的都是 Decimal。6. 进阶用法给表打上时间版本、校验经纬度与按需导出6.1 用 version 字段管理年度区划调整行政区划不是一成不变的每年都可能出现撤县设区、新区挂牌、名称变更。如果你把省市区表当成静态字典等下游缓存更新时就会发现旧编码突然查不到。我习惯在表里留version字段和effective_date字段导数据时把这次导入的来源版本号写进去。ALTER TABLE region ADD COLUMN version VARCHAR(16) NOT NULL DEFAULT 2024 COMMENT 数据版本, ADD COLUMN effective_date DATE DEFAULT NULL COMMENT 生效日期;这个字段的价值在增量更新场景。下次拿到新 SQL不要先删旧表再导入而是先 SELECT 出旧版本里的area_code集合和新数据对比找出“消失的编码”和“新增的编码”再决定哪些记录下线、哪些记录上线。这个操作能避免线上用户历史地址在数据更新后变成孤儿。6.2 用一段 SQL 校验经纬度范围比肉眼快得多每次拿到新数据我都会先执行一段经纬度范围校验。中国陆地大致范围是经度 73 到 136 左右纬度 18 到 54 左右超范围的记录基本可以断定是脏数据或者坐标错位。SELECT name, area_code, lng, lat FROM region WHERE lng NOT BETWEEN 73 AND 136 OR lat NOT BETWEEN 18 AND 54;如果你的业务覆盖港澳台和其他南海区域边界会略有不同但只要这个查询查出超过 100 条记录你基本可以怀疑这批数据的坐标系或列名是不是拿错了。把它写成项目里的一个定时校验每次导入数据后跑一遍能省下大量前端排查时间。6.3 导出 JSON 的习惯做法我一般会把生成的region.json定时输出到静态文件目录而不是让前端每次请求都查 MySQL。region.json可以只包含当前版本的数据文件名带上 hash比如region-20250101.json前端发布版本时再引用对应文件。这样省市区数据变更后旧版本依然可以被历史订单页面引用不会因为覆盖写导致 H5 页面上的默认地址追溯异常。这个习惯救过我一次那次因为新区划上线旧区划下所有历史订单地址全部显示不出城市名后来靠版本化 JSON 才把数据追回来。希望帮到你。本文还有配套的精品资源点击获取