★ FEATURED ARTICLE 简介这套三级四级五级联动 SQL 文件面向数据库开发者、后端工程师与数据库管理员围绕国家、省份、市州等多层级行政区域数据提供建表、外键约束、层级查询与联动取数的完整示例可用于地址选择、区域管理、分级下拉等业务场景也适合作为 SQL 多表关联与数据库设计的学习素材。压缩包共 25 个文件约 22.91MB以 PHP 代码、Markdown 说明、YAML 配置、CSV 数据、SQL 脚本及 XML/JSON 配置为主其中 SQL 负责表结构、初始数据与查询逻辑CSV 便于批量导入区域数据PHP 与迁移、配置模块则展示联动接口和分层数据访问的实现思路。资源还涉及 JOIN 联合查询、触发器、存储过程、视图与索引优化等知识点可帮助读者理解外键完整性、层级查询和性能取舍。目录按源码、迁移、SQL、CSV、配置等模块组织便于按需查阅与二次改造。目前已有 26 人学习下载。1. 从一次「选完街道没反应」说起去年接了个后台项目用户地址要精确到社区。前端省、市、区县三级下拉跑得好好的产品加了两级选完街道之后社区框一直是空的接口返回 200 但 data 是空数组。查了半天发现后端拿着街道 ID 去查parent_id而导入的 sql 文件里社区数据压根没写进来——那份「三级四级五级联动sql文件」是分三个文件给的我只 source 了第一个。这件事之后我才老老实实把这类行政区划 sql 文件当成一套完整的数据工程来看而不是当成一段建表语句随手跑一下。这类 sql 文件解决的事情很具体把省、市、区县、街道、社区五个层级的行政区划数据装进一张或几张表配上能按父级取子级的查询结构让前端的级联选择器有数据可取。它适合正在做地址录入、门店选址、配送范围、数据大屏区域筛选的开发者尤其是那些不打算为了一个下拉框去接第三方开放接口的项目。下面按「表怎么设计 → 数据怎么导 → 查询怎么发 → 哪里容易踩坑 → 怎么加自定义层级」的顺序把我拆这份 sql 文件的过程写清楚。2. 核心模块拆解一张自关联表怎么撑起五级联动三级、四级、五级联动听起来像三套东西实际落地时几乎都是同一套结构区别只在数据的覆盖深度。省市区是三级加上街道是四级再加社区就是五级。数据文件拆成三份是为了让不需要街道和社区的项目只导入前一份少几万行噪音数据。理解这一点后面的表设计就不用反复推倒重来。2.1 两种主流方案parent_id 自关联与路径枚举自关联方案是这类 sql 文件里最常见的做法一张表存所有层级用parent_id指向上一级根节点的parent_id填 0。层级不靠表结构体现靠一个level字段标记。它的好处是增删层级只改数据不改表前端要几级就查几级坏处是查一棵子树要递归MySQL 8 之前没有WITH RECURSIVE只能靠应用层一层层拼。路径枚举是在自关联基础上加一列path存从根到自己的 ID 链比如0,1,10,103。查某个节点的所有后代就变成一次LIKE prefix%前缀匹配不用递归。这类 sql 文件如果只给了parent_id没给path导入之后可以自己补一列一条 UPDATE 就能刷出来。我在数据量超过五万行的场景里基本都会补这一列因为级联组件偶尔要「反查整条路径」每次递归上去很浪费。还有一种是闭包表单独建一张region_path(ancestor, descendant, depth)存所有祖先-后代关系。查询最灵活任意层级的关系一次 JOIN 就能拿到代价是表膨胀明显——五万行数据大概会生成几十万行关系记录。除非项目里要做「查某市下所有社区」这类复杂统计否则为一份现成的 sql 文件加闭包表属于过度设计我不推荐。2.2 建表 SQL 与字段约定下面是我在这类 sql 文件基础上整理的建表语句字段名和注释按实际项目习惯补全。这份表结构能同时承载三级、四级、五级数据导入时靠level区分。CREATE TABLE sys_region ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, parent_id BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 父级ID0表示根节点, name VARCHAR(64) NOT NULL COMMENT 区域名称, short_name VARCHAR(32) DEFAULT NULL COMMENT 简称用于列表窄列展示, level TINYINT UNSIGNED NOT NULL COMMENT 层级1省 2市 3区县 4街道 5社区, code VARCHAR(12) NOT NULL COMMENT 行政区划编码长度随层级变化, path VARCHAR(255) NOT NULL DEFAULT COMMENT 从根到当前节点的ID路径逗号分隔, has_child TINYINT NOT NULL DEFAULT 0 COMMENT 是否有下级0无 1有, sort INT NOT NULL DEFAULT 0 COMMENT 同级排序值, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1启用 0停用, PRIMARY KEY (id), UNIQUE KEY uk_code (code), KEY idx_parent_sort (parent_id, sort), KEY idx_level (level), KEY idx_path (path) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT行政区划五级联动表;parent_id和level是联动的命脉level建议在导入阶段就写死不要靠查询时数父级推算否则每次渲染下拉都要多打几轮数据库。has_child是给前端懒加载用的级联组件判断一个节点该不该显示右侧箭头看这个字段比再发一次「有没有子节点」的请求便宜得多。code上的唯一索引要留意。三级、四级数据的编码通常是 6 位、9 位五级社区编码常见 12 位但不同来源的数据文件里偶尔会出现同一编码挂在两个父级下面的情况——这在街道层级调整时很常见。导入前先确认唯一索引会不会顶掉数据如果数据源不保证唯一就把uk_code降级成普通索引idx_code把唯一性校验放到业务侧。path这一列不在原始 sql 里是我后加的。加完之后层级查询从递归变成前缀匹配改动成本很低-- 先给根节点刷上 path UPDATE sys_region SET path CONCAT(0,, id) WHERE parent_id 0; -- 再按层级逐级向上拼接level 2 到 5 各跑一次 UPDATE sys_region c JOIN sys_region p ON c.parent_id p.id SET c.path CONCAT(p.path, ,, c.id) WHERE c.level 2;这段要按level从 2 递增到 5 依次执行因为后一级依赖前一级的path已经算好。顺序跑反了p.path还是空串拼出来的路径只剩自己的 ID后面所有前缀查询都会失准。我一般会把这几条写进一个init_path.sql导入数据后固定执行一次。2.3 数据文件的组织方式三份 sql 怎么选这类资源通常按层级拆成三个文件命名大致是region_level3.sql、region_level4.sql、region_level5.sql。三份文件不是互斥关系而是层层追加三级文件建表并插入省市县四级文件追加街道五级文件追加社区。所以导入顺序必须是 3 → 4 → 5跳着导会出现社区记录的parent_id指向一个不存在的街道。如果项目只需要三级就只跑第一份表结构和查询代码完全不用改level最大值是 3 而已。这种「表结构统一、数据分层给」的做法比给三套不同表结构要友好得多因为二次开发时想从三级升到四级只需要补导数据不用改代码里的表名。单份文件动辄几万到十几万行 INSERT用客户端工具打开会卡。我的习惯是全程用命令行 source不进 GUI。下一章展开具体步骤。3. 导入实战把 sql 文件跑进 MySQL拿到 sql 文件之后第一件事不是急着执行而是先看头部的建表语句和字符集声明再看尾部有没有SET FOREIGN_KEY_CHECKS之类的开关。这一步花两分钟能省掉后面半小时的乱码排查。3.1 环境准备与命令行导入先建库字符集直接定成utf8mb4别用默认的latin1或utf8。区域名称里出现生僻字和少数民族文字的概率不低三字节的utf8存不下四字节字符导入时会直接报Incorrect string value。# 建库时就锁死字符集避免后面表继承到错的默认值 mysql -uroot -p -e CREATE DATABASE IF NOT EXISTS demo_region DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;然后按顺序导入三份文件。把--max_allowed_packet调大是因为单条 INSERT 批量插入几百行时默认 4M 的包大小容易被顶爆报Packet too large之后整个导入中断前面已经插进去的数据还得手工清。# 依次导入三级、四级、五级数据顺序不能乱 for f in region_level3.sql region_level4.sql region_level5.sql; do echo importing $f mysql -h127.0.0.1 -P3306 -uroot -pyour_password \ --default-character-setutf8mb4 \ --max_allowed_packet64M \ demo_region $f || { echo failed: $f; exit 1; } done--default-character-setutf8mb4这个参数必须显式带上。客户端默认字符集和文件实际编码不一致时中文会变成问号或者一串浙江这样的乱码而且导入过程不报错等你打开表看到一堆问号才发现那时候只能删表重来。导入完成后确认行数三级数据大致在三千行量级四级加进去接近五万行五级会到六七十万行。数字对不上就说明某一份文件没跑完。-- 按层级统计确认三份文件都进了 SELECT level, COUNT(*) AS cnt FROM sys_region GROUP BY level ORDER BY level;3.2 字符集、事务与导入速度大文件导入慢是常态几十万行逐条 INSERT 在默认配置下跑十几分钟不稀奇。想快一点可以在导入前临时关掉几个检查项跑完再打开-- 导入会话内关闭自动提交和唯一性检查显著提速 SET autocommit 0; SET unique_checks 0; SET foreign_key_checks 0; SOURCE /path/to/region_level3.sql; SOURCE /path/to/region_level4.sql; SOURCE /path/to/region_level5.sql; COMMIT; SET unique_checks 1; SET foreign_key_checks 1;unique_checks 0关掉的是导入期间对唯一索引的校验代价是如果源数据本身有重复code重复行会被静默写进去等重新打开检查时才在后续写入时报错。所以我一般只在大批量首次导入时这么干跑完立刻做一次重复检查下一节的 SQL 里有。还有一个提速点是改表引擎的插入顺序。如果 sql 文件里的 INSERT 是乱序的省市区混在一起可以先把表建成 MyISAM 导完再ALTER TABLE ... ENGINEInnoDB能快将近一倍。不过这招在 MySQL 8 上收益已经不明显我现在的做法是保持 InnoDB靠调大innodb_buffer_pool_size和innodb_flush_log_at_trx_commit2来提速改配置比换引擎稳妥。如果导入过程中断了别急着重跑整份文件。先看表里已有的最大层级从断点那份文件的对应位置继续。用tail -n 行号截取剩下的部分导入比从头再来省时间。3.3 导入后的自检 SQL数据进去之后必须做三类检查这三条 SQL 我基本是固定写在项目的check.sql里每次换数据源都跑一遍。-- 1. 断链检测parent_id 指向了一个不存在的父节点 SELECT c.id, c.name, c.parent_id FROM sys_region c LEFT JOIN sys_region p ON c.parent_id p.id WHERE c.parent_id 0 AND p.id IS NULL; -- 2. 同级重名同一个父级下出现两个同名区域前端会显示重复项 SELECT parent_id, name, COUNT(*) AS cnt FROM sys_region GROUP BY parent_id, name HAVING cnt 1; -- 3. has_child 与实际子节点数不一致会导致级联组件箭头显示错误 SELECT p.id, p.name, p.has_child, COUNT(c.id) AS real_children FROM sys_region p LEFT JOIN sys_region c ON c.parent_id p.id GROUP BY p.id, p.has_child HAVING (p.has_child 1 AND real_children 0) OR (p.has_child 0 AND real_children 0);第一条查出来的断链记录常见原因是四级文件里出现了三级文件没覆盖到的区县多半是数据源版本不一致比如三级数据是旧版、四级是新版。这种只能换同一批数据源或者手工补父节点。第三条的结果可以用一条 UPDATE 修掉不用逐行改-- 用子查询重算 has_child修掉箭头显示不对的问题 UPDATE sys_region p SET p.has_child EXISTS ( SELECT 1 FROM (SELECT parent_id FROM sys_region WHERE parent_id 0) c WHERE c.parent_id p.id );MySQL 不允许在 UPDATE 里直接子查询同一张表所以中间套了一层派生表。这条跑完之后前端懒加载的leaf判断就准了。4. 查询链路拆解一次省市区街道选择要发几条 SQL表建好、数据导完接下来是联动本身。前端级联组件有两种工作模式一次性把所有选项加载到内存或者点一级加载一级。对应的后端接口和 SQL 完全不同选错了要么页面卡死要么请求数爆炸。4.1 懒加载模式按 parent_id 取下一级懒加载是默认推荐的做法尤其数据到了五级。用户点开省才查这个省下的市点开市才查区县。单次请求只返回几十行接口响应稳定在几十毫秒。-- 取某个节点的直接下级parentId 为 0 时返回所有省 SELECT id, name, short_name, level, has_child FROM sys_region WHERE parent_id ? AND status 1 ORDER BY sort ASC, id ASC;参数只有一个parent_id。ORDER BY sort, id这个组合不能省sort是人工维护的展示顺序直辖市排在前面、开发区排在后面之类的业务需求相同sort时用id兜底保证结果稳定否则同一次刷新两次顺序不一样用户会觉得列表在跳。idx_parent_sort这个联合索引就是为这条查询建的走索引之后即使表里有七十万行取某一级的子节点也是毫秒级。后端接口按这个思路写用 Node Express 举例核心只有几行// GET /api/regions?parentId0 懒加载取下一级 router.get(/api/regions, async (req, res) { // parentId 缺省为 0即根层级转成数字防止注入和类型不匹配 const parentId Number(req.query.parentId ?? 0); if (!Number.isInteger(parentId) || parentId 0) { return res.status(400).json({ code: 400, msg: parentId 非法 }); } // 走 idx_parent_sort 索引只取必要字段别 SELECT * const [rows] await pool.query( SELECT id, name, level, has_child FROM sys_region WHERE parent_id ? AND status 1 ORDER BY sort ASC, id ASC, [parentId] ); // 前端级联组件需要 leaf 标记has_child 取反即可 res.json({ code: 0, data: rows.map(r ({ ...r, leaf: r.has_child 0 })) }); });这里把has_child映射成前端组件通用的leaf字段是为了让前端不用关心业务字段名。parentId做了整数校验因为拼字符串进 SQL 是这类接口最常见的注入点虽然用了占位符参数类型不对一样会让索引失效——传0和传0在 MySQL 里走的是不同的执行计划。懒加载的代价是用户每展开一级就发一次请求从省选到社区一共四次。四次往返在网络差的环境下会有明显顿挫。缓解办法是选中某一级之后预取它的子级用户还没点就已经在本地了。4.2 全量树模式一次拉平缓存另一条路是启动时把所有数据查出来在内存里拼成树接口只做数组过滤。数据量在五万行以内也就是到四级时这条路很舒服一次查询、零次网络往返。-- 全量拉取只取建树必需的字段控制单次返回体积 SELECT id, parent_id, name, level, has_child, sort FROM sys_region WHERE status 1 ORDER BY parent_id ASC, sort ASC, id ASC;七十万行五级数据这么拉肯定不行返回体几百兆Node 序列化就卡住了。所以全量树模式我只在三级、四级场景用五级一律懒加载加缓存。判断标准很简单全量 JSON 序列化后超过 2MB就该换方案。如果只用路径前缀查子树加过path列之后一条 SQL 就够不用在应用层递归-- 查某节点下所有后代最多到指定层级 SELECT id, parent_id, name, level FROM sys_region WHERE path LIKE CONCAT((SELECT path FROM sys_region WHERE id ?), ,%) AND level ? ORDER BY level ASC, sort ASC;LIKE 前缀%能用到idx_path索引因为前缀是确定的。这里有个细节拼接的时候必须加上逗号,%只写CONCAT(path, %)会把1,10和1,100混在一起后者本来不是前者的后代却被查出来。这种错误在测试环境数据少的时候看不出来上线后某个区县多出几十个不相干的社区才发现属于典型的血泪经验。4.3 反查路径与回显编辑页面要回显用户已选的五级地址得从社区 ID 一路往上找到省。用path列就是一次查询加字符串切分-- 根据叶子节点 ID 反查完整路径用于编辑页回显 SELECT id, parent_id, name, level, path FROM sys_region WHERE id ? OR FIND_IN_SET(id, (SELECT path FROM sys_region WHERE id ?)) ORDER BY level ASC;第二条FIND_IN_SET拿path里的 ID 串去匹配返回的是一串按层级排好序的记录前端直接按level顺序填进级联组件的v-model数组即可。如果没有path列就得在应用层写循环查父级五级地址五次查询接口耗时翻五倍。这就是我前面坚持补path的原因。5. 避坑与排查导入和联动里最容易翻车的几处前面几章的代码都是顺利路径实际项目里真正耗时间的是下面这些。每条按现象、原因、解决写都是我或者同事实际遇到过的。5.1 导入后中文全是问号现象表能建行数也对但name字段打开一看全是???前端下拉框里一排问号。原因sql 文件本身是 UTF-8 编码但客户端连接用了默认的latin1转码过程中四字节字符全部丢失写入时就变成了问号。这种情况导入过程不报错属于最坑的一类。解决先确认文件编码file -i region_level5.sql再确认导入命令带--default-character-setutf8mb4最后确认表和库的字符集都是utf8mb4。三处任何一处不对都会出问题。已经导错的表没法修补只能TRUNCATE后重导。5.2 选完街道社区下拉是空的现象接口返回 200data是空数组但数据库里明明有社区数据。原因两种可能。一是只导了三级和四级文件五级文件没执行二是社区的parent_id在数据源里指向的街道 ID和实际导入后的自增 ID 对不上——如果 sql 文件里没显式指定id靠自增生成后面再单独导入五级文件时自增起点变了父子关系就断了。解决先跑 3.3 的断链检测 SQL能查出来就是第二种。修法是导入时保留原始 ID不要靠自增也就是 sql 文件里的 INSERT 必须带id列。如果文件里没带就只能按code前缀重新关联父级这个工作量不小所以拿到文件第一件事就看 INSERT 有没有指定主键。5.3 级联组件箭头一直显示点开却是空的现象某个区县右侧有展开箭头点开之后没有街道组件卡住不响应。原因has_child字段标成了 1但实际没有子节点。多半是数据源里有已撤销的区县父级记录还在下级数据被清理了而has_child没同步更新。解决跑 3.3 第三条 SQL 找出不一致的记录用那条 UPDATE 重算全表has_child。更省事的做法是干脆不用has_child字段前端首次展开时无论有没有子级都发请求返回空数组再标记leaf多一次请求换掉一类 bug看项目取舍。5.4 同一父级下出现两个重名的区县现象下拉列表里出现两行完全一样的名字用户不知道选哪个。原因数据源合并了两个版本的数据同一条记录导了两遍但code不同所以唯一索引没拦住或者id是自增的重复导入时生成了新 ID。解决先跑 3.3 第二条 SQL 定位。合并时优先保留code较新的一条另一条把status置 0 而不是物理删除因为历史订单里可能还引用着旧 ID直接删会导致地址回显时查不到名称。这条教训是数据治理里的常规操作别图省事DELETE。5.5 导入到最后一行报 Packet too large现象导入跑到大半突然报MySQL server has gone away或Packet too large前面已插入的数据还在。原因默认max_allowed_packet是 4M五级文件末尾如果有超大的一条批量 INSERT会直接超过限制。解决导入命令加--max_allowed_packet64M服务端my.cnf里的同名参数也要调到一致只改客户端不生效。已经中断的导入不用重来用SELECT MAX(id) FROM sys_region拿到断点再用tail -n N截取文件剩余部分续导。6. 二次开发加第六级、换存储、做缓存这份 sql 文件的数据结构是开放的改起来不复杂但有几个地方值得提前想清楚。最常见的需求是加第六级网格、楼栋表结构完全不用动只要约定level 6父级指向社区 ID 就行。真正要改的是三处前端级联组件的props配置要放开层级限制后端接口的level上限校验要放宽以及has_child的重算逻辑要覆盖到第 6 层。改完之后跑一次 3.3 的断链检测确认新层级的数据父级都对得上。第二个场景是数据量上去之后的性能。下面这张表是我在不同项目里用过的三种查询方案按数据规模选方案适用数据量单次请求 SQL 数优点代价懒加载逐级查五级几十万行以上每展开一级 1 条内存占用低首屏快展开有网络往返全量树内存过滤三级、四级五万行内1 条启动时零往返实现简单数据更新要重启或刷新Redis 缓存 懒加载任意规模命中缓存 0 条兼顾速度与体积需处理缓存失效缓存这块我的做法是按parent_id做 key值存该父级下的子节点数组 JSONTTL 设一天。数据变更频率极低一天一次全量刷新完全够用。预热脚本就是遍历所有has_child 1的节点批量查一次再写进 Redis# 缓存预热按 parent_id 分组写入 Rediskey 形如 region:children:100 import json, pymysql, redis conn pymysql.connect(host127.0.0.1, userroot, passwordpwd, databasedemo_region, charsetutf8mb4) rds redis.Redis(host127.0.0.1, port6379, db0) with conn.cursor(pymysql.cursors.DictCursor) as cur: # 一次查出所有节点在内存里分组避免 N1 查询 cur.execute(SELECT id, parent_id, name, level, has_child FROM sys_region WHERE status 1) buckets {} for row in cur.fetchall(): buckets.setdefault(row[parent_id], []).append(row) # pipeline 批量写减少往返TTL 一天 pipe rds.pipeline() for parent_id, children in buckets.items(): pipe.setex(fregion:children:{parent_id}, 86400, json.dumps(children, ensure_asciiFalse)) pipe.execute()这段的关键是先全量查一次再内存分组而不是对每个父级发一条查询。七十万行数据全量拉一次大概几秒如果按父级循环查五万个父级就是五万次往返预热脚本能跑一晚上。ensure_asciiFalse必须加否则 Redis 里存的是\uXXXX转义串取出来还得再解一次。第三个改造点是数据更新。区域数据不是一成不变的撤县设区、街道合并每年都有。我的做法是保留一个version字段或者在表外单独维护一张region_version表记录每次导入的数据版本和时间。前端接口返回时带上版本号页面拿到的版本和当前不一致就提示刷新。这个机制听着多余等真遇到用户在旧数据页面上选了一个已经不存在的街道、提交时报外键约束失败就知道有多需要。最后说个我自己的习惯。这类 sql 文件我从来不在生产库上直接导一定先在本地建一份同名库跑通跑完跑一遍 3.3 的三条自检 SQL确认断链、重名、has_child都为 0 条记录再打包成带版本号的脚本走发布流程。从那以后我每次接新的区域数据源都强制走这一遍看着麻烦实际比事后修数据省事得多。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
