城市分级表导入数据库实战：从Excel到SQL的完整指南-育师

简介：面向城市规划研究者、数据分析人员与投资决策者的城市分级数据工具包，围绕2020年城市商业魅力排行榜，将全国城市按经济规模、人口吸引力、创新能力与交通便利性等维度划入一至五线城市，可用于区域对比、趋势研究与市场机会判断。压缩包共2个文件，约17KB，包含一份xlsx表格与一份sql数据库文件：表格便于直接浏览与筛选各等级城市名单，sql文件以自增id、城市名称、城市等级三个字段建表，方便导入数据库后做查询统计与二次开发。已有10404人学习下载，说明其在同类数据中具备一定参考价值。读者可据此快速获得结构清晰的城市等级底表，结合自身业务完成人口流动、产业结构、教育资源分布等维度的交叉分析，也可作为教学演示或数据可视化项目的基础数据集使用。

1. 拿到一份城市分级表，先别急着往库里灌

做区域分析、门店选址、用户画像分层的同行，多半都遇到过同一个尴尬：手里有一批带城市字段的业务数据，想按“一线/新一线/二线……五线”做聚合，结果发现公司内部根本没有一份口径统一、能直接 join 的城市等级表。有人临时手搓一张 Excel，有人从各种文章里复制粘贴，最后同一座城市在不同报表里等级对不上，汇报时被追问一句“这个分级依据是什么”就卡壳。

这份资源就是冲这个场景来的：一份 2020 年口径的中国城市等级划分表，同时给了.xlsx表格和.sql文件两种形态。Excel 适合业务同学直接筛选、透视、做可视化；SQL 文件适合工程师一次性导入数据库，和订单表、门店表、用户表做关联。它解决的不是“城市有多少个”这种常识问题，而是“把城市名映射成可计算的等级标签”这件脏活。适合数据分析、BI、后端开发，以及任何需要按城市线级做维度拆分的从业者。

2. 先看清表结构：城市分级表到底存了什么

2.1 一张分级表的最小字段集

城市等级表看着简单，真正能用的版本字段设计是有讲究的。常见做法是至少包含这么几列：城市名称、所属省份、城市等级（一线到五线）、以及一个可选的排名或序号。城市名称是关联键，省份用于纠偏和分组，等级是核心标签，排名则方便做“同等级内排序”这类需求。

拿到.xlsx后第一件事不是导入，而是打开看表头和前几十行，确认三件事：城市名用的是“市”后缀全称还是简称（比如“北京市”还是“北京”），等级列写的是“一线”还是“1”还是“一线城市”，有没有合并单元格或表头占了两行。这三件事直接决定后面导入脚本怎么写。我一般会先另存一份副本再动手，原始文件保持只读，避免改坏了没法回溯。

2.2 等级口径与城市数量的对应关系

2020 年这套分级口径里，一线城市是固定的四个，新一线是十几个，二线到五线逐级铺开，总数覆盖全国地级及以上城市。这里有个容易被忽略的点：不同机构发布的分级名单在“新一线”这一档上会有出入，所以这份表的价值不在于“绝对权威”，而在于“内部口径统一”。你把它作为公司内部分析的基准表，比每次临时找名单要靠谱得多。

实际使用时建议把等级做成有序枚举，而不是纯文本。比如一线=1、新一线=2、二线=3 一直到五线=6，这样排序、区间筛选、算平均线级都方便。文本标签保留给人看，数值编码留给机器算，两列并存是最省事的做法。

import pandas as pd # 读取时先不指定 dtype，观察原始数据长什么样 df = pd.read_excel("city_level_2020.xlsx", sheet_name=0, header=0) print(df.columns.tolist()) # 看表头 print(df.head(10)) # 看前 10 行 print(df["城市等级"].value_counts()) # 看等级分布是否合理

这段代码的作用是先做“体检”而不是直接清洗。header=0假定第一行是表头，如果实际表头占了两行，就要改成header=1或先skiprows。value_counts()能快速暴露问题：如果出现“一线城市”“一线”“1线”混用，说明这一列需要统一映射，后面导入数据库才不会出现同一等级被拆成多个值的情况。

