简介：面向城市规划研究者、数据分析人员与投资决策者的城市分级数据包，围绕2020年城市商业魅力排行榜，将全国城市按经济规模、人口吸引力、创新能力与交通便利性等维度划入一至五线城市，可用于区域对比、趋势研究与市场机会判断。包内共2个文件，含一份xlsx表格与一份sql数据库脚本，压缩包约17KB，表格便于直接筛选查看各等级城市名单，sql文件以自增id、城市名称、城市等级三字段建表，方便导入数据库后做查询与统计分析。已有10404人学习下载，说明该数据在同类资料中关注度较高。读者可据此快速获得完整的城市等级对照清单，并借助SQL按等级检索、聚合与横向比较，为政策研究、企业选址或个人分析提供可直接复用的基础数据。

1. 城市等级划分表：一份被低估的“脏数据”治理样本

拿到「2020中国城市等级划分（一二三四五线城市及排名）excel表格及sql文件」这个标题，很多人第一反应是找一份现成的表导进数据库就完事。真做过的人都知道，这类城市分级数据落地时最麻烦的从来不是“有没有表”，而是同一座城市在 Excel 里叫“XX市”、在业务系统里叫“XX”、在物流面单上又是拼音缩写，一线到五线的划分口径还随榜单年份漂移。它本质上是一份典型的维表（dimension table），要解决的是地址标准化、区域归属和分级标签三件事。适合谁：做用户地域分析、门店选址、区域运营报表、CRM 地址清洗的从业者。下面按“先立数据模型、再复现导入、再排坑、最后做校验”的顺序讲透，新手能照着跑，熟手能直接对参数和边界。

2. 先把城市分级维表的数据模型立住：字段、粒度与分级口径

动手建表之前，得先想清楚这张表到底服务什么查询。城市等级划分表看着简单，实际至少承载三类需求：按城市查等级（点查）、按等级聚合统计（分组）、按城市名做模糊匹配清洗地址（关联）。这三类需求对字段和索引的要求完全不同，模型立错了，后面导入再多数据也是返工。

2.1 一张城市维表最少要有哪些字段

我一般会把字段拆成四组：标识组、名称组、层级组、分级组。标识组是主键和行政编码，名称组放全称/简称/拼音，层级组放省、市、区县和行政级别，分级组放一线到五线的标签和排名。下面这张表是我常用的最小字段集，Excel 列名和 SQL 列名一一对应，方便导入脚本直接映射。

关键点在于admin_code和city_name的分工：行政编码稳定、城市名会变（撤县设区、改名），所以关联一律走编码，展示才用名称。tier用 TINYINT 而不是字符串，是为了让“一线到五线”能直接做数值比较和排序，省掉一堆 CASE WHEN。

2.2 分级口径为什么必须先定死再导数据

一线到五线的划分不是官方行政标准，而是商业机构按经济活力、人口、消费等维度打的标签，不同榜单差异不小。落地时最容易翻车的地方，是同一份 Excel 里一线城市 4 个、新一线 15 个，而业务方嘴里的“一线”可能只认 4 个。我的做法是在建表阶段就把口径写进注释和一张配置表，而不是散落在代码里。

常见做法是：tier只保留 1 到 5 五档，把“新一线”并入 2 档但在tier_rank里保留原始排名，另加一个tier_label字段存原始榜单文字（如“新一线城市”）。这样既满足“一二三四五线”的粗粒度聚合，又不丢原始信息。如果业务只要五档，查询时直接GROUP BY tier；如果要还原榜单，读tier_label。这一步想清楚，后面导入脚本的字段映射就不用反复改。

2.3 主键与唯一约束怎么设才不返工

我见过太多人拿city_name当主键，结果遇到“市辖区重名”“同名县级市跨省”直接崩。正确姿势是city_id自增主键 +admin_code唯一索引 +(city_name, province)普通联合索引。唯一索引放在admin_code上，导入时用INSERT ... ON DUPLICATE KEY UPDATE做幂等，重复跑脚本不会产生脏数据。联合索引服务于“按城市名+省”这种最常见的地址清洗查询，比单列索引命中率高。

