简介这份SQL文件面向地理信息系统开发者、C#应用工程师及需要位置服务的数据分析人员提供全球主要城市的经纬度数据解决地图服务、位置追踪与地理统计中缺乏统一城市坐标基准的问题。压缩包共1个文件为单个sql脚本体积约146KB内含建表DDL与数据插入DML语句可直接导入数据库使用。数据精确到城市级别包含中英文对照的城市名称并保留国家、地区、州/省等层级关系便于区域分析与多语言应用开发。已有151人学习下载适合需要快速搭建地理数据底层的开发者参考。读者可借助该脚本在C#项目中实现两点距离计算、导航定位与地理统计分析也能将城市层级结构集成到已有系统扩展国际化位置服务能力。1. 全球主要城市经纬度数据一份 SQL 文件能省掉多少脏活做地理相关功能时最容易被低估的不是算法而是基础数据。你写一个「附近门店」查询逻辑三行就够但要让结果可信背后得有全球主要城市的经纬度、中英文名称、以及国家-省州-城市这种层级关系。很多团队一开始用在线接口顶着量一上来就发现限流、字段缺失、中英文对不上最后还是要落库。这份「全球主要城市经纬度数据中英文层级关系精确到城市SQL 文件」解决的正是这件事它把城市点位和行政层级一次性固化成本地可查的表导入即用不依赖外部服务。适合做 LBS、物流分单、跨境业务地区选择器、数据看板地图下钻的开发者。下面我按「表怎么设计、SQL 怎么导、查询怎么写、坑在哪」讲一遍都是能直接抄的。2. 先想清楚表结构城市、层级、中英文怎么落到字段拿到一份 SQL 文件第一件事不是急着导入而是看它的表结构能不能撑住你的查询场景。城市数据看着简单真用起来会冒出三类需求按坐标找最近城市、按层级做级联选择、按中英文做多语言展示。这三类需求对字段的要求不一样设计时得提前留好。2.1 城市主表要有的字段和类型选择城市主表的核心是「一个城市一行」主键用自增 ID 还是业务编码取决于你要不要跨库同步。我一般用自增 BIGINT 做主键再留一个业务编码字段做外部对齐。经纬度必须用 DECIMAL 而不是 FLOAT原因后面避坑章节会细说。下面是我常用的建表语句字段名做了通用化处理你可以按自己项目习惯改。CREATE TABLE city ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, city_code VARCHAR(16) NOT NULL COMMENT 业务编码用于外部对齐, name_zh VARCHAR(64) NOT NULL COMMENT 城市中文名, name_en VARCHAR(96) NOT NULL COMMENT 城市英文名, country_code CHAR(2) NOT NULL COMMENT 国家二字码如 CN、US, admin1_code VARCHAR(16) DEFAULT NULL COMMENT 一级行政区编码省/州, latitude DECIMAL(9,6) NOT NULL COMMENT 纬度保留6位小数, longitude DECIMAL(9,6) NOT NULL COMMENT 经度保留6位小数, timezone VARCHAR(48) DEFAULT NULL COMMENT IANA 时区名, population INT UNSIGNED DEFAULT NULL COMMENT 人口用于排序取主要城市, PRIMARY KEY (id), UNIQUE KEY uk_city_code (city_code), KEY idx_country_admin (country_code, admin1_code), KEY idx_name_zh (name_zh), KEY idx_name_en (name_en) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT城市主表;逻辑说明city_code加唯一索引保证重复导入时可以用INSERT ... ON DUPLICATE KEY UPDATE做幂等。country_code和admin1_code建联合索引是因为级联查询几乎都是「先选国家再选省州」。name_zh和name_en单独建索引方便做前缀搜索。参数上DECIMAL(9,6)表示总共 9 位、小数 6 位纬度范围 -90 到 90、经度 -180 到 180 都放得下6 位小数精度约 0.11 米对城市级定位绰绰有余。2.2 层级关系用邻接表还是闭包表层级关系是这份数据里最容易做错的部分。国家、省州、城市三层用邻接表每行存 parent_id最省空间但查「某国家下所有城市」要递归。用闭包表额外存祖先-后代关系查询快但数据量翻几倍。城市级数据总量通常在几万行量级我倾向邻接表加一张冗余的路径字段兼顾简单和查询效率。CREATE TABLE region ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, region_code VARCHAR(16) NOT NULL COMMENT 行政区编码, parent_code VARCHAR(16) DEFAULT NULL COMMENT 父级编码顶级为 NULL, level TINYINT NOT NULL COMMENT 层级1国家 2省州 3城市, name_zh VARCHAR(64) NOT NULL, name_en VARCHAR(96) NOT NULL, full_path VARCHAR(255) DEFAULT NULL COMMENT 冗余全路径如 CN/省/城市, PRIMARY KEY (id), UNIQUE KEY uk_region_code (region_code), KEY idx_parent (parent_code), KEY idx_level (level) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT行政区层级表;逻辑说明level字段让查询可以直接过滤层级不用递归判断。full_path是冗余字段写入时拼好查询「某省下所有城市」时用LIKE CN/省/%就能命中避免递归。参数上level用 TINYINT 足够full_path长度按最深路径预留 255。注意parent_code允许 NULL顶级国家没有父级。2.3 中英文双字段还是翻译表有人喜欢把多语言拆成翻译表一行一个语言。城市数据我建议直接双字段因为语言种类固定就中英两种拆表反而增加 JOIN 成本。如果以后要加第三种语言再迁移也不迟。双字段的代价是表宽一点但查询简单展示层直接按 locale 选字段即可。3. 把 SQL 文件导进库命令行、客户端和批量优化SQL 文件到手导入方式直接影响你要等五分钟还是五十分钟。几万行的 INSERT 语句如果每条单独提交光事务开销就够呛。下面按「先看文件、再选方式、最后校验」的顺序讲。3.1 导入前先看文件头和编码别急着执行先看文件开头几行和编码。很多 SQL 文件是 UTF-8 无 BOM但 Windows 环境导出的可能带 BOM导入时第一行会报语法错误。# 看文件头 20 行确认是 INSERT 还是 LOAD DATA 形式 head -n 20 city_data.sql # 检查编码确认是 UTF-8 file -i city_data.sql # 统计 INSERT 语句条数估算导入时间 grep -c INSERT INTO city_data.sql逻辑说明head看结构如果是LOAD DATA LOCAL INFILE形式导入会快很多但要确认文件路径。file -i看编码输出charsetutf-8才放心。grep -c估算规模几万条 INSERT 通常几分钟内能完成。如果文件带 BOM用sed -i 1s/^\xEF\xBB\xBF// city_data.sql去掉。3.2 命令行导入与关键参数命令行导入最稳适合服务器环境。核心是关掉自动提交、调大缓冲区。mysql -u root -p \ --default-character-setutf8mb4 \ --max_allowed_packet256M \ your_database city_data.sql逻辑说明--default-character-setutf8mb4保证中文不乱码这是血泪经验漏了它中文城市名会变问号。--max_allowed_packet256M防止大 INSERT 语句被截断报Packet too large。如果文件里有CREATE DATABASE语句注意别覆盖现有库。导入前建议先SET autocommit0;包在事务里但很多 SQL 文件已经自带事务控制看文件头决定。3.3 导入后做三件事校验导入完成不代表数据可用必须校验行数、坐标范围、层级完整性。-- 1. 行数是否符合预期 SELECT COUNT(*) AS total FROM city; -- 2. 坐标是否越界 SELECT COUNT(*) AS bad_coord FROM city WHERE latitude NOT BETWEEN -90 AND 90 OR longitude NOT BETWEEN -180 AND 180; -- 3. 层级是否有孤儿节点 SELECT COUNT(*) AS orphan FROM region r WHERE r.parent_code IS NOT NULL AND NOT EXISTS (SELECT 1 FROM region p WHERE p.region_code r.parent_code);逻辑说明第一条确认没丢数据。第二条抓坐标越界常见于经纬度写反纬度写成经度值。第三条抓孤儿节点层级数据里父级缺失会导致级联查询断链。三条都返回预期值才算导入成功。4. 查询怎么写最近城市、级联选择、中英文切换数据进库后真正的活是查询。城市数据的查询模式就那么几种写对了性能差一个数量级。4.1 按坐标找最近城市别直接算距离「给一个经纬度找最近的城市」是最常见的需求。新手容易写成全表扫描算距离几万行还能忍上百万行就崩了。正确做法是先用边界框缩小范围再算精确距离。-- 先框出候选再算距离避免全表扫描 SELECT id, name_zh, name_en, ST_Distance_Sphere( POINT(longitude, latitude), POINT(116.4074, 39.9042) ) AS distance_m FROM city WHERE latitude BETWEEN 39.9042 - 0.5 AND 39.9042 0.5 AND longitude BETWEEN 116.4074 - 0.5 AND 116.4074 0.5 ORDER BY distance_m LIMIT 1;逻辑说明ST_Distance_Sphere是 MySQL 5.7 的空间函数直接算球面距离单位米。先用BETWEEN框一个约 0.5 度约 55 公里的边界框把候选集压到几十行再排序。参数上0.5 度是经验值城市密集区可以调小到 0.2稀疏区调大到 1。注意POINT的参数顺序是「经度在前、纬度在后」写反了结果会跑到地球另一边这是最常见的翻车点。4.2 级联选择用 full_path 一次查完前端做「国家-省州-城市」三级联动如果每级都发一次请求体验差。用full_path可以一次查出某国家下所有城市前端自己组装树。-- 查某国家下所有城市按省州分组 SELECT r.parent_code AS admin1_code, r.name_zh AS admin1_name, c.name_zh AS city_name, c.latitude, c.longitude FROM city c JOIN region r ON r.region_code c.admin1_code WHERE c.country_code CN ORDER BY r.name_zh, c.name_zh;逻辑说明city表冗余了admin1_code直接 JOINregion拿到省州名避免递归。如果数据量大可以在city表再加一个admin1_name冗余字段省掉 JOIN。参数上country_code用二字码注意大小写统一导入时最好UPPER()处理一遍。4.3 中英文切换与模糊搜索多语言展示靠字段选择模糊搜索靠索引。中文搜索用LIKE 前缀%能命中索引LIKE %关键词%不行。-- 英文前缀搜索命中 idx_name_en SELECT id, name_en, name_zh FROM city WHERE name_en LIKE Shang% LIMIT 20; -- 中文搜索注意 utf8mb4 下前缀匹配 SELECT id, name_zh, name_en FROM city WHERE name_zh LIKE 上海% LIMIT 20;逻辑说明前缀匹配能用上 B-Tree 索引中间匹配不行。如果必须做中间匹配考虑加全文索引或用外部搜索引擎。参数上LIMIT 20是防止返回过多前端做自动补全通常 10 到 20 条够用。5. 避坑与排查坐标、编码、层级、性能的五个真实翻车这一章是我踩过的坑每条按「现象、原因、解决」写你对照排查能省不少时间。5.1 经纬度写反导致城市跑到南极现象查询「附近城市」返回一个距离几千公里的结果或者地图上点位全在南极附近。原因POINT(经度, 纬度)参数顺序写反或者导入时源数据列顺序就是纬度在前。解决导入后跑一次范围校验纬度绝对值大于 90 的必然是写反了查询里统一用POINT(longitude, latitude)并在代码注释里标死顺序。5.2 中文乱码变成问号现象name_zh字段显示???或乱码方块。原因导入时连接字符集不是 utf8mb4或者表本身建成了 latin1。解决建表时DEFAULT CHARSETutf8mb4导入命令加--default-character-setutf8mb4连接串也确认是 utf8mb4。三处都对了才不会乱码。5.3 层级孤儿导致级联断链现象前端选完国家省州列表为空。原因region表里某些城市的parent_code指向了一个不存在的省州编码或者编码大小写不一致。解决跑 3.3 节的孤儿查询把孤儿节点找出来对照源数据修正编码。导入前统一UPPER()处理编码能避免大小写问题。5.4 全表扫描算距离拖垮数据库现象附近城市查询响应从几十毫秒涨到几秒CPU 打满。原因没加边界框直接对全表算ST_Distance_Sphere。解决先用BETWEEN框边界再算距离数据量特别大时考虑用空间索引SPATIAL INDEX配合ST_Within。5.5 重复导入产生重复城市现象同一个城市出现两行级联列表里重复。原因导入脚本没做幂等第二次执行又插了一遍。解决city_code加唯一索引导入语句改成INSERT ... ON DUPLICATE KEY UPDATE name_zhVALUES(name_zh), ...重复执行只更新不新增。6. 进阶用法用这份数据做时区推断和距离分单基础查询跑通后这份数据还能撑起两个进阶场景。第一个是时区推断城市表里有timezone字段用户选了城市就能直接拿到 IANA 时区名不用再调外部接口。第二个是距离分单物流场景里订单地址解析出经纬度后用 4.1 的最近城市查询找到归属城市再按城市维度做运力分配。这两个场景我都用过关键是别在查询里做复杂计算把能冗余的字段提前算好。验证数据是否可用的一个具体技巧随机抽 10 个城市用在线地图反查坐标误差在 1 公里内就算合格。我一般会写个小脚本批量抽检比人工核对快得多。# 随机抽检城市坐标输出待人工核对的清单 import random import pymysql conn pymysql.connect(hostlocalhost, userroot, passwordyour_password, databaseyour_database, charsetutf8mb4) cur conn.cursor() cur.execute(SELECT id, name_zh, latitude, longitude FROM city ORDER BY RAND() LIMIT 10) for row in cur.fetchall(): print(f{row[1]}: {row[2]}, {row[3]}) conn.close()逻辑说明ORDER BY RAND()在几万行表上可接受百万行以上要换采样策略。输出清单后人工在地图上核对重点看边界城市和同名城市。参数上LIMIT 10是抽检量正式验收可以抽 50 到 100 个。我自己维护这类基础数据有个习惯每次导入后必跑一遍坐标范围、编码一致性、层级孤儿三项校验脚本化放在 CI 里。吃过一次坐标写反的亏之后再也不敢跳过校验。希望帮到你。本文还有配套的精品资源点击获取