MySQL 将 .txt 转换为 .sql - 包含国家、州、城市、.. 名称的列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13936478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert .txt to .sql - Contains a list of country, state, city, .. names
提问by Toni Michel Caubet
I've been googling a lot for a SQL database with all (or the many as posible) the cities of the world,
我一直在谷歌上搜索一个包含世界上所有（或尽可能多）城市的 SQL 数据库，
But the best I've found is a .txt file with a format such as:
但我发现最好的是一个 .txt 文件，其格式如下：
-1049671 Kassel agglomeration 323216
-1049670 Kingston agglomeration 956716
-1049669 Derry agglomeration 98319
-1049668 Birmingham agglomeration 1000010
-1049667 West Palm Beach agglomeration 1370178
-1049666 Springfield agglomeration 748567
-1049665 Philadelphia agglomeration 6423182
-1049664 New York agglomeration 23637491
And also other lines are such as:
其他线路例如：
467009670 Lillo locality 3139 3972 -330 Spain Kastilien-La
Mancha Toledo
467009725 Tresjuncos locality 363 3972 -275 Spain Kastilien-La Mancha Cuenca
467009743 Alconchel de la Estrella locality 129 3972 -257 Spain Kastilien-La Mancha Cuenca
467009780 Valverde de Júcar locality 1284 3972 -220 Spain Kastilien-La Mancha Cuenca
467009793 Piqueras del Castillo locality 71 3972 -207 Spain Kastilien-La Mancha Cuenca
467009812 Almodóvar del Pinar locality 452 3972 -188 Spain Kastilien-La Mancha Cuenca
467009822 Paracuellos locality 123 3972 -178 Spain Kastilien-La Mancha Cuenca
467009857 Mira locality 780 3972 -143 Spain Kastilien-La Mancha Cuenca
467009965 Estivella locality 1454 3972 -35 Spain Valencia Valence
467009975 Faura locality 3872 3972 -25 Spain Valencia Valence
467010262 Valldemosa Valdemosa, Valldemossa locality 2098 3972 262 Spain Balearen Baléares
467010287 Lloseta locality 5939 3972 287 Spain Balearen Baléares
467010291 Inca locality 30441 3972 291 Spain Balearen Baléares
467010865 Terralba locality 9610 3972 865 Italy Sardinien
So the thing is I have no idea how to import this to mysql/sql tables.. and then be able to make querys.
所以问题是我不知道如何将其导入 mysql/sql 表..然后能够进行查询。
Is there a way? Any SQL complete database if not?
有办法吗？如果没有，任何 SQL 完整数据库？
采纳答案by Mariusz Sakowski
Look at the LOAD DATA LOCAL INFILE statement in mysql
查看mysql中的LOAD DATA LOCAL INFILE语句
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
basically you will end up with opening your file in excel or something like that, saving as .csv and then issue query
基本上你最终会在 excel 或类似的东西中打开你的文件，保存为 .csv 然后发出查询
LOAD DATA LOCAL INFILE 'c:\db.csv' INTO TABLE some_table FIELDS TERMINATED BY ';'
of course your 'some_table' has to have appropriate structure and you have to adjust field separator to what you have chosen when saving .csv file.
当然，您的“some_table”必须具有适当的结构，并且您必须将字段分隔符调整为您在保存 .csv 文件时选择的内容。
look at the docs I have provided to see further options, like setting file encoding or ignoring n first lines (useful if your file has header)
查看我提供的文档以查看更多选项，例如设置文件编码或忽略 n 第一行（如果您的文件有标题，则很有用）
回答by Robert H
Check out the GeoLite databases .
查看GeoLite 数据库。
They are designed for geo location and are pretty complete (383361 rows). The Geolite-city location's file are provided as a CSV which you should be able to import with minimal issues.
它们专为地理位置而设计，非常完整（383361 行）。Geolite-city 位置的文件以 CSV 格式提供，您应该能够以最少的问题导入。
To import a CSV file see the following post: How to import a csv file into MySQL workbench?and The MySQL manual
要导入 CSV 文件，请参阅以下帖子：如何将 csv 文件导入 MySQL 工作台？和MySQL 手册
回答by nayef harb
yeah first import it into excel(tab delimited) then import it to SQL by the import export tool
是的，首先将其导入excel（制表符分隔），然后通过导入导出工具将其导入SQL
and if you may provide the file plz
如果你可以提供文件请