2.3 为什么同时给 Excel 和 SQL 两种形态

这不是凑数。Excel 面向的是不会写 SQL 的业务方，他们要做的是筛选、透视、贴到 PPT 里；SQL 面向的是要把这张表变成数据库里一张维表（dimension table）的工程师。两种形态对应两条完全不同的使用路径，也对应两类不同的坑：Excel 的坑在编码和格式，SQL 的坑在字符集和主键。

从工程角度看，正确姿势是“以 SQL 文件为准入库，Excel 只作为人工核对和交付物”。因为 SQL 文件里的建表语句、字段类型、插入数据是一体的，导入后就是一张可直接 join 的表；而 Excel 每次读取都要重新推断类型，城市名里的空格、全半角括号都可能让关联失败。把 SQL 当源头，Excel 当快照，这条原则能省掉后面很多对不上的麻烦。

3. 把 SQL 文件导进库：建表、导入、校验三步走

3.1 建表语句里该盯的几个点

打开.sql文件，先找CREATE TABLE那段，重点看三样：字符集、城市名字段的长度、有没有主键或唯一索引。城市名是中文，字符集必须是utf8mb4，否则遇到生僻字或特殊符号会截断甚至报错。城市名字段长度给到VARCHAR(50)一般够用，但如果你后续要拼“省+市”做联合键，就得留余量。

主键这块，如果原文件没给，我一般会自己加一个自增 id 或者把城市名设成唯一索引。原因很实际：导入过程中如果重复执行，没有唯一约束就会插进去两份数据，后面 join 出来的结果直接翻倍，这种错误在聚合报表里极难发现。加唯一索引后，重复导入会报错而不是静默污染数据，这属于花一分钟换后面几小时排查时间的买卖。

