簡(jiǎn)介這份SQL文件面向地理信息系統(tǒng)開(kāi)發(fā)者、C#應(yīng)用工程師及需要位置服務(wù)的數(shù)據(jù)分析人員提供全球主要城市的經(jīng)緯度數(shù)據(jù)解決地圖服務(wù)、位置追蹤與地理統(tǒng)計(jì)中缺乏統(tǒng)一城市坐標(biāo)基礎(chǔ)表的問(wèn)題。壓縮包共1個(gè)文件為單個(gè)sql腳本體積約146KB內(nèi)含建表DDL與數(shù)據(jù)插入DML語(yǔ)句可直接導(dǎo)入數(shù)據(jù)庫(kù)使用。數(shù)據(jù)精確到城市級(jí)別包含中英文對(duì)照的城市名稱(chēng)并保留國(guó)家、地區(qū)、州/省等層級(jí)關(guān)系便于區(qū)域分析與多語(yǔ)言應(yīng)用開(kāi)發(fā)。已有151人學(xué)習(xí)下載。讀者可借此快速構(gòu)建城市坐標(biāo)表用于兩點(diǎn)距離計(jì)算、導(dǎo)航定位、地理統(tǒng)計(jì)分析并結(jié)合C#等語(yǔ)言開(kāi)發(fā)具備位置服務(wù)能力的應(yīng)用或集成到現(xiàn)有系統(tǒng)中擴(kuò)展地理信息功能省去自行采集與整理全球城市經(jīng)緯度的工作量。1. 全球主要城市經(jīng)緯度數(shù)據(jù)一份 SQL 文件怎么把中英文和層級(jí)關(guān)系一次說(shuō)清做地理相關(guān)的業(yè)務(wù)時(shí)最容易被低估的往往不是地圖渲染而是底層那份城市清單。你拿到一份「全球主要城市_經(jīng)緯度數(shù)據(jù)_中英文_層級(jí)關(guān)系_精確到城市_SQL文件.zip」第一反應(yīng)可能是不就是一張表字段有城市名、經(jīng)緯度、國(guó)家、省份嗎真導(dǎo)入進(jìn)去用起來(lái)問(wèn)題才一個(gè)個(gè)冒出來(lái)——同一個(gè)城市中文名有「舊金山」和「圣弗朗西斯科」兩種寫(xiě)法英文名帶不帶州后綴層級(jí)關(guān)系里「市」和「區(qū)」混在一列經(jīng)緯度精度在小數(shù)點(diǎn)后四位還是六位之間反復(fù)橫跳。這份數(shù)據(jù)的價(jià)值不在于它有多少行而在于它把中英文對(duì)照、行政層級(jí)、坐標(biāo)精度這三件事同時(shí)壓進(jìn)了一個(gè)可被 SQL 直接消費(fèi)的結(jié)構(gòu)里。適合誰(shuí)用做跨境物流地址校驗(yàn)的、做多語(yǔ)言站點(diǎn)地區(qū)選擇器的、做數(shù)據(jù)看板里城市維度下鉆的以及任何需要「按城市聚合但不想自己維護(hù)一份臟清單」的團(tuán)隊(duì)。下面按導(dǎo)入、理解結(jié)構(gòu)、查詢、避坑、進(jìn)階的順序把這份 SQL 文件從解壓到跑通講透。2. 先看懂 SQL 文件里的表結(jié)構(gòu)和層級(jí)設(shè)計(jì)2.1 為什么層級(jí)關(guān)系不能只靠一張平表很多人拿到城市數(shù)據(jù)的第一反應(yīng)是建一張寬表城市名、省份名、國(guó)家名、經(jīng)緯度全塞一行。這種設(shè)計(jì)在查詢「某個(gè)國(guó)家下所有城市」時(shí)很快但一旦要做「從洲到國(guó)家到省到市」的四級(jí)下鉆或者處理「一個(gè)城市屬于多個(gè)上級(jí)行政區(qū)」的邊界情況平表就會(huì)暴露冗余和更新異常。這份 SQL 文件通常采用自引用或分層編碼的方式來(lái)表達(dá)層級(jí)每一行有一個(gè)唯一 ID同時(shí)有一個(gè) parent_id 指向上一級(jí)或者用類(lèi)似level字段標(biāo)記當(dāng)前是洲、國(guó)家、省還是市。精確到城市意味著最細(xì)粒度那一層的 level 值固定往上聚合時(shí)只需要按 parent_id 遞歸或按層級(jí)編碼前綴匹配。常見(jiàn)做法是兩種鄰接表parent_id和路徑枚舉path 字段存/洲/國(guó)家/省/市。鄰接表寫(xiě)入簡(jiǎn)單、更新方便但遞歸查詢依賴(lài)數(shù)據(jù)庫(kù)的 CTE 支持路徑枚舉查詢快但移動(dòng)節(jié)點(diǎn)時(shí)要批量改路徑。這份數(shù)據(jù)既然強(qiáng)調(diào)「層級(jí)關(guān)系」大概率在表里同時(shí)保留了 parent_id 和一個(gè)可讀的層級(jí)路徑或?qū)蛹?jí)碼方便你按前綴 LIKE 查詢。導(dǎo)入前先確認(rèn)你的數(shù)據(jù)庫(kù)版本支持遞歸 CTEMySQL 8.0 以上、PostgreSQL 全系、SQL Server 2005 以上都沒(méi)問(wèn)題老版本 MySQL 5.7 就得靠自連接硬撐。2.2 中英文字段的命名習(xí)慣與編碼陷阱中英文對(duì)照不是簡(jiǎn)單加兩列name_cn和name_en就完事。實(shí)際數(shù)據(jù)里常見(jiàn)的坑是英文列里混著本地語(yǔ)言轉(zhuǎn)寫(xiě)中文列里夾著繁體或異體字還有的用name_zh、name_en、local_name三列并存。導(dǎo)入前先用文本編輯器或head命令看前幾十行 INSERT 語(yǔ)句確認(rèn)列名和字符集。如果 SQL 文件里建表語(yǔ)句寫(xiě)了CHARSETutf8mb4那中文和 emoji 都能存如果只寫(xiě)了utf8MySQL 里其實(shí)是 utf8mb3遇到生僻字或某些符號(hào)會(huì)截?cái)嗷驁?bào)錯(cuò)。另一個(gè)高頻問(wèn)題是排序規(guī)則。utf8mb4_general_ci和utf8mb4_unicode_ci對(duì)中英文混合排序結(jié)果不同做「按城市名排序」的列表時(shí)前者可能把中文排在英文后面且順序不符合拼音習(xí)慣。如果業(yè)務(wù)需要按拼音或筆畫(huà)排序光靠數(shù)據(jù)庫(kù)默認(rèn)排序不夠得額外加一列name_pinyin或sort_key。這份數(shù)據(jù)如果沒(méi)帶拼音列你可以在導(dǎo)入后用腳本補(bǔ)但別指望 SQL 文件本身幫你解決。2.3 導(dǎo)入前的三步檢查編碼、分隔符、批量大小拿到.sql文件先別急著source。第一步用file -i看文件編碼常見(jiàn)是 UTF-8 帶 BOM 或不帶 BOM帶 BOM 時(shí)第一行建表語(yǔ)句可能報(bào)語(yǔ)法錯(cuò)誤。第二步看 INSERT 語(yǔ)句是單行一條還是批量多值批量多值時(shí)注意max_allowed_packet是否夠大默認(rèn) 4MB 或 16MB全球城市數(shù)據(jù)加上中英文和層級(jí)字段批量 INSERT 很容易超。第三步確認(rèn) SQL 文件里有沒(méi)有CREATE DATABASE和USE語(yǔ)句如果沒(méi)有你得先建庫(kù)再導(dǎo)入。# 檢查文件編碼和大小 file -i global_cities.sql ls -lh global_cities.sql # 查看前 50 行確認(rèn)建表語(yǔ)句和字符集 head -n 50 global_cities.sql # 查看是否有 CREATE DATABASE / USE 語(yǔ)句 grep -n -E CREATE DATABASE|USE global_cities.sql | head上面命令的邏輯說(shuō)明file -i輸出 MIME 類(lèi)型和字符集如果顯示charsetbinary或unknown-8bit說(shuō)明編碼不是標(biāo)準(zhǔn) UTF-8需要轉(zhuǎn)碼后再導(dǎo)入。head -n 50讓你在不打開(kāi)大文件的情況下看清表結(jié)構(gòu)定義重點(diǎn)看CHARSET和COLLATE。grep用來(lái)確認(rèn)庫(kù)名避免導(dǎo)入到錯(cuò)誤的數(shù)據(jù)庫(kù)。參數(shù)上如果文件超過(guò) 100MB建議用mysql命令行客戶端而不是圖形工具圖形工具在批量 INSERT 時(shí)容易超時(shí)或內(nèi)存溢出。3. 把 SQL 文件跑起來(lái)建庫(kù)、導(dǎo)入、驗(yàn)證的最小閉環(huán)3.1 建庫(kù)與調(diào)整導(dǎo)入?yún)?shù)導(dǎo)入前先根據(jù)數(shù)據(jù)量調(diào)整幾個(gè)關(guān)鍵參數(shù)。max_allowed_packet決定單條 SQL 語(yǔ)句的最大長(zhǎng)度批量 INSERT 多行時(shí)容易撞上限innodb_buffer_pool_size影響導(dǎo)入速度但生產(chǎn)環(huán)境別為了導(dǎo)入臨時(shí)調(diào)太大autocommit在導(dǎo)入時(shí)建議關(guān)閉用事務(wù)包住批量插入能快很多。下面是一套可抄的導(dǎo)入流程以 MySQL 為例。-- 創(chuàng)建數(shù)據(jù)庫(kù)字符集必須 utf8mb4 CREATE DATABASE IF NOT EXISTS geo_city DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci; USE geo_city; -- 導(dǎo)入前臨時(shí)調(diào)整會(huì)話參數(shù)只影響當(dāng)前連接 SET SESSION sql_mode NO_AUTO_VALUE_ON_ZERO; SET SESSION autocommit 0; SET SESSION unique_checks 0; SET SESSION foreign_key_checks 0;邏輯說(shuō)明utf8mb4是必須的否則中文城市名里的生僻字和某些符號(hào)會(huì)丟。sql_mode設(shè)為NO_AUTO_VALUE_ON_ZERO是為了防止自增列遇到 0 值時(shí)報(bào)錯(cuò)有些數(shù)據(jù)文件會(huì)用 0 表示未知父級(jí)。關(guān)閉autocommit、unique_checks、foreign_key_checks是導(dǎo)入加速的常規(guī)操作導(dǎo)入完成后記得改回來(lái)。參數(shù)上unique_checks0只在確定數(shù)據(jù)無(wú)重復(fù)時(shí)用如果數(shù)據(jù)本身有重復(fù)主鍵導(dǎo)入后會(huì)留下隱患建議導(dǎo)入后跑一次去重檢查。3.2 用命令行導(dǎo)入并觀察錯(cuò)誤# 在 shell 中執(zhí)行導(dǎo)入把錯(cuò)誤輸出到單獨(dú)文件 mysql -u root -p --default-character-setutf8mb4 geo_city global_cities.sql 2 import_error.log # 查看錯(cuò)誤日志里有沒(méi)有報(bào)錯(cuò) grep -i -E error|warning import_error.log | head -n 20邏輯說(shuō)明--default-character-setutf8mb4確?？蛻舳撕头?wù)端握手時(shí)用對(duì)字符集少了這個(gè)參數(shù)中文可能變問(wèn)號(hào)。2 import_error.log把標(biāo)準(zhǔn)錯(cuò)誤重定向到文件避免刷屏導(dǎo)入后集中看。如果錯(cuò)誤日志里出現(xiàn)Packet too large就去調(diào)大max_allowed_packet出現(xiàn)Incorrect string value說(shuō)明文件編碼和數(shù)據(jù)庫(kù)字符集不匹配需要轉(zhuǎn)碼或改列字符集。3.3 導(dǎo)入后的三張驗(yàn)證查詢導(dǎo)入完成不等于數(shù)據(jù)可用。至少跑三類(lèi)驗(yàn)證行數(shù)是否符合預(yù)期、層級(jí)關(guān)系是否閉環(huán)、經(jīng)緯度是否在合法范圍。-- 驗(yàn)證 1總行數(shù)和各層級(jí)行數(shù) SELECT level, COUNT(*) AS cnt FROM cities GROUP BY level ORDER BY level; -- 驗(yàn)證 2找出沒(méi)有父級(jí)的非頂級(jí)節(jié)點(diǎn)層級(jí)斷裂 SELECT id, name_cn, level, parent_id FROM cities WHERE parent_id IS NULL AND level continent LIMIT 20; -- 驗(yàn)證 3經(jīng)緯度合法性檢查 SELECT id, name_cn, latitude, longitude FROM cities WHERE latitude NOT BETWEEN -90 AND 90 OR longitude NOT BETWEEN -180 AND 180 LIMIT 20;邏輯說(shuō)明第一條按層級(jí)分組計(jì)數(shù)能快速看出數(shù)據(jù)覆蓋是否完整比如城市層行數(shù)遠(yuǎn)少于預(yù)期可能是導(dǎo)入中斷。第二條找層級(jí)斷裂parent_id為空但 level 不是頂級(jí)說(shuō)明數(shù)據(jù)有缺失或字段映射錯(cuò)了。第三條檢查坐標(biāo)范圍緯度超出 ±90 或經(jīng)度超出 ±180 的記錄要么是數(shù)據(jù)錯(cuò)誤要么是字段順序顛倒把經(jīng)度當(dāng)緯度存了。參數(shù)上LIMIT 20只是抽樣看真要全量檢查就去掉 LIMIT 看總數(shù)。4. 按城市查經(jīng)緯度和中英文名的常用 SQL 寫(xiě)法4.1 精確匹配與模糊匹配的取舍業(yè)務(wù)里查城市經(jīng)緯度最常用的是按名稱(chēng)精確匹配。但中英文混合場(chǎng)景下「精確」本身就有歧義用戶輸入「北京」要能命中輸入「Beijing」也要能命中輸入「北京市」最好也能命中。如果表里只有name_cn和name_en兩列精確匹配就得寫(xiě)兩個(gè)條件用 OR 連接或者用 UNION。更穩(wěn)的做法是建一個(gè)統(tǒng)一的搜索列把中英文和別名拼在一起加全文索引或前綴索引。-- 精確匹配中英文名含常見(jiàn)后綴變體 SELECT id, name_cn, name_en, latitude, longitude, level FROM cities WHERE level city AND (name_cn 北京 OR name_en Beijing OR name_cn 北京市) LIMIT 10; -- 模糊匹配適合搜索框聯(lián)想 SELECT id, name_cn, name_en, latitude, longitude FROM cities WHERE level city AND (name_cn LIKE 北% OR name_en LIKE Bei%) ORDER BY name_en LIMIT 20;邏輯說(shuō)明第一條用 OR 枚舉常見(jiàn)變體適合已知輸入規(guī)范的場(chǎng)景。第二條用 LIKE 前綴匹配適合搜索框?qū)崟r(shí)聯(lián)想但注意LIKE %北京%這種前后都帶通配符的寫(xiě)法無(wú)法走索引數(shù)據(jù)量大時(shí)會(huì)全表掃描。參數(shù)上level city是為了排除省和國(guó)家層同名干擾比如「吉林」既是省也是市不加層級(jí)過(guò)濾會(huì)返回多條。4.2 按層級(jí)下鉆從國(guó)家拿到所有城市層級(jí)關(guān)系的核心價(jià)值是下鉆。假設(shè)你要查「某個(gè)國(guó)家下所有城市及其經(jīng)緯度」用鄰接表遞歸 CTE 是最標(biāo)準(zhǔn)的寫(xiě)法。下面以 PostgreSQL 和 MySQL 8.0 通用的遞歸 CTE 為例。-- 從國(guó)家節(jié)點(diǎn)遞歸拿到其下所有城市 WITH RECURSIVE city_tree AS ( -- 錨點(diǎn)先找到目標(biāo)國(guó)家節(jié)點(diǎn) SELECT id, name_cn, name_en, level, parent_id, latitude, longitude FROM cities WHERE name_en France AND level country UNION ALL -- 遞歸逐層向下找子節(jié)點(diǎn) SELECT c.id, c.name_cn, c.name_en, c.level, c.parent_id, c.latitude, c.longitude FROM cities c INNER JOIN city_tree ct ON c.parent_id ct.id ) SELECT id, name_cn, name_en, latitude, longitude FROM city_tree WHERE level city ORDER BY name_en;邏輯說(shuō)明錨點(diǎn)部分定位到國(guó)家節(jié)點(diǎn)遞歸部分通過(guò)c.parent_id ct.id逐層展開(kāi)直到?jīng)]有子節(jié)點(diǎn)為止。最后過(guò)濾level city只取城市層。參數(shù)上如果數(shù)據(jù)里層級(jí)深度超過(guò)默認(rèn)遞歸限制MySQL 默認(rèn)cte_max_recursion_depth是 1000一般夠用如果數(shù)據(jù)用路徑枚舉而不是 parent_id就把遞歸部分換成WHERE path LIKE /France/%這種前綴匹配速度更快但依賴(lài)路徑字段的格式統(tǒng)一。4.3 中英文對(duì)照輸出的排序與去重做多語(yǔ)言站點(diǎn)時(shí)經(jīng)常需要「按當(dāng)前語(yǔ)言排序但輸出中英文兩列」。如果直接ORDER BY name_cn中文排序結(jié)果取決于數(shù)據(jù)庫(kù)的 collation可能不是拼音順序。一個(gè)折中方案是加一列name_en作為次級(jí)排序因?yàn)橛⑽呐判蚍€(wěn)定且可預(yù)期。-- 按英文名排序輸出中英文對(duì)照去重同名城市 SELECT DISTINCT name_cn, name_en, latitude, longitude FROM cities WHERE level city AND name_en IS NOT NULL AND name_cn IS NOT NULL ORDER BY name_en ASC LIMIT 100;邏輯說(shuō)明DISTINCT用來(lái)去掉中英文名和坐標(biāo)完全相同的重復(fù)行有些數(shù)據(jù)文件在合并多個(gè)來(lái)源時(shí)會(huì)留下重復(fù)。name_en IS NOT NULL和name_cn IS NOT NULL過(guò)濾掉缺失翻譯的記錄保證輸出對(duì)照完整。參數(shù)上LIMIT 100只是示例實(shí)際分頁(yè)要用LIMIT offset, size或游標(biāo)分頁(yè)避免大偏移量性能問(wèn)題。5. 避坑導(dǎo)入和查詢這份城市數(shù)據(jù)時(shí)最容易翻車(chē)的 5 個(gè)點(diǎn)5.1 現(xiàn)象中文城市名導(dǎo)入后變成問(wèn)號(hào)或亂碼原因SQL 文件本身是 GBK 或 Latin1 編碼但導(dǎo)入時(shí)客戶端用了 utf8mb4或者建表時(shí)列字符集沒(méi)跟上。解決先用file -i確認(rèn)文件編碼如果是 GBK用iconv -f GBK -t UTF-8轉(zhuǎn)碼后再導(dǎo)入建表語(yǔ)句里所有文本列顯式寫(xiě)CHARACTER SET utf8mb4別依賴(lài)庫(kù)默認(rèn)。5.2 現(xiàn)象遞歸查詢報(bào)錯(cuò)「Recursive query aborted after 1001 iterations」原因數(shù)據(jù)里存在循環(huán)引用A 的 parent 是 BB 的 parent 又是 A遞歸 CTE 陷入死循環(huán)。解決導(dǎo)入后跑一次循環(huán)檢測(cè)找出 parent_id 鏈上回到自身的記錄。MySQL 里可以臨時(shí)調(diào)大cte_max_recursion_depth看看到底循環(huán)在哪但根治方法是修數(shù)據(jù)把循環(huán)的 parent_id 置空或指向正確上級(jí)。-- 檢測(cè)簡(jiǎn)單循環(huán)自己指向自己 SELECT id, name_cn, parent_id FROM cities WHERE id parent_id; -- 檢測(cè)兩級(jí)循環(huán)A-B-A SELECT a.id, a.name_cn, b.id AS parent_id, b.name_cn AS parent_name FROM cities a JOIN cities b ON a.parent_id b.id WHERE b.parent_id a.id;5.3 現(xiàn)象經(jīng)緯度查出來(lái)是 0,0 或者明顯偏移原因數(shù)據(jù)里用 0,0 表示未知坐標(biāo)或者經(jīng)緯度列順序顛倒緯度存了經(jīng)度值。解決導(dǎo)入后跑范圍檢查把 0,0 的記錄單獨(dú)標(biāo)記如果發(fā)現(xiàn)大量緯度絕對(duì)值大于 90基本可以確定列順序反了用 UPDATE 交換兩列。5.4 現(xiàn)象按城市名查詢返回多條分不清哪個(gè)是目標(biāo)原因不同國(guó)家有同名城市比如「圣何塞」在美國(guó)和哥斯達(dá)黎加都有「劍橋」在英國(guó)和美國(guó)都有。解決查詢時(shí)必須帶上國(guó)家或省份過(guò)濾或者用層級(jí)路徑字段做前綴限定。如果業(yè)務(wù)允許用戶只輸城市名返回結(jié)果里要帶上國(guó)家名和經(jīng)緯度讓用戶自己選。5.5 現(xiàn)象導(dǎo)入速度極慢幾萬(wàn)行跑了半小時(shí)原因autocommit 開(kāi)著每條 INSERT 都刷盤(pán)或者批量 INSERT 的包太大反復(fù)重試。解決導(dǎo)入前關(guān) autocommit用START TRANSACTION包住把大文件拆成多個(gè)小文件分批導(dǎo)入每批 5000 到 10000 行如果用的是 InnoDB導(dǎo)入期間臨時(shí)把innodb_flush_log_at_trx_commit設(shè)為 2導(dǎo)入后改回 1。6. 進(jìn)階用法用這份數(shù)據(jù)做城市距離計(jì)算和區(qū)域聚合6.1 用經(jīng)緯度算球面距離的 SQL 寫(xiě)法拿到城市經(jīng)緯度后一個(gè)高頻需求是「查附近城市」或「算兩個(gè)城市直線距離」。MySQL 里可以用 ST_Distance_Sphere 函數(shù)PostgreSQL 用 earthdistance 擴(kuò)展或 PostGIS。下面是不依賴(lài)擴(kuò)展的純 SQL 近似算法Haversine 公式適合快速驗(yàn)證。-- 計(jì)算北京到上海的大圓距離單位公里 SELECT ROUND( 6371 * ACOS( COS(RADIANS(39.9042)) * COS(RADIANS(31.2304)) * COS(RADIANS(121.4737) - RADIANS(116.4074)) SIN(RADIANS(39.9042)) * SIN(RADIANS(31.2304)) ), 2 ) AS distance_km;邏輯說(shuō)明6371 是地球平均半徑公里RADIANS把角度轉(zhuǎn)弧度ACOS里的表達(dá)式是球面余弦定理。參數(shù)上緯度用latitude經(jīng)度用longitude注意順序別寫(xiě)反。這個(gè)公式在短距離幾百公里內(nèi)精度夠用跨半球或極地附近誤差會(huì)變大生產(chǎn)環(huán)境建議用 PostGIS 的ST_DistanceSphere或ST_Distance帶 geography 類(lèi)型。6.2 按國(guó)家或洲聚合城市數(shù)量與中心點(diǎn)層級(jí)關(guān)系讓聚合變得簡(jiǎn)單按 parent_id 分組就能統(tǒng)計(jì)每個(gè)國(guó)家有多少城市再往上按洲分組。如果要算某個(gè)區(qū)域的城市中心點(diǎn)可以對(duì)經(jīng)緯度取平均值但注意經(jīng)度在 ±180 附近跨線時(shí)平均值會(huì)出錯(cuò)比如斐濟(jì)和新西蘭的城市經(jīng)度平均后可能跑到 0 度附近。更穩(wěn)的做法是用向量平均或直接取區(qū)域邊界框中心。-- 統(tǒng)計(jì)每個(gè)國(guó)家的城市數(shù)量按數(shù)量降序 SELECT p.name_cn AS country_name, COUNT(*) AS city_count FROM cities c JOIN cities p ON c.parent_id p.id WHERE c.level city AND p.level country GROUP BY p.id, p.name_cn ORDER BY city_count DESC LIMIT 20; -- 計(jì)算某國(guó)家所有城市的經(jīng)緯度平均值僅作粗略中心點(diǎn) SELECT p.name_cn AS country_name, AVG(c.latitude) AS avg_lat, AVG(c.longitude) AS avg_lon FROM cities c JOIN cities p ON c.parent_id p.id WHERE c.level city AND p.level country AND p.name_en France GROUP BY p.id, p.name_cn;邏輯說(shuō)明第一條自連接把城市和它的父級(jí)國(guó)家關(guān)聯(lián)起來(lái)按國(guó)家分組計(jì)數(shù)。第二條用 AVG 算平均坐標(biāo)只適合經(jīng)度范圍不跨 ±180 的國(guó)家。參數(shù)上JOIN條件用c.parent_id p.id依賴(lài)層級(jí)數(shù)據(jù)完整如果 parent_id 有缺失這些城市會(huì)被排除在統(tǒng)計(jì)外跑之前先用第 3 章的驗(yàn)證查詢確認(rèn)層級(jí)閉環(huán)。6.3 把城市數(shù)據(jù)做成可緩存的查詢接口如果業(yè)務(wù)里頻繁按城市名查經(jīng)緯度每次走數(shù)據(jù)庫(kù)不是最優(yōu)解?？梢栽趯?dǎo)入后把level city的記錄導(dǎo)出成一份輕量緩存比如 Redis 的 Hash 結(jié)構(gòu)key 用city:name_cn或city:name_envalue 存經(jīng)緯度和層級(jí)路徑。更新頻率低的話這份緩存可以長(zhǎng)期有效數(shù)據(jù)有更新時(shí)重新導(dǎo)入 SQL 后刷新緩存即可。注意中英文 key 要分開(kāi)存避免大小寫(xiě)和空格差異導(dǎo)致命中失敗。我自己的習(xí)慣是拿到任何一份帶層級(jí)的城市數(shù)據(jù)先跑一遍第 3 章的驗(yàn)證查詢確認(rèn)行數(shù)、層級(jí)、坐標(biāo)范圍三項(xiàng)都正常再開(kāi)始寫(xiě)業(yè)務(wù)查詢。這一步花五分鐘能省掉后面幾小時(shí)排查臟數(shù)據(jù)的后悔藥。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取