提示：如果拿不到可靠的行政区划编码，宁可留空并加is_verified标记位，也不要用城市名拼一个假编码，否则后面关联全是坑。

3. 从 Excel 到 SQL：导入脚本、建表语句与幂等重跑

模型定完就进入动手环节。这一章给的是能直接抄的建表语句和导入脚本，Excel 到数据库的路径用 Python 打通，参数和失败排查都写清楚。假设 Excel 文件名为city_tier_2020.xlsx，sheet 名cities，列名与 2.1 的字段表一致。

3.1 建表 SQL 与索引设计

先建表。字符集统一 utf8mb4，避免城市名里的生僻字或少数民族地名乱码。tier和city_level都加 CHECK 约束（MySQL 8.0 支持），把非法值挡在入库前。

CREATE TABLE dim_city_tier ( city_id INT AUTO_INCREMENT PRIMARY KEY, admin_code VARCHAR(12) NOT NULL COMMENT '行政区划编码，稳定关联键', city_name VARCHAR(64) NOT NULL COMMENT '城市全称，带市/州/盟后缀', short_name VARCHAR(64) DEFAULT NULL COMMENT '去后缀简称，用于模糊匹配', pinyin VARCHAR(128) DEFAULT NULL COMMENT '全拼，地址清洗回退用', province VARCHAR(64) NOT NULL COMMENT '所属省级，直辖市填自身', city_level TINYINT NOT NULL COMMENT '1直辖市 2省会 3地级市 4县级市', tier TINYINT NOT NULL COMMENT '商业分级 1-5 对应一线到五线', tier_rank INT DEFAULT NULL COMMENT '同 tier 内排名', tier_label VARCHAR(32) DEFAULT NULL COMMENT '原始榜单文字，如新一线城市', data_year SMALLINT NOT NULL DEFAULT 2020 COMMENT '数据年份', is_verified TINYINT NOT NULL DEFAULT 0 COMMENT '编码是否可信', UNIQUE KEY uk_admin_code (admin_code), KEY idx_name_prov (city_name, province), KEY idx_tier (tier, tier_rank), CONSTRAINT chk_tier CHECK (tier BETWEEN 1 AND 5), CONSTRAINT chk_level CHECK (city_level BETWEEN 1 AND 4) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='城市等级划分维表';

uk_admin_code是幂等导入的基础，idx_tier让“按等级聚合”走索引而不是全表扫。CHECK 约束看着多余，实际能拦住 Excel 里手滑填的“0”或“6”，省掉后面排查半天才发现是脏值。

3.2 用 Python 把 Excel 读进来并做字段清洗

导入脚本的核心不是“读进来”，而是“读进来之前把脏值处理掉”。下面这段脚本做了四件事：列名映射、去空格、简称生成、空值兜底。依赖pandas和openpyxl，sqlalchemy负责写库。

import pandas as pd from sqlalchemy import create_engine, text # 1. 读 Excel，指定 sheet 和列名映射 df = pd.read_excel("city_tier_2020.xlsx", sheet_name="cities") col_map = { "行政编码": "admin_code", "城市": "city_name", "省份": "province", "行政级别": "city_level", "分级": "tier", "排名": "tier_rank", "榜单文字": "tier_label", } df = df.rename(columns=col_map) # 2. 清洗：去空格、统一后缀、生成简称 df["city_name"] = df["city_name"].astype(str).str.strip() df["province"] = df["province"].astype(str).str.strip() df["short_name"] = df["city_name"].str.replace(r"(市|州|盟|地区)$", "", regex=True) # 3. 空值兜底：编码缺失标记未核验，分级缺失直接丢弃 df["admin_code"] = df["admin_code"].fillna("").astype(str).str.strip() df["is_verified"] = (df["admin_code"] != "").astype(int) df = df[df["tier"].notna()].copy() df["tier"] = df["tier"].astype(int) # 4. 幂等写入：按 admin_code 冲突则更新 engine = create_engine("mysql+pymysql://user:pwd@127.0.0.1:3306/demo?charset=utf8mb4") with engine.begin() as conn: for _, row in df.iterrows(): conn.execute(text(""" INSERT INTO dim_city_tier (admin_code, city_name, short_name, province, city_level, tier, tier_rank, tier_label, data_year, is_verified) VALUES (:admin_code, :city_name, :short_name, :province, :city_level, :tier, :tier_rank, :tier_label, 2020, :is_verified) ON DUPLICATE KEY UPDATE city_name=VALUES(city_name), short_name=VALUES(short_name), province=VALUES(province), city_level=VALUES(city_level), tier=VALUES(tier), tier_rank=VALUES(tier_rank), tier_label=VALUES(tier_label), is_verified=VALUES(is_verified) """), row.to_dict())

逻辑说明：col_map把中文列名映射成英文，避免脚本里到处写中文键；short_name用正则去掉行政后缀，是地址模糊匹配的关键；is_verified把“编码缺失”显式化，而不是悄悄填个假值。ON DUPLICATE KEY UPDATE保证脚本可以反复跑，第二次跑不会插重复行，只会刷新字段。

参数说明：create_engine里的charset=utf8mb4必须带，否则生僻地名会截断；data_year在 SQL 里写死 2020，是因为标题锁定了年份，如果以后要导多版本，把它改成从 DataFrame 取列即可。逐行execute在几千行量级完全够用，上万行建议换executemany或to_sql(method="multi")。

3.3 导入后必做的三条校验查询

导完不校验等于没导。我固定跑三条 SQL：总数对不对、分级分布合不合理、有没有重复城市名跨省。

-- 1. 总行数与分级分布，和 Excel 原始透视表对得上 SELECT tier, COUNT(*) AS cnt FROM dim_city_tier GROUP BY tier ORDER BY tier; -- 2. 同一城市名出现在多个省，多半是县级市重名，需人工确认 SELECT city_name, COUNT(DISTINCT province) AS prov_cnt FROM dim_city_tier GROUP BY city_name HAVING prov_cnt > 1; -- 3. 编码未核验的记录，导入后要单独跟进 SELECT COUNT(*) FROM dim_city_tier WHERE is_verified = 0;

第一条对不上，说明 Excel 里有被过滤掉的行（比如 tier 为空），回看 3.2 的df[df["tier"].notna()]；第二条结果多，说明简称或全称没统一，得回到清洗步骤补规则；第三条数量大，说明编码来源不可靠，这批数据只能做展示不能做关联。

4. 城市分级数据落地避坑：五条血泪经验

这一章全是踩过的坑，每条按“现象→原因→解决”写，照着排能省不少时间。

4.1 现象：导入报 Duplicate entry，脚本中断

原因：Excel 里同一城市出现两行（比如榜单把某地级市和它的某个区都列了），而admin_code唯一索引拦住了第二行。解决：导入前先df.drop_duplicates(subset=["admin_code"], keep="last")，并在日志里打印被去重的行，人工确认保留哪条。别图省事直接INSERT IGNORE，那会把真正的数据冲突也吞掉。

4.2 现象：按城市名关联，匹配率只有六成

原因：业务表里存的是“XX”，维表里是“XX市”，或者维表简称规则没覆盖“自治州”“地区”。解决：关联时用short_name做一级匹配，匹配不上再用pinyin做二级回退，两级都失败才落人工。简称正则要覆盖市|州|盟|地区|自治州，别只写市。

4.3 现象：分级聚合结果和业务方口径对不上

原因：把“新一线”当成了独立一档，或者把县级市算进了地级市。解决：tier只保留 1 到 5，新一线并入 2 档但tier_label保留原文；city_level单独表达行政级别，两个维度不要混用。聚合前先和业务方对齐“一线到底几个”，把结论写进表注释。

4.4 现象：中文城市名入库变问号

原因：连接串没带charset=utf8mb4，或者表/列字符集是 utf8（三字节），遇到四字节生僻字截断。解决：建表、连接串、客户端三处字符集统一 utf8mb4，导入后用SELECT city_name FROM dim_city_tier WHERE city_name LIKE '%?%'抽查。

4.5 现象：重跑脚本后排名错乱

原因：tier_rank在 Excel 里是文本型数字，astype(int)前没处理空串，导致部分行写成 0 或 NULL，排序全乱。解决：清洗阶段pd.to_numeric(df["tier_rank"], errors="coerce")，空值保留 NULL 而不是填 0，排序时ORDER BY tier_rank IS NULL, tier_rank把空值排到最后。

5. 用分级维表做地址标准化：一个可复用的匹配函数与验收方法

维表建好只是开始，真正体现价值的是拿它去清洗业务里的脏地址。这一章给一个可复用的匹配函数，再讲怎么验收匹配质量，最后落到一个我常用的技巧上。

5.1 三级匹配函数：全称、简称、拼音回退

匹配策略按精确度从高到低：先全称精确匹配，再简称匹配，最后拼音前缀匹配。每级命中都打上match_level标记，方便后续按置信度分流。

import pandas as pd def match_city(raw: str, dim: pd.DataFrame) -> dict: """raw 为业务侧原始城市串，dim 为维表 DataFrame""" s = str(raw).strip() # 一级：全称精确 hit = dim[dim["city_name"] == s] if len(hit) == 1: return {"city_id": hit.iloc[0]["city_id"], "match_level": "exact"} # 二级：简称精确 hit = dim[dim["short_name"] == s] if len(hit) == 1: return {"city_id": hit.iloc[0]["city_id"], "match_level": "short"} # 三级：拼音前缀，取最长命中 cand = dim[dim["pinyin"].fillna("").str.startswith(s.lower())] if len(cand) >= 1: best = cand.sort_values("pinyin", key=lambda x: x.str.len(), ascending=False).iloc[0] return {"city_id": best["city_id"], "match_level": "pinyin"} return {"city_id": None, "match_level": "miss"}

逻辑说明：每级只在唯一命中时才返回，多命中一律降级到下一级或落 miss，避免“同名跨省”被错误合并。match_level是后续验收的分组维度。参数上，拼音匹配用startswith而不是contains，因为地址里常带“XX新区”“XX开发区”，contains会误命中。

5.2 匹配质量怎么验收：分层抽样看命中率

别只看总体命中率，要按match_level分层看。我的验收标准是：exact 占比应超过七成，short 占两成左右，pinyin 和 miss 合计不超过一成。pinyin 命中里如果出现大量“新区/开发区”被误配到主城区，说明维表缺新区条目，得补数据而不是改函数。抽样时重点看 miss 样本，往往能反推出维表缺哪些县级市。

5.3 一个我常用的技巧：把分级标签做成可配置映射

分级口径会随榜单年份变，硬编码在 SQL 里迟早返工。我的习惯是单独建一张cfg_tier_mapping配置表，存tier到业务标签的映射和生效年份，查询时 JOIN 取标签。这样换年份只改配置不动主表，回滚也有后悔药。验收时跑一遍SELECT tier, COUNT(*) FROM dim_city_tier GROUP BY tier和配置表对账，数字对得上才算这次导入收工。这套流程我跑过好几版城市数据，最深的教训是：别信“一份表导进去就完事”，城市分级数据的价值全在清洗和校验那几步，前面偷的懒，后面都会以匹配率对不上的形式还回来。希望帮到你。

本文还有配套的精品资源，点击获取