-- 建表时显式指定字符集，避免中文乱码 CREATE TABLE dim_city_level ( id INT AUTO_INCREMENT PRIMARY KEY, city_name VARCHAR(50) NOT NULL COMMENT '城市名称', province VARCHAR(50) NOT NULL COMMENT '所属省份', city_level VARCHAR(20) NOT NULL COMMENT '等级文本，如 一线/新一线', level_code TINYINT NOT NULL COMMENT '等级数值编码 1-6', rank_no INT DEFAULT NULL COMMENT '同等级内排名', UNIQUE KEY uk_city (city_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

建表语句里level_code单独留一列是刻意为之，文本等级给人看，数值编码给ORDER BY和区间统计用。UNIQUE KEY uk_city是防重复导入的闸门。COLLATE选通用排序规则即可，除非你有跨库比对需求，否则不必纠结具体用哪个变体。

3.2 导入命令与常见报错

导入方式取决于你用的库。命令行下最常见的是mysql客户端直接 source，或者用图形工具执行脚本。如果 SQL 文件里已经带了INSERT语句，直接执行整个文件即可；如果只有建表没有数据，就得先把 Excel 转成 CSV 再LOAD DATA。

# 方式一：直接执行带数据的 sql 文件 mysql -u root -p your_database < city_level_2020.sql # 方式二：先建表，再把清洗后的 csv 灌进去 mysql -u root -p your_database -e " LOAD DATA LOCAL INFILE 'city_level_clean.csv' INTO TABLE dim_city_level FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (city_name, province, city_level, level_code, rank_no);"

第一种方式最省事，前提是文件里的库名、表名和你环境一致，不一致就先改文件头或手动USE目标库。第二种方式适合你已经在 Python 里清洗过一遍、想控制字段顺序的场景。IGNORE 1 ROWS跳过表头，ENCLOSED BY处理带逗号的字段。如果报The used command is not allowed，是客户端没开LOCAL_INFILE，需要在连接参数里显式允许，这是权限配置问题不是数据问题。

3.3 导入后必须做的三条校验

导完不校验等于没导。我固定跑三条：总数对不对、等级分布合不合理、有没有空值或重复。总数可以对照 Excel 行数；等级分布看每一档的城市数量是否落在常识范围内；空值检查重点看省份和等级列。

-- 1. 总行数 SELECT COUNT(*) FROM dim_city_level; -- 2. 各等级城市数量分布 SELECT city_level, COUNT(*) AS cnt FROM dim_city_level GROUP BY city_level ORDER BY MIN(level_code); -- 3. 空值与重复检查 SELECT COUNT(*) FROM dim_city_level WHERE province IS NULL OR province = ''; SELECT city_name, COUNT(*) FROM dim_city_level GROUP BY city_name HAVING COUNT(*) > 1;

第一条对总数，第二条看分布，第三条兜底。如果第二条里出现“一线”有十几个城市，基本可以断定等级列映射错了或者数据串行了。第三条查出重复，说明唯一索引没生效或者导入前表里已有旧数据，需要先清表再导。这三条跑完，这张维表才算可用。

4. 和业务表关联：城市名对齐才是真功夫

4.1 关联键不统一是最大的翻车点

维表建好了，真正让人头疼的是和业务表 join。业务表里的城市字段可能是“北京”“北京市”“北京市朝阳区”，甚至是拼音或编码。直接ON a.city = b.city_name大概率关联率只有六七成，剩下三成静默丢失，报表数字偏小还找不到原因。

常见做法是建一个映射层：先把业务表的城市字段做标准化（去空格、去“市”后缀、截取到市级），再和维表关联。如果业务数据里是区县级，就得先有一张区县到城市的对照，这一步不能省。我一般会在 ETL 里单独加一个city_std字段，专门存标准化后的城市名，原始字段保留不动，方便回溯。

-- 标准化后再关联：去掉“市”后缀并去空格 SELECT o.order_id, o.city_raw, c.city_level, c.level_code FROM fact_order o LEFT JOIN dim_city_level c ON TRIM(REPLACE(o.city_raw, '市', '')) = TRIM(REPLACE(c.city_name, '市', ''));

这里用LEFT JOIN而不是INNER JOIN，是为了让关联不上的记录也保留下来，方便统计关联率。REPLACE去“市”后缀是粗粒度处理，对“北京市”有效，但对“北京市朝阳区”无效，那种情况需要先做字符串截取或正则提取。关联率低于 90% 时不要急着往下做分析，先把没关联上的城市名SELECT DISTINCT出来看一眼，往往能发现几个脏数据或口径差异。

4.2 用等级编码做聚合而不是文本

关联上之后，聚合统计要用level_code而不是文本等级。原因很直接：文本排序是字典序，“一线”“三线”“二线”排出来是乱的；数值编码才能正确做ORDER BY、BETWEEN、算平均线级。比如算“平均城市线级”这种指标，只有数值列能算。

-- 按线级统计订单量和客单价，用数值编码排序 SELECT c.city_level, c.level_code, COUNT(*) AS order_cnt, AVG(o.amount) AS avg_amount FROM fact_order o JOIN dim_city_level c ON TRIM(REPLACE(o.city_raw, '市', '')) = TRIM(REPLACE(c.city_name, '市', '')) GROUP BY c.city_level, c.level_code ORDER BY c.level_code;

GROUP BY里同时带文本和编码，是为了输出可读；ORDER BY只认编码。如果只按文本分组排序，一线和新一线的顺序会错位，汇报时一眼就能被看出来。这类细节不影响数据正确性，但影响可信度。

4.3 把维表做成视图或宽表

如果多个报表都要用城市等级，别让每个查询都写一遍REPLACE关联逻辑。常见做法是建一个视图，把标准化和关联封装进去，业务方直接查视图。视图的好处是口径统一，改一处全生效；代价是每次查询都实时计算，数据量大时性能一般。折中方案是做成物化宽表，定时刷新。

CREATE VIEW v_order_with_city_level AS SELECT o.*, c.city_level, c.level_code FROM fact_order o LEFT JOIN dim_city_level c ON TRIM(REPLACE(o.city_raw, '市', '')) = TRIM(REPLACE(c.city_name, '市', ''));

视图里保留o.*是为了不破坏原有字段引用。如果业务表字段很多，建议显式列出需要的列而不是*，避免后续加字段导致视图定义漂移。视图建好后，关联率这个指标要定期监控，业务侧新增城市或录入不规范时，关联率会悄悄下降。

5. 避坑清单：这几处翻车我见过太多次

5.1 现象：导入后中文全是问号

原因基本锁定在字符集。要么建表时没指定utf8mb4，要么客户端连接字符集是latin1，要么 SQL 文件本身保存编码不是 UTF-8。三者任一不对都会出问号或乱码。解决顺序是：先确认文件编码（用编辑器看，别猜），再确认建表语句字符集，最后确认连接参数里character_set_client等几个变量。三处统一到utf8mb4后重新导入，不要在原表上修修补补。

5.2 现象：关联率突然从 95% 掉到 70%

原因通常是业务侧改了城市字段的录入格式，比如从“北京”改成“北京市”，或者新增了海外城市。维表里没有对应记录，LEFT JOIN后等级为空。解决办法不是改维表去迁就脏数据，而是在 ETL 里加标准化规则和未匹配告警。我一般会设一个阈值，关联率低于阈值就阻断下游任务，逼着上游把数据修好，而不是让错误数据一路流到报表。

5.3 现象：同一城市出现两条不同等级

原因多半是维表被重复导入且唯一索引没生效，或者手工往表里补过数据。排查方法是按城市名分组数大于 1 查出来，看两条记录的来源。解决是清表重导，并确认唯一索引确实建上了。如果业务上确实需要保留历史版本（比如不同年份口径不同），那就该加一个version字段，而不是让两条记录混在同一张表里。

5.4 现象：Excel 打开正常，程序读出来列错位

原因是 Excel 里有合并单元格或隐藏列，pandas读出来会把合并区域的值只填在第一行，其余为NaN。解决办法是读之前在 Excel 里取消合并并向下填充，或者在代码里用ffill()补全。更稳妥的做法是让提供方给一份“扁平化”的导出文件，一行一个城市，不带任何合并。这类格式问题在跨团队交付时特别常见，收到文件先看结构比先写代码重要。

5.5 现象：按线级统计的总数和全量对不上

先查是不是用了INNER JOIN把未匹配记录丢了，再查是不是GROUP BY时把NULL等级过滤掉了。LEFT JOIN产生的NULL等级在GROUP BY里会单独成一组，如果报表只展示已知等级，这部分就被隐藏了。正确做法是把NULL组显式命名为“未匹配”并展示出来，让数据缺口可见，而不是假装它不存在。

6. 进阶用法：把分级表变成可复用的分析底座

这张表真正的价值不在“查一下某城市是几线”，而在于它能成为一套可复用的分析底座。我习惯在它之上再叠两层：一层是“城市-省份-大区”的层级映射，方便按大区汇总；另一层是“线级分组”，把一线和新一线归为高线，二线三线归为中线，四线五线归为低线，做粗粒度对比时不用每次写一长串IN。

-- 在线级基础上派生大区分组，便于粗粒度对比 SELECT CASE WHEN level_code IN (1, 2) THEN '高线' WHEN level_code IN (3, 4) THEN '中线' ELSE '低线' END AS tier_group, COUNT(*) AS city_cnt, SUM(order_cnt) AS total_orders FROM v_order_with_city_level GROUP BY tier_group ORDER BY MIN(level_code);

这段派生的意义在于，很多业务问题不需要细到“新一线还是二线”，只需要“高线还是低线”。把分组逻辑固化下来，不同报表之间口径就一致了。参数上唯一要调的是分组边界，边界怎么划取决于你的业务分布，没有标准答案，但一旦定了就写进文档，别让每个人各划各的。

验证这套底座是否可靠，我固定做两件事：一是拿几个已知城市手工核对等级，二是把关联率和未匹配城市清单定期导出给业务方确认。前者防低级错误，后者防口径漂移。下面这张表是我常用的自检清单，照着过一遍基本不会出大问题。

检查项	合格标准	不合格时的动作
总行数	与源文件一致	查是否重复导入或漏导
等级分布	各档数量落在常识范围	查等级列映射
关联率	高于设定阈值	导出未匹配城市名排查
空值	省份、等级列无空	回源补全或标记
重复城市	唯一索引生效，无重复	清表重导