简介面向需要实现省市区街道乡镇联动选择、地址库初始化或行政区划检索的Web/移动端开发者及数据维护人员这份四级城市地区数据资源覆盖国内省市县街道乡镇可显著降低地址数据整理成本。压缩包共6个文件包含xlsx表格便于人工查阅与校对sql脚本可直接导入MySQL等关系型数据库另附txt字段说明和3张结构预览截图整体约2.48MB。数据结构包含ID、父ID、名称、联动ID、层级、是否末级等关键字段层级1~4清晰对应省、市、县区、街道乡镇联动ID采用如“11-1101-110107-110107001”的层级串联格式前端可直接按拆分结果实现逐级联动末级标记为1的条目可用于判断选择器最末一级无需二次整理导入即可使用。目前已有603人学习下载适合作为城市联动组件的数据底座、行政区划数据库设计参考也可用于前端联动效果演示与教学。1. 四级地址联动看着简单真正让你加班的是那四列字段做物流、门店配送这类业务时最让后端头疼的往往不是接口性能而是地址。省市区三级联动做得好好的一上线发现仓库和驿站按街道乡镇派单用户只能把街道名硬塞进备注框。这份国内省市县街道乡镇四级地址数据xlsx 和 sql 文件都有表面只是几万行文本真正麻烦的是四列字段怎么消费名称、联动ID、层级、是否末级。联动ID 决定父子节点怎么查层级决定你做几级联动是否末级决定列表末端还能不能继续点。这篇文章按可落地的路径讲先把字段语义和边界定清楚再清洗导入数据库建索引最后落到前端联动查询上。适合正在做四级联动、要把精确到街道的地址作为标准数据落库的工程师。2. 字段定性决定后续所有查询名称、联动ID、层级、是否末级的语义边界拿到这种地址数据第一件事不是打开 Excel 看有几行而是先把表的字段语义搞清楚。很多翻车现场并不是数据缺行而是把“联动ID”理解成了自增主键把“是否末级”理解成了不能再展开。这两个理解错了后面的父子查询、递归路径、前端级联组件全部跟着错。这一章先不写导入脚本只做一件事把这四列的真实含义和边界条件钉死。文件里字段名可能会略有出入比如“联动ID”可能叫 pid、parent_code、p_codelevel 可能叫 grade但表达的东西是一致的。你只需要按这套思路去对应实际文件里的列名就行。2.1 把一张平面表还原成一棵树先画父子关系再谈联动四级地址表看起来是扁平的行列表实际是一棵固定深度的树。树的根是省份第二层是地级市第三层是区县第四层是街道乡镇。每一行至少要能回答两个问题我是谁我父亲是谁。如果文件里只有题面列出的四列通常还缺一个“自身唯一标识”。常见的五列结构是下面这样列名示例说明code330000本行行政区划代码全表唯一name浙江省本行名称parent_id0父级行政区划代码省级填 0level11省2市3区县4街道乡镇is_leaf01末级0非末级如果你的文件没有 code 这一列只有“名称、联动ID、层级、是否末级”四列就一定要先确认“联动ID”到底指向谁。我见过一种表它的“联动ID”就是父级的 code本行自己的 code 被藏在了 Excel 的第一列或者根本没给。这种情况下导入数据库之前必须自己补一个唯一 ID可以用父级 ID 顺序生成也可以直接用“父级 ID 当前行号”拼一个。千万别拿 name 当主键全国同名街道、同名区县实在太多。判断方法很简单把省级那一行找出来如果它的联动ID 是 0 或空字符串而市级行的联动ID 能对应到某个省级行的 code那联动ID 就是父级 ID。画出来的父子关系就是一棵标准的树。2.2 用三行数据判断联动ID 到底是父ID 还是路径ID不看清楚必翻车同一个“联动ID”这个词在不同版本的数据文件里可能代表两种完全不同的东西。一种是你常见的父级 ID查子级时直接WHERE parent_id ?。另一种是“路径 ID”也就是这一行从根到自己的整条编码链比如省级是 33市级是 3301区县是 330102街道是 330102001那么街道行的联动ID 可能是完整的330102001或者一串用逗号拼接的祖先路径。区分方法不需要看整个文件取三行就够了。先看省级这一行如果它的联动ID 不是空而是自己的 code那多半是“自身 ID”而不是父级 ID。再看一个市级行如果它的联动ID 和省级行的联动ID 存在“前缀包含”关系比如省级是 33市级是 3301区县级是 330102那这组数据用的是路径模式查询时要按位长截断或者用 LIKE 前缀。路径模式下查子级的 SQL 会是这样的-- 假设 path_id 是“祖先编码串”每一层通过加两位或三位扩展 -- 查浙江省下面的地级市以 33 开头并且总长度是 4 位 SELECT code, name FROM cn_region WHERE path_id LIKE 33% AND CHAR_LENGTH(path_id) 4;这种写法对索引很不友好能不用尽量不用。如果数据源是路径模式我一般会在导入阶段把它拆成标准的 parent_id 和 code 两列而不是让业务查询一直背着 LIKE 前缀。拆列的逻辑不复杂按层级截取前若干位上一级就是当前路径去掉最后两位或三位的部分。2.3 层级与是否末级的四种组合别被 level4 的想当然骗了层级字段的范围一般是 1 到 4对应省、市、区县、街道乡镇。是否末级字段是 0 和 11 表示这一节点下面没有子级了。理想情况下level1、2、3 的行 is_leaf 都应该是 0level4 的行 is_leaf 是 1。但实际拿到的数据往往不是这样。我遇到过几种典型偏差有的文件把“街道”里拆出来的社区也算了一级导致 level 出现 5有的文件只在某些区县下补了街道数据另一些区县在 level3 时就标了 is_leaf1还有的文件把乡镇下面的自然村也整理了虽然 level 没有 5但 is_leaf 和 level 对不上。写前端联动组件时判断“还能不能继续展开”应该优先看 is_leaf而不是看 level 是否小于 4。最稳的规则是level 5 AND is_leaf 0才允许继续加载子级这样即使数据里多出一层也不会把组件搞死。3. 落地第一步把 xlsx 清洗成可执行的 sql 文件导入 MySQL 与 SQLite 都走通字段语义确认完接下来就是把 xlsx 变成能直接跑的表。这一步的坑几乎都集中在数据清洗上。Excel 文件在人工整理时会有大量肉眼看不见的问题合并单元格、单元格前后空格、数字被存成文本、空白行、全角符号。直接 read 出来拼 INSERT大概率在导入数据库时才发现问题。我的习惯是分三段走先清洗再生成 SQL最后导入。清洗用 pandas 最省事生成 SQL 用 Python 拼字符串导入阶段再决定用 MySQL 还是 SQLite。下面把每段的关键参数和边界条件都列出来。3.1 用 pandas 读取 xlsx先去做三件“不显眼但保命”的清洗读取 Excel 时第一件事是统一列名和数据类型。我见过太多人直接pd.read_excel把层级列读成了 float导入 MySQL 后数字全带.0然后拿着1.0去匹配level 1查不出任何结果。import pandas as pd # dtype 全部先按字符串读避免 0/1 被识别成 bool 或 float df pd.read_excel(region.xlsx, dtypestr, header0) # 统一列名文件里可能是中文列名转成小写英文再处理 df.columns [str(col).strip().lower() for col in df.columns] # 第一件事去掉整行全空的数据Excel 里经常有大量无意义空行 df df.dropna(howall) # 第二件事去掉所有单元格首尾空白中文数据里混全角空格很常见 for col in [name, parent_id, level, is_leaf]: if col in df.columns: df[col] df[col].astype(str).str.strip() # 第三件事把 level 和 is_leaf 从字符串转成数字转不了的丢弃 df[level] pd.to_numeric(df[level], errorscoerce) df[is_leaf] pd.to_numeric(df[is_leaf], errorscoerce) # 名称或者层级空的行没有任何保留价值 df df.dropna(subset[name, level]) df df[df[name] ! nan] print(df.head())这里最关键的参数是dtypestr。Excel 里的数字列如果被 pandas 自动识别成 float行政区划代码这种“看起来像数字但实际上是字符串”的字段就会被截断或者变成科学计数法。六位的区划代码还好九位的街道代码一旦被 float 化精度丢失就直接错了。所以必须先用字符串读后面再按字段语义去转类型。errorscoerce的作用是遇到没法转成数字的值时填 NaN而不是抛异常中断整个脚本。清零数据里偶尔会有“-”、“空格”这种垃圾值直接报错的话反而拖慢进度。3.2 生成 CREATE TABLE 与批量 INSERT参数和转义一个都不能省清洗完之后生成 SQL。表结构按章节 2.1 的五列标准设计其中 code 是主键。建表语句建议直接指定 utf8mb4 字符集别等到导入时再解决编码问题。CREATE DATABASE IF NOT EXISTS region_assist DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE region_assist; CREATE TABLE IF NOT EXISTS cn_region ( code VARCHAR(12) NOT NULL COMMENT 行政区划代码, name VARCHAR(64) NOT NULL COMMENT 名称, parent_id VARCHAR(12) NOT NULL DEFAULT 0 COMMENT 父级联动ID省级为0, level TINYINT NOT NULL COMMENT 1省 2市 3区县 4街道乡镇, is_leaf TINYINT NOT NULL DEFAULT 0 COMMENT 1末级 0非末级, PRIMARY KEY (code), KEY idx_parent (parent_id, level) ) ENGINEInnoDB COMMENT中国省市县街道四级地址表;生成 INSERT 时最容易被忽略的是单引号转义。地名里虽然少见但“某某村委会”这种数据不是没有。下面这段 Python 负责把清洗后的 DataFrame 拼成批量 INSERT 语句。def escape_sql(value: str) - str: # 先把反斜杠和单引号转义再包一层单引号 return str(value).replace(\\, \\\\).replace(, \\) sql_lines [] sql_lines.append(USE region_assist;) # 每 500 行拼一条批量 INSERT减少执行次数 batch_size 500 for i in range(0, len(df), batch_size): chunk df.iloc[i:i batch_size] values [] for _, row in chunk.iterrows(): # row 里的 code/name 已经是清洗后的值 values.append( ,.join([ escape_sql(row[code]), escape_sql(row[name]), escape_sql(row[parent_id]), str(int(row[level])), # 层级转成整数 str(int(row[is_leaf]) if not pd.isna(row[is_leaf]) else 0) ]) ) sql_lines.append( INSERT INTO cn_region (code, name, parent_id, level, is_leaf) VALUES\n ,\n.join(( v ) for v in values) ; ) with open(region.sql, w, encodingutf-8) as f: f.write(\n.join(sql_lines))批量 INSERT 的batch_size建议控制在 200 到 1000 之间太大容易超过 MySQL 的max_allowed_packet太小又浪费网络往返。3000 到 5000 行的四级地址表500 一批是安全值。3.3 不想引 Python 的工程操作 Excel 的 JS 工具库 xlsx 读取并转 JSON不是每个团队都愿意为了一个数据文件引入 Python 依赖。如果你们的工程是纯 Node 技术栈直接用操作 Excel 的 JS 工具库 xlsx 读取会更顺。这个库的 readFile 方法可以直接读取 .xlsx配合 sheet_to_json 转出数组。const XLSX require(xlsx); // 读取第一个 sheet const workbook XLSX.readFile(region.xlsx); const sheet workbook.Sheets[workbook.SheetNames[0]]; // defval 让空白单元格得到空字符串而不是 undefined const rows XLSX.utils.sheet_to_json(sheet, { defval: }); // 取前几行确认列名映射 console.log(rows.slice(0, 3)); // 生成 SQL 时同样要注意转义 const escapeSql (v) ${String(v).replace(/\\/g, \\\\).replace(//g, \\)}; const inserts rows .filter((r) r[name] r[level]) .map((r) (${escapeSql(r[code])}, ${escapeSql(r[name])}, ${escapeSql(r[parent_id])}, ${r[level]}, ${r[is_leaf] || 0})) .join(,\n);defval: 是这里最值得说明的参数。xlsx 库默认会将空白单元格解析为undefined后续拼字符串时会出现undefined字符串混进 SQL 的诡异现象。显式给空字符串后后续逻辑可以统一按空值处理。需要注意xlsx 库的sheet_to_json默认把第一行当列名如果文件第一行有中文表头你需要手动改 key或者在读入时把表头行单独处理。3.4 SQL 文件导入命令字符集和 SOURCE 的写法说明生成好的 region.sql 接下来要导入数据库。如果只在本机验证SQLite 是最快的路径。如果作为业务正式表走 MySQL 导入。# SQLite 快速验证表结构完全兼容五列模型 sqlite3 region.db region.sql # MySQL 导入注意 --default-character-set 必须放在最前面 mysql -uroot -p --default-character-setutf8mb4 region_assist region.sqlMySQL 导入时最常见的问题是中文乱码。--default-character-setutf8mb4要放在region.sql前面因为它是客户端参数不是 SQL 语句。如果 SQL 文件非常大或者你想在导入过程中看每一阶段的报错用 source 方式更稳。mysql -uroot -p --default-character-setutf8mb4USE region_assist; SET NAMES utf8mb4; SOURCE /path/to/region.sql;SET NAMES utf8mb4是让当前会话的客户端字符集和表字符集保持一致SOURCE则是执行外部文件。如果 source 进来的文件编码是 UTF-8 但文件头带着 BOM第一行建表语句可能直接报语法错误这时先用sed -i s/^\xef\xbb\xbf// region.sql去掉 BOM 再执行。4. 查询层设计一级子节点的 SQL、向上反查的递归 CTE 和索引设置地址表导入数据库只是第一步真正让这四列数据产生价值的是查询层。日常业务里最频繁的场景是两类一类是从省或者市往下拉子级列表一类是从街道向上反查完整路径。这两类查询的写法完全不同用错了性能差距会很大。4.1 按联动ID 查下一级父子查询只用 parent_id不要用 level最常规的下拉列表查询直接按父级 ID 过滤即可。即使你已经知道当前节点的层级也不要图省事写WHERE level 当前层级 1更不要相信“市级行的 level 一定是 2”这种假设。省直辖县级市、部分经济开发区、高新区在数据里挂在省级下面层级是 3直接套 level 换算会漏掉一整批节点。-- 查浙江省的地级市 SELECT code, name FROM cn_region WHERE parent_id 330000 ORDER BY code; -- 查杭州市的区县联动ID 指向杭州的行政区划代码 SELECT code, name FROM cn_region WHERE parent_id 330100 ORDER BY code;这段 SQL 没有限定 level所以无论子节点是普通地级市还是省直辖县级市都能被查出来。等宽的 code 排序在同类节点里是稳定的省级两位、市级四位、区县六位天然就是正确的显示顺序。前端级联组件每次切换节点时都调用这种查询频率会很高。所以第 3 章建表时建的KEY idx_parent (parent_id, level)这时候就起作用了。实际执行时 MySQL 会先按 parent_id 找到所有子节点再按 level 排序或过滤两者组合的索引比单列索引更省回表。4.2 向上拼路径用 WITH RECURSIVE 从街道回推到省第二个高频场景是反查路径。比如用户提交了一个街道 ID后端需要知道这个街道属于哪个省市区用来做订单区域统计或者权限判断。一条比较快的路径是用 MySQL 8.0 的递归 CTE 从当前行往上逐层找父亲。WITH RECURSIVE path_cte AS ( -- 初始行从当前街道开始 SELECT code, name, parent_id, level FROM cn_region WHERE code 330100001 UNION ALL -- 递归找到父级条件是父级 code 当前行的 parent_id SELECT r.code, r.name, r.parent_id, r.level FROM cn_region r INNER JOIN path_cte c ON r.code c.parent_id WHERE c.parent_id 0 ) SELECT GROUP_CONCAT(name ORDER BY level SEPARATOR /) AS full_path FROM path_cte;这里的关键逻辑是INNER JOIN path_cte c ON r.code c.parent_id。每次递归取出的是一行父级记录直到 parent_id 为 0 也就是省级节点的根位置。WHERE c.parent_id 0是为了让递归在到达省级之后停止避免因为数据错误出现死循环。如果数据量不大也可以不用递归在代码里循环查。但业务查询一旦要批量计算比如一次给 100 个街道补全路径循环查询就是 100 次交互递归 CTE 在数据库里一次性算完性能明显更好。4.3 索引参数parent_id、level、is_leaf 怎么建最实用四级地址表通常就是几万行即使不建索引查询也慢不到哪里去但联动组件的请求频率会放大单次查询的延迟。索引不是越多越好这表最常用的过滤条件是 parent_id 和 level最常用的是按 is_leaf 过滤。-- 常用查询场景 -- 1. WHERE parent_id ? -- 2. WHERE parent_id ? AND level ? -- 3. WHERE is_leaf 1 AND name LIKE xx% ALTER TABLE cn_region ADD KEY idx_parent_level (parent_id, level); ALTER TABLE cn_region ADD KEY idx_leaf_name (is_leaf, name);idx_parent_level上面的两个条件字段都覆盖了联合索引在多数情况下能直接用。idx_leaf_name则是给搜索场景用的当你搜“西湖区有哪些可派送的街道”时先用 is_leaf 过滤掉非末级节点再按名称模糊匹配索引命中率高很多。如果数据库版本支持把 code 换成更短的整型当然可以但省级到街道的 code 本身只有 6 到 12 位VARCHAR 完全够用省这个优化没必要。5. 踩坑记录合并单元格、省直辖县级市、is_leaf 错值与乱码的排查方法标题里的字段看着清爽落到真实文件里到处都是看似正常实则害人的数据。这一章是我反复导入地址数据后沉淀下来的排查清单每一条都按“现象、原因、解决”写你们遇到类似情况可以直接照着定位。5.1 现象level 和 is_leaf 出现大片空值第一次用 pandas 读取 xlsx 时发现有些行只有名称后面的联动ID、层级、是否末级全是 NaN。去 Excel 里看却发现单元格里明明有内容再仔细看是“浙江省”这一列被合并单元格了。合并单元格在 pandas 读取时只有左上角第一行有值其余行都是 NaN但不影响其他列的数据。解决方式是在清洗阶段对关键列做前向填充也就是把上一行非空的值补到当前行。# 合并单元格的典型表现名称列或联动ID列整列 NaN # 用 ffill 沿列向下填充 df[name] df[name].ffill() df[parent_id] df[parent_id].ffill()填充之后第二行的 name 会继承省级名称parent_id 会继承省级联动ID。对于这种“省市县街道”逐层归属的表格ffill 是准确的因为每个省级下面的市级行本来就应该共享省级的父级信息。填充完后再跑一次dropna(subset[name, level])把真正无用的行清理掉。5.2 现象城市列表总是缺几个怎么都查不出来项目上线后运营反馈在城市下拉框里找不到河南济源、湖北仙桃这些城市。起初以为是数据缺行查了 xlsx 才发现数据在但这些城市的层级是 3父级直接是省级而不是某个地级市。这类“省直辖县级市”在行政体制里就是一种特殊存在不会经过地级市这一层。前端组件如果按“省 → 市 → 区县”三级硬编码来加载看到 level3 的济源出现在省下面第一反应是数据错了于是过滤掉。解决方法是把层级逻辑彻底从查询里拿掉子级只看 parent_id不看 level。前端组件展示的仍然是“当前节点的子列表”只不过省级下面的子列表里既有地级市也有省直辖县级市这对业务是正确的。5.3 现象is_leaf1 的节点还能查出子级维护阶段遇到过一个更隐蔽的问题某个街道节点 is_leaf 标了 1但按它的 code 查子级还能查出 20 多个社区。原因是有同事在四级地址之外又补了社区级数据却没有同步更新上一级节点的 is_leaf。联动组件发现 is_leaf1 后就不再发请求那些社区数据就成了死数据。这种问题要用一致性检查提前拦住。SQL 里找出那些“自己标了末级但表中还有孩子指向它”的节点SELECT p.code, p.name, COUNT(c.code) AS child_cnt FROM cn_region p INNER JOIN cn_region c ON c.parent_id p.code WHERE p.is_leaf 1 GROUP BY p.code, p.name HAVING COUNT(c.code) 0;如果这个查询返回了记录说明 is_leaf 字段和真实父子关系已经脱节。解决时可以按业务需要把 is_leaf 统一改为 0也可以反方向把社区级数据从表里拆出去单独维护。我一般选择前者因为前端组件能正常展开总比藏着数据强。5.4 现象SQL 文件导入 MySQL 中文全变问号或者报错“Invalid utf8 character string”中文乱码是 SQL 文件导入最常见的故障。现象很直接导入后 select 出来地名全是??或者锟斤拷。原因通常是两条一是 SQL 文件本身是 UTF-8但执行导入的客户端连接字符集不是 utf8mb4二是文件其实是 GBK 编码被硬按 UTF-8 解析。排查时先做两件事。第一件用file region.sql看文件真实编码第二件确认客户端字符集参数。如果是文件编码问题用 iconv 转换而不是在 SQL 里硬塞SET NAMES。# 查看文件编码 file region.sql # GBK 转 UTF-8 iconv -f GBK -t UTF-8 region.sql region_utf8.sql # 再导入 mysql -uroot -p --default-character-setutf8mb4 region_assist region_utf8.sql注意SET NAMES utf8mb4只能改变当前会话的客户端字符集改不了 SQL 文件本身的字节。文件是 GBK 的时候设置SET NAMES等于让数据库把 GBK 字节流按 UTF-8 解码照样是乱码必须先转码。5.5 现象INSERT 时大量 Duplicate entry 报错导入到最后阶段报主键重复通常是同一份文件里出现了重复的行政区划代码。常见原因是数据源在整理时把某些街道在不同区县下重复录了一次code 一样但名称不同。生产环境直接执行 result整批导入会失败。入库前先单独查一遍重复 code是成本最低的防线SELECT code, COUNT(*) AS cnt FROM cn_region GROUP BY code HAVING COUNT(*) 1;如果重复记录确实存在看业务上想保哪一份。我处理这类问题时优先保留名称和层级与官方区划一致的那条再用唯一的INSERT ... ON DUPLICATE KEY UPDATE把其余记录覆盖掉。最省事的做法是导入前在临时库里跑同一个检查 SQL确认无重复主键后再动生产表。6. 再进一步用同一份数据生成路径缓存让搜索和建议框直接用四级地址表做成了标准库之后还可以把父子关系预计算成“完整路径”这样搜索和建议框就不用每次递归拼路径了。路径缓存的核心价值是把查询从递归变成一次字符串匹配对高频搜索场景帮助明显。做法也并不复杂。一次性把全表的 code、name、parent_id、level 读进内存按 code 建一个字典然后从每个叶子节点向上回溯拼出“省/市/区/街道”格式的完整路径。# 一次性读取全表 cur.execute(SELECT code, name, parent_id, level FROM cn_region) rows cur.fetchall() node_by_code {row[code]: row for row in rows} def build_path(code: str) - str: parts [] current code # 向上回溯到省级parent_id 为 0 时结束 while current and current ! 0: node node_by_code.get(current) if not node: break parts.append(node[name]) current node[parent_id] return /.join(reversed(parts)) # 只给是末级的节点生成路径缓存 for row in rows: if row[is_leaf] 1: path build_path(row[code]) # 写入 path_cache 表字段为 code、full_path、name这个函数有几个参数要注意。while current and current ! 0保证了回溯不会越界一旦数据里出现某个节点的父级不存在循环也会因为node_by_code.get(current)返回 None 而安全退出。路径拼接用/.join(reversed(parts))因为回溯是从街道往省方向走天然得到的是逆序反转一次才能得到正常的省市区顺序。缓存表建好之后最直接的应用是“根据用户输入的关键词补全地址”。前端的搜索框可以用SELECT code, full_path FROM path_cache WHERE full_path LIKE %西湖% LIMIT 10做模糊匹配返回的就是一条完整的“浙江省/杭州市/西湖区”不需要再写递归查询。我自己的习惯是在生成缓存表之后再做一次“叶子不一致检查”如果某个 is_leaf1 的节点在 path_cache 表中也有子级路径就把 is_leaf 修正掉。这个自查动作跑一次只要几秒钟却能在业务出问题之前把脏数据拦下来省掉很多和运营对线的工夫。这份数据本身不复杂复杂的是拿到数据后怎么理解这几列的边界。只要“联动ID”是谁、“是否末级”到底能不能展开这两点不再含糊后面无论是做三级联动还是四级联动甚至加一层社区数据都不会再让你熬夜加班。希望这套流程能帮你少踩几个坑。本文还有配套的精品资源点击获取
原理与实战：SORT、DeepSORT、ByteTrack选型指南)
