省市区街道村五级联动SQL：编码规则、导入校验与查询优化

简介面向数据库初学者及需要实现多层级级联选择功能的开发者这套三级、四级、五级联动SQL资料围绕层级数据表设计、外键关联与联合查询展开可用于行政区划等分层信息的建模与检索场景。压缩包共25个文件约22.91MB以SQL脚本、PHP示例、CSV数据为主并配有Markdown说明、YAML配置及XML/JSON等辅助文件目录按源码、数据库迁移、配置与数据分发组织便于按模块查阅。SQL部分侧重建表、插入与JOIN查询CSV便于导入层级样例数据PHP代码展示联动逻辑的调用方式。已有28人学习适合用来对照理解父子层级字段、外键约束以及触发器、视图和索引在联动查询中的作用。读者可从中获得可参考的表结构、数据样例与查询写法用于快速搭建省—市—区县等逐级筛选功能并据此梳理数据完整性与查询性能的优化思路。1. 五级联动数据不是一张表就能解决先看清这份 SQL 里装了什么产品经理丢过来一个几千万把行的 sql 文件说“省市区街道村全都有直接导进去就能用”。打开一看五级数据塞在一张自关联表里编码有 6 位、9 位、12 位三种长度还夹着一批 parent 指向 0 的节点——这就是典型的三级四级五级联动 sql 文件它把省、市、区县、街道乡镇、村社区五层行政区划一次性落库用父子关系串成树前端级联选择器按层下钻就能从“某省”一路选到“某村”。它的价值在于层级完整、编码规范、能直接导入跑通。适合做收货地址、客户归属、表单归属地这类需求的团队也适合手上只有三级数据、被要求“再往下钻两层”的人。直接mysql file.sql就完事的人多半会在字符集、层级跳跃和排序上出错。2. 表结构与编码规则6 位、9 位、12 位为什么要分开理解拿到文件先别急着导入把建表语句和几行样例数据读一遍能省掉后面一半的排查时间。这份数据的组织方式无非三种选错了后面每加一层都要改代码。2.1 三种表结构单表自关联、分层多表和扁平宽表结构典型字段优点代价适用场景单表自关联code / parent_code / name / level / sort加层不用改表三级和五级共用一套查询逻辑取全路径要递归或逐层查绝大多数业务系统分层多表province / city / district / street / village 五张表单表数据量小、语义直白、联表看得懂层级固定加一层要动表结构和代码层级多年不变的老系统扁平宽表code12 / province / city / district / street / village / full_name一条 SQL 出全路径导出给报表很方便冗余大改一个区名要全表更新只读展示、数据导出单表自关联是主流也是改动成本最低的一种。它的核心就一句话任何一个节点靠parent_code找到爹靠code前缀找到自己属于谁。层级用level字段显式标出来1 省、2 市、3 区县、4 街道乡镇、5 村社区前端拿这个字段决定渲染第几列比按数组下标猜层级安全得多。注意level一定要有而且要和code长度互相印证。只靠长度判断层级遇到补零不规范的 dump 文件会直接判断错误。2.2 编码分段前缀就是父级这是整份数据的骨架行政区划代码是分段编的每一段往下挂一层第 1–2 位省级第 3–4 位市级第 5–6 位县级到这里凑齐 6 位即三级联动能用到的最细一级第 7–9 位乡级街道、镇凑齐 9 位第 10–12 位村级村、社区凑齐 12 位也就是说一个 12 位的村码前 6 位就是它所属区县的码前 9 位就是它所属街道的码。这个性质非常关键它意味着“由子找父”根本不需要递归用字符串截取就能直接定位上层节点后面第 6 章会用到。字段映射上常见的建表大概长这样字段类型说明codeCHAR(12)主键左侧补零到统一长度千万别用 INTparent_codeCHAR(12)顶层节点写 0其余写父级 codelevelTINYINT1–5必须与 code 长度对应nameVARCHAR(64)展示名字符集 utf8mb4short_nameVARCHAR(32)去掉“省/市/区/县”后缀用于拼接短地址pinyin / initialVARCHAR(64) / VARCHAR(8)拼音与首字母做搜索和 A–Z 索引sortSMALLINT手工排序权重没有就留 NULLstatusTINYINT1 有效、0 已撤销用于增量更新时保留历史code用 CHAR 而不用 INT是因为存在前导零一旦转成数字前导零就没了010000变成10000前缀关系全部失效。我见过有人为了“省空间”把 code 设成 INT结果五级查询全乱返工重导一遍。CHAR(12) 定长还有一个好处比较和截取都不用考虑长度差异LEFT(code, 6)永远拿到县级码。2.3 导入前的四道校验花两分钟省两小时拿到文件后先建个临时库导进去跑下面四条查询。任何一条返回非空说明文件本身有毛病先修数据再谈联调。-- 1) 孤儿节点父级不存在。这类节点在前端表现为“某一列永远是空白” SELECT r.code, r.name, r.parent_code FROM sys_region r LEFT JOIN sys_region p ON p.code r.parent_code WHERE r.level 1 AND p.code IS NULL; -- 2) 层级与码长不匹配level 说是村code 却只有 6 位 SELECT code, name, level, CHAR_LENGTH(code) AS code_len FROM sys_region WHERE (level 3 AND CHAR_LENGTH(code) 6) OR (level 4 AND CHAR_LENGTH(code) 9) OR (level 5 AND CHAR_LENGTH(code) 12); -- 3) 子节点前缀对不上父节点说明编码被改过或补零不齐 SELECT c.code, c.name, c.parent_code, p.code AS real_parent FROM sys_region c JOIN sys_region p ON p.code c.parent_code WHERE LEFT(c.code, CHAR_LENGTH(p.code)) p.code; -- 4) 主键重复多次追加导入的典型后果 SELECT code, COUNT(*) AS cnt FROM sys_region GROUP BY code HAVING cnt 1;第一条查询的逻辑是自连接左联父表没匹配上就是孤儿。第二条靠CHAR_LENGTH反查注意用CHAR_LENGTH而不是LENGTH后者算的是字节数utf8mb4 下中文一个字 3 字节容易误判。第三条是最有价值的一条它能一次性把编码体系里所有“父子关系断裂”的位置全找出来比逐层点开页面看快得多。第四条不用解释导入前TRUNCATE一遍就不会有。这四条跑完都干净再往下做联调心里就有底了。3. 导入与联调把 SQL 文件落到库里跑通五级下钻数据干净之后剩下的就是导入、建索引、写查询、接前端。这一章按执行顺序走每一步都给到能直接抄的命令和参数。3.1 建库与导入字符集和包大小是两个必调参数建库时把字符集和排序规则显式写死不要依赖服务端默认值。很多 dump 文件头部会写SET NAMES utf8mb4但表定义里可能是utf8mb4_general_ci如果服务端默认是别的排序规则后面 JOIN 时会出现“非法混合排序规则”的报错。# 建库字符集与排序规则跟 dump 保持一致别赌默认值 mysql -uroot -p -e CREATE DATABASE region_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; # 导入三个参数先放大再喂文件 mysql -uroot -p \ --default-character-setutf8mb4 \ --max-allowed-packet512M \ region_db region_5level.sql参数逐个说清楚--default-character-setutf8mb4客户端连接字符集。不写这一条客户端可能按 latin1 解析文件里的中文导进去全是问号而且是不可逆的只能重导。--max-allowed-packet512M单条 SQL 语句或单个结果集的上限。这类文件为了导入快常常把几千行INSERT合并成一条一条语句超过默认的 4MB 或 16MB 就直接断连。服务端对应参数也要放开SET GLOBAL max_allowed_packet 536870912;改完新连接才生效。导入完成后第一件事是核对各层级数量SELECT level, COUNT(*) AS cnt, MIN(CHAR_LENGTH(code)) AS min_len, MAX(CHAR_LENGTH(code)) AS max_len FROM sys_region GROUP BY level ORDER BY level;这条查询同时验证了两件事数量是否符合预期量级省几十、市几百、区县几千、街道几万、村几十万具体以文件为准以及每层的码长是否统一。如果 level5 那行的min_len出现 6说明有村节点没补零到 12 位前面前缀匹配的思路就用不了得先补零UPDATE sys_region SET code RPAD(code,12,0) WHERE level 5;。3.2 索引设计与逐层下钻查询dump 文件里通常只带主键业务查询要用的索引得自己补。五级联动最典型的访问模式是“给定父级取子级列表”所以联合索引的顺序是parent_code, level, sortALTER TABLE sys_region ADD PRIMARY KEY (code), ADD KEY idx_parent_level_sort (parent_code, level, sort), ADD KEY idx_name (name);parent_code放最左因为它是等值条件level第二位用来过滤掉同父级下不同层的杂项正常情况下不会出现但脏数据里常见sort放最后让排序也能吃到索引避免Using filesort。下钻查询就一条-- 取某个节点下的直接子级用于级联选择器的下一列 SELECT code, name, level, IFNULL(sort, 9999) AS sort_key FROM sys_region WHERE parent_code 100000 ORDER BY sort_key, code;两个细节。第一parent_code传参必须是补零后的定长字符串前端如果传了数字100000被转成100000还好但传10000就查不到任何东西所以接口层要做一次String(value).padStart(12,0)。第二IFNULL(sort, 9999)是为了把没有手工排序的节点压到后面——MySQL 里 NULL 在升序排序时排最前直接用ORDER BY sort, code会让一堆没设权重的节点跑到列表顶部看起来像“数据乱了”。如果要一次性预热三级数据用整表扫更划算-- 三级全量约几千行一次取回前端自己拼树 SELECT code, parent_code, name, level FROM sys_region WHERE level 3 ORDER BY code;几千行 JSON 大概几百 KB走一次接口缓存起来完全没问题。3.3 全量加载还是懒加载按层数决定不是按喜好决定三级和五级是两种不同的前端策略这个选择直接决定首屏体验。方案做法首屏体积适用层级代价全量树一次取回所有层级前端拼成嵌套结构三级约几百 KB五级可达十几 MB三级五级下会把首屏拖慢移动端基本不可用分层懒加载每次只取当前节点的子级选中后再请求下一级每次几 KB四级、五级需要接口和缓存配合多几次往返混合三级预加载四级五级按需几百 KB 按需实际最常用需要处理好“回填”场景三级数据一把梭没问题五级数据千万别这么干。全国村级节点数量在几十万量级序列化成 JSON 十几 MB移动端解析一次就是几百毫秒起步还不算内存占用。懒加载的接口层加一层按父级缓存就够用了key 里带上层级避免同一个 code 在不同层出现时读错缓存// 前端侧按「层级 父码」缓存避免同名不同级读错 const cache new Map(); async function loadChildren(parentCode, level) { const key ${level}:${parentCode}; if (cache.has(key)) return cache.get(key); const url /api/region/children?parent${encodeURIComponent(parentCode)}level${level}; const list await fetch(url).then(r r.json()); cache.set(key, list); return list; }level进缓存 key 是有原因的有些数据源里同一个 code 在 3 级和 4 级都存在过历史遗留或补零问题不带层级的话第二次请求会命中错误结果表现为“选了街道下一列出来的还是区县”。3.4 回填编辑页才是最容易出错的地方新增表单的联动好做编辑页才容易出错。用户存的是最末级100101001001回填时你需要一次性拿到从省到村的完整路径然后逐列展开。笨办法是循环调 5 次接口每次拿 parent前端串行等待页面会明显卡一下。更好的做法是后端开一个/api/region/path?codexxx内部用一条 SQL 拆码解决这个在第 6 章展开。这里先记住一条原则回填永远从叶子往上找不要从根往下逐层匹配名称。用名称匹配的话遇到“某街道”在多个区县重名直接选错。4. 避坑与排查五个实际排查过的点这一章是整篇最有价值的部分。前面按流程走这里按“出事之后怎么查”组织。4.1 导入阶段连接断开与中文乱码现象一导入到一半报ERROR 2006 (HY000) at line 18342: MySQL server has gone away进程直接退出。原因是 dump 文件里存在超大 INSERT 语句超过了max_allowed_packet服务端主动断开。解决分两步先在服务端执行SET GLOBAL max_allowed_packet 536870912;再重启客户端重导如果文件里单条语句实在太大超过 500MB改用split -l 5000把文件切成小段分批导入或者在客户端里用source逐段执行。我一般会先grep -c INSERT INTO看一下语句条数条数很少但文件很大就说明是巨型 INSERT提前把包大小放开。现象二数据导进去了SELECT name出来全是???或一堆问号方块。原因是连接字符集不对。注意这时候改表的字符集已经没用了——问号是编码转换时不可逆丢失的只能删库重导。重导时检查三处客户端命令加--default-character-setutf8mb4连接串里加characterEncodingutf8部分驱动还要useUnicodetrue建库语句显式写DEFAULT CHARACTER SET utf8mb4。另外如果数据里有生僻字地名utf8mb3 存不下必须是 utf8mb4这个在导入前用SELECT MAX(LENGTH(name)) FROM ... WHERE level5大致估一下就能发现异常。4.2 层级与联动第二列空白、列表顺序乱现象三选了省之后第二个下拉框是空的或者选了直辖市的省直接跳到区县中间那列没有内容。原因是数据里的层级跳跃。直辖市、省直管县级市这类结构在部分数据源里会做成“省级节点下面直接挂区县”没有市级节点。前端如果按固定列数渲染第一列省、第二列市、第三列区这一列就是空的。解决办法有两个方向数据侧补虚拟节点让每个区县的parent_code指向一个虚构的市级节点前端侧不按列索引渲染改成按level字段动态决定列。我更倾向后者因为补虚拟节点会污染数据导出报表时又要特殊处理。前端拿到子级列表后先看level如果直接是 3就把第 2 列也填上同一个批次的数据并标记为“市辖区”占位用户点起来不会断。现象四下拉列表看着“乱七八糟”有的区县跑到最前面有的排序跟别的省不一样。三种可能数据里sort字段为 NULL 且用了ORDER BY sortNULL 排最前排序规则用了utf8mb4_general_ci而名称里带生僻字排序结果不符合预期或者干脆是按name字母序排的中文按拼音和按笔画排出来的顺序完全不同。定位方法是把ORDER BY去掉看原始顺序是否稳定再看sort的 NULL 比例SELECT COUNT(*) AS total, SUM(sort IS NULL) AS null_sort FROM sys_region WHERE level 3;。解决就是用ORDER BY IFNULL(sort, 9999), codecode兜底保证顺序稳定可复现。现象五同一个页面刷新两次地址选择器里的数据不一样或者昨天能选的街道今天没了。原因是数据被追加导入过或者缓存没失效。先查重复SELECT code, COUNT(*) FROM sys_region GROUP BY code HAVING COUNT(*) 1;如果返回一堆说明是INSERT而不是REPLACE同一份数据被灌了两遍parent_code相同的两批节点混在一起。解决是先TRUNCATE TABLE sys_region;再完整重导别用DELETE——几十万行DELETE产生的回滚段和 binlog 比TRUNCATE大得多。缓存那一侧全量 JSON 的缓存 key 要带一个数据版本号版本号从库里一张region_version表读数据更新后手动加一前端localStorage里的旧 key 自然失效。提示这五条里第一条和第二条发生在导入阶段后三条发生在联调阶段。按这个顺序排查能避免在错误的方向上浪费时间。5. 数据裁剪与增量更新区划每年都在变别让数据留在旧版本五级数据不是导一次管一辈子。区划调整、撤县设区、村居合并每年都有字段名没变但内容变了用户投诉“我们这儿早就不叫这个名了”就是这么来的。这一章解决两件事怎么把文件裁小怎么比对出变更。5.1 按需裁剪只保留用得到的层级如果业务只用三级就没必要把几十万条村级数据塞进生产库。裁剪有两种做法看你的导入流程在哪一步。如果还能重导用mysqldump带--where直接导子集# 只导出三级及以上的数据体积能小一个数量级 mysqldump -uroot -p region_db sys_region \ --wherelevel 3 \ --default-character-setutf8mb4 \ --no-create-info --complete-insert \ region_l3.sql--no-create-info表示只导数据不带建表--complete-insert让每条 INSERT 都带列名避免目标表列顺序不同导致错位。两个参数一起用是把数据往已有表里灌时的标准姿势。如果数据已经在库里用CREATE TABLE AS复制一份瘦身版顺便把不用的字段砍掉-- 只留三级去掉拼音和经纬度生产库就轻了 CREATE TABLE region_l3 AS SELECT code, parent_code, name, level, IFNULL(sort, 9999) AS sort FROM sys_region WHERE level 3; ALTER TABLE region_l3 ADD PRIMARY KEY (code), ADD KEY idx_parent (parent_code, level, sort);CREATE TABLE ASCTAS不会继承原表的索引和字符集定义所以后面必须手动补主键和索引字符集也要核对一遍SHOW CREATE TABLE region_l3;。这一步如果漏了查询会退化成全表扫描几千行数据也能慢出几百毫秒。5.2 年度变更比对新增、撤销、改名三条 SQL拿到新版数据不要直接覆盖。先建一张region_new表导入新数据跟旧表做三向比对把差异落到一张变更表里人工过一遍再合并。-- 新增新数据里有旧数据里没有 SELECT n.code, n.name, n.level FROM region_new n LEFT JOIN sys_region o ON o.code n.code WHERE o.code IS NULL; -- 撤销旧数据里有新数据里没有 SELECT o.code, o.name, o.level FROM sys_region o LEFT JOIN region_new n ON n.code o.code WHERE n.code IS NULL AND o.status 1; -- 改名code 相同但名称变了 SELECT o.code, o.name AS old_name, n.name AS new_name FROM sys_region o JOIN region_new n ON n.code o.code WHERE o.name n.name;三条查询的逻辑都是标准的“左联 判空”或“内联 不等”成本很低。关键在结果怎么处理新增的直接插入并置status 1撤销的不要删除把status置 0因为历史订单里还存着这些 code删了之后回填就查不到名称页面上会出现空白地址改名的更新name和short_name同时在变更表里留一条记录方便后续追溯。5.3 版本号与缓存失效比“定时刷新”可靠缓存的失效策略定时刷新是最容易出事的一种——数据已经更新了缓存还没到期用户看到的是旧数据反过来刷新太频繁缓存又没意义。更稳的做法是显式版本号。-- 一张单行版本表数据合并完成后手动加一 CREATE TABLE region_version ( id TINYINT PRIMARY KEY, version INT NOT NULL, updated_at DATETIME NOT NULL ); UPDATE region_version SET version version 1, updated_at NOW() WHERE id 1;前端拿到数据时一并取回version存进localStorage的 key 里比如region_tree_v17。下次进页面先请求版本号不一致就清掉本地缓存重新拉。这一套下来数据更新和数据可见之间的延迟从“等缓存过期”变成了“下一次请求”而且排查问题时一眼能看出用户端用的是哪一版数据。注意撤销的节点保留status 0之后所有列表查询都要显式加WHERE status 1包括导出和后台管理页。漏一处就会出现“已经撤销的村还能选”的问题这类反馈排查起来非常耗时间。6. 前缀匹配与递归 CTE不查五次接口一次 SQL 还原五级路径订单表里只存了一个 12 位村码后台要显示“省 / 市 / 区县 / 街道 / 村”的完整地址。最直觉的写法是循环查五次每次拿parent_code这在批量导出时会直接拖垮数据库。用编码结构做自连接一条 SQL 就够了-- 前提code 已补齐到 12 位定长用前缀拼出各层主键一次自连接取全路径 SELECT p.name AS province, c.name AS city, d.name AS district, s.name AS street, v.name AS village FROM sys_region v LEFT JOIN sys_region s ON s.code CONCAT(LEFT(v.code, 9), 000) LEFT JOIN sys_region d ON d.code CONCAT(LEFT(v.code, 6), 000000) LEFT JOIN sys_region c ON c.code CONCAT(LEFT(v.code, 4), 00000000) LEFT JOIN sys_region p ON p.code CONCAT(LEFT(v.code, 2), 0000000000) WHERE v.code 100101001001;四张表都走主键等值查找代价极低。用LEFT JOIN而不是JOIN是因为某些层级可能缺失缺了也要把已有的部分显示出来不能让整行消失。反过来要取某个节点下的所有后代比如统计某市下的所有村别用递归用前缀匹配-- 取某区县下全部村级节点走主键最左前缀比递归快一个量级 SELECT code, name FROM sys_region WHERE code LIKE 100101% AND level 5 AND status 1 ORDER BY code;LIKE 100101%是固定前缀能吃到主键索引写成LIKE %100101%就退化成全表扫描了这个差别在几十万行上非常明显。递归 CTE 我一般不用来查业务数据而是用来做数据校验——把整棵树展开成带路径的行人工扫一遍层级是否连续-- 展开成「省/市/区/街道/村」的路径仅用于校验需 MySQL 8.0 或 PostgreSQL WITH RECURSIVE t AS ( SELECT code, name, parent_code, 1 AS lvl, CAST(name AS CHAR(512)) AS path FROM sys_region WHERE level 1 UNION ALL SELECT r.code, r.name, r.parent_code, t.lvl 1, CONCAT(t.path, / , r.name) FROM sys_region r JOIN t ON r.parent_code t.code WHERE t.lvl 5 ) SELECT lvl, path FROM t WHERE code 100101001001;CAST(name AS CHAR(512))是必须的不显式定长某些版本会报长度不匹配。另外 MySQL 有个cte_max_recursion_depth默认 1000五层远不到上限但如果哪天数据有问题形成环A 的父是 B、B 的父是 A递归会一直往下跑直到报错——这反而是个免费的死循环检测。从那以后我每次拿到这类文件都先跑一遍第 2.3 节的四条校验再跑一条SELECT level, COUNT(*) FROM ... GROUP BY level对量级最后才导入业务库。这三步加起来不到五分钟能挡掉后面百分之八十的玄学问题。希望帮到你。本文还有配套的精品资源点击获取