MySQL 城市代码表设计与实践：从基础到最佳实践

目录#

基础表结构设计
- 1.1 最简单的单表设计
- 1.2 层级化设计（省-市-区）
数据规范化与高级设计
- 2.1 遵循数据库范式
- 2.2 使用闭包表管理复杂层级
最佳实践与性能优化
示例用法与常见查询
- 4.1 基础查询
- 4.2 层级关联查询
数据获取与维护
总结
参考资料

一、基础表结构设计#

1.1 最简单的单表设计#

对于需求简单、数据量不大的项目，可以将所有省、市、区县数据放在一张表中，通过一个 parent_id 字段来建立层级关系。

表结构示例：

CREATE TABLE `sys_region` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键，唯一标识',
  `code` varchar(20) NOT NULL COMMENT '行政区划代码（如：110100）',
  `name` varchar(50) NOT NULL COMMENT '名称（如：北京市，市辖区）',
  `level` tinyint(1) NOT NULL COMMENT '层级：1-省/直辖市，2-市，3-区/县',
  `parent_id` int(10) UNSIGNED DEFAULT NULL COMMENT '父级ID，顶级节点的父ID为0或NULL',
  `pinyin` varchar(100) DEFAULT NULL COMMENT '名称拼音或缩写，用于搜索',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态：1-有效，0-无效',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_code` (`code`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_level` (`level`),
  KEY `idx_name` (`name`),
  KEY `idx_pinyin` (`pinyin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='行政区划表';

设计说明：

code：使用国家标准行政区划代码，具有唯一性，便于与外部系统对接。
level：明确标识层级，简化查询逻辑（例如，直接查询所有省份 WHERE level = 1）。
parent_id：实现树形结构的核心字段，使用索引 idx_parent_id 来优化基于父节点的查询。
pinyin：非常实用的字段，支持按拼音首字母或全拼进行快速搜索（例如，输入 “bj” 可搜索到 “北京”）。

1.2 层级化设计（省-市-区）#

当业务逻辑对省、市、区有明确的区分时，可以拆分成多张表。这种设计更符合范式，关联查询更清晰。

表结构示例：

-- 省份表
CREATE TABLE `sys_province` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL COMMENT '省代码（如：11）',
  `name` varchar(50) NOT NULL COMMENT '省份名称',
  `pinyin` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_code` (`code`)
) ENGINE=InnoDB COMMENT='省份表';
 
-- 城市表
CREATE TABLE `sys_city` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL COMMENT '城市代码（如：110100）',
  `name` varchar(50) NOT NULL COMMENT '城市名称',
  `pinyin` varchar(100) DEFAULT NULL,
  `province_id` int(10) UNSIGNED NOT NULL COMMENT '所属省份ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_code` (`code`),
  KEY `idx_province_id` (`province_id`),
  CONSTRAINT `fk_city_province` FOREIGN KEY (`province_id`) REFERENCES `sys_province` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB COMMENT='城市表';
 
-- 区县表
CREATE TABLE `sys_district` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL COMMENT '区县代码（如：110101）',
  `name` varchar(50) NOT NULL COMMENT '区县名称',
  `pinyin` varchar(100) DEFAULT NULL,
  `city_id` int(10) UNSIGNED NOT NULL COMMENT '所属城市ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_code` (`code`),
  KEY `idx_city_id` (`city_id`),
  CONSTRAINT `fk_district_city` FOREIGN KEY (`city_id`) REFERENCES `sys_city` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB COMMENT='区县表';

设计说明：

优点：结构清晰，外键约束保证了数据的引用完整性。查询特定城市的区县时，效率很高。
缺点：当需要查询完整的省-市-区三级路径时，需要进行多次 JOIN 操作。表结构相对固定，如果未来需要增加“乡镇”层级，则需要修改表结构。

二、数据规范化与高级设计#

2.1 遵循数据库范式#

上述的层级化设计已经部分遵循了第三范式（3NF），因为它消除了传递依赖（区县依赖于城市，城市依赖于省份）。确保每个字段只依赖于主键，是设计此类数据表的基本原则。

2.2 使用闭包表管理复杂层级#

对于层级深度不确定或需要高效查询任意层级节点关系（如查找一个节点的所有子孙节点）的场景，单表的 parent_id 模式可能效率不高（需要递归查询）。闭包表（Closure Table）是一种更好的解决方案。

它使用一个额外的表来专门记录节点之间的所有路径关系。

核心表结构：

-- 节点表（存放省市区基本信息）
CREATE TABLE `region_node` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `level` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 
-- 闭包表（记录路径）
CREATE TABLE `region_closure` (
  `ancestor` int(10) UNSIGNED NOT NULL COMMENT '祖先节点ID',
  `descendant` int(10) UNSIGNED NOT NULL COMMENT '后代节点ID',
  `depth` int(10) UNSIGNED NOT NULL COMMENT '深度（0表示自身）',
  PRIMARY KEY (`ancestor`, `descendant`),
  KEY `idx_descendant` (`descendant`),
  FOREIGN KEY (`ancestor`) REFERENCES `region_node` (`id`),
  FOREIGN KEY (`descendant`) REFERENCES `region_node` (`id`)
) ENGINE=InnoDB;

示例数据： 假设有北京市(id=1) -> 市辖区(id=2) -> 东城区(id=3) 的关系，闭包表将存储：

ancestor	descendant	depth
1	1	0
1	2	1
1	3	2
2	2	0
2	3	1
3	3	0

查询优势：

查询所有子节点： SELECT n.* FROM region_node n JOIN region_closure c ON n.id = c.descendant WHERE c.ancestor = 1 AND c.depth >= 1; (一次性查出，无需递归)
查询所有父节点： SELECT n.* FROM region_node n JOIN region_closure c ON n.id = c.ancestor WHERE c.descendant = 3 AND c.depth >= 1;

闭包表以空间换时间，非常适合读多写少的场景。

三、最佳实践与性能优化#

3.1 索引策略#

正确的索引是高性能查询的保障。必备的索引包括：

主键：通常为 id。
唯一索引：建立在 code 字段上，防止代码重复。
外键索引：如 parent_id, province_id, city_id。
常用查询字段索引：如 level, name, pinyin。对于 name 和 pinyin，如果主要用于前缀匹配（LIKE ‘北京%’），使用普通索引即可；如果用于全文搜索，可考虑 MySQL 的全文索引（FULLTEXT）。

3.2 选择合适的数据类型#

id：使用 UNSIGNED INT，足够容纳中国的行政区划数量。
code/name：使用 VARCHAR，并选择合适的长度。推荐使用 utf8mb4 字符集以支持所有 Unicode 字符（如生僻字）。
level/status：使用 TINYINT。

3.3 考虑使用数据字典表#

如果 level 字段的含义（1-省，2-市...）或 status 字段可能在代码中多处使用，建议创建一个数据字典表，避免在代码中写死“魔法数字”。

CREATE TABLE `sys_dict` (
  `dict_type` varchar(50) NOT NULL COMMENT '字典类型，如：region_level',
  `dict_code` varchar(50) NOT NULL COMMENT '字典编码，如：1',
  `dict_value` varchar(50) NOT NULL COMMENT '字典值，如：省份',
  PRIMARY KEY (`dict_type`, `dict_code`)
) ENGINE=InnoDB;
 
-- 插入数据
INSERT INTO `sys_dict` (`dict_type`, `dict_code`, `dict_value`) VALUES
('region_level', '1', '省份'),
('region_level', '2', '城市'),
('region_level', '3', '区县');

四、示例用法与常见查询#

4.1 基础查询#

查询所有省份（单表设计）：

SELECT * FROM sys_region WHERE level = 1 AND status = 1 ORDER BY code;

根据城市名模糊查询（支持拼音）：

SELECT * FROM sys_region
WHERE level = 2
  AND (name LIKE '%北京%' OR pinyin LIKE '%bj%')
  AND status = 1;

查询某个省下的所有城市（多表设计）：

SELECT c.* FROM sys_city c
WHERE c.province_id = (SELECT id FROM sys_province WHERE name = '广东省');

4.2 层级关联查询#

查询“北京市-市辖区-东城区”的完整路径（单表设计，使用递归CTE，MySQL 8.0+）：

WITH RECURSIVE region_path AS (
  SELECT id, name, parent_id, name as path
  FROM sys_region
  WHERE name = '东城区' -- 从叶子节点开始
  UNION ALL
  SELECT r.id, r.name, r.parent_id, CONCAT(r.name, ' -> ', rp.path)
  FROM sys_region r
  INNER JOIN region_path rp ON r.id = rp.parent_id
)
SELECT path FROM region_path WHERE parent_id IS NULL; -- 追溯到根节点

联查获取完整地址信息（多表设计）：

SELECT
  d.name as district_name,
  c.name as city_name,
  p.name as province_name
FROM sys_district d
JOIN sys_city c ON d.city_id = c.id
JOIN sys_province p ON c.province_id = p.id
WHERE d.name = '西湖区';

五、数据获取与维护#

数据来源：最权威的来源是国家统计局发布的年度行政区划代码。许多开源项目也会在 GitHub 上维护这些数据（例如 modood/Administrative-divisions-of-China）。
数据更新：行政区划并非一成不变。需要建立定期检查和更新的机制。对于线上系统，建议使用软删除（status 字段）而非物理删除，以保持历史数据的完整性。
初始化脚本：准备一个完整的 SQL 初始化脚本，方便在新环境部署。

六、总结#

设计一个优秀的 MySQL 城市代码表需要综合考虑业务需求、查询性能和数据维护性。

简单应用：从单表设计开始，利用 parent_id 和 level 字段，简单有效。
复杂或高性能应用：如果业务模型固定，省-市-区多表设计更清晰；如果层级复杂且查询需求多样，闭包表是强大的工具。
通用原则：不要忘记索引、外键约束（保证数据一致性）和恰当的字段类型。引入 pinyin 字段和数据字典表能极大提升开发效率和代码质量。

选择合适的方案，将为您的应用打下坚实的数据基础。

目录#