mysql - 如何在 mysql 中加载 geonames.org 数据集

Question

我在加载 geonomes 数据集时遇到问题。这是我的过程:

首先我创建了我的数据库GEONAMES

然后我创建我的表:

CREATE TABLE `geoname` (
`geonameid` int(11) NOT NULL,
`name` varchar(200) DEFAULT NULL,
`asciiname` varchar(200) DEFAULT NULL,
`alternatenames` varchar(4000) DEFAULT NULL,
`latitude` decimal(10,7) DEFAULT NULL,
`longitude` decimal(10,7) DEFAULT NULL,
`fclass` char(1) DEFAULT NULL,
`fcode` varchar(10) DEFAULT NULL,
`country` varchar(2) DEFAULT NULL,
`cc2` varchar(60) DEFAULT NULL,
`admin1` varchar(20) DEFAULT NULL,
`admin2` varchar(80) DEFAULT NULL,
`admin3` varchar(20) DEFAULT NULL,
`admin4` varchar(20) DEFAULT NULL,
`population` int(11) DEFAULT NULL,
`elevation` int(11) DEFAULT NULL,
`gtopo30` int(11) DEFAULT NULL,
`timezone` varchar(40) DEFAULT NULL,
`moddate` date DEFAULT NULL,
PRIMARY KEY (`geonameid`),
KEY `name` (`name`),
KEY `asciiname` (`asciiname`),
KEY `latitude` (`latitude`),
KEY `longitude` (`longitude`),
KEY `fclass` (`fclass`),
KEY `fcode` (`fcode`),
KEY `country` (`country`),
KEY `cc2` (`cc2`),
KEY `admin1` (`admin1`),
KEY `population` (`population`),
KEY `elevation` (`elevation`),
KEY `timezone` (`timezone`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

接下来是我的问题所在，我尝试加载数据并获取:

最后一条错误信息

无效的 utf8 字符串:''Afikanisitani,'Apekanikana,A Phu Han(阿富汗),A Phú Hãn '

这是我加载数据的查询:

LOAD DATA LOCAL INFILE 'allCountries.txt'
INTO TABLE geoname
CHARACTER SET 'UTF8'
(geonameid, name, asciiname, alternatenames, latitude, longitude, fclass,       fcode, country, cc2, admin1, admin2, admin3, admin4, population, elevation, gtopo30, timezone, moddate);

我在 sequel pro 命令行中运行我的查询。

Answer 1

我得到了 the same error on MySQL 5.7.13 - 这很奇怪，因为导入 allCountries.txt 文件似乎在 MySQL 5.6 中工作正常

不确定what exactly changed between MySQL 5.6 and 5.7但是跟5.7默认的character_set和collat​​ion有关系

基于 this Q&A我将默认字符集更新为 utf8mb4，这样我就可以毫无问题地加载 allCountries.txt 文件

使用 mysql -u root -p 在终端中登录 mysql 并运行以下查询:
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collat​​ion%';

默认的 MySQL 5.7.13 响应如下所示:

+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

(在 OS X 上工作)然后我需要将一个 my.cnf 文件添加到 /etc/my.cnf
- 你可以复制它:
sudo cp/usr/local/mysql/support-files/my-default.cnf/etc/my.cnf
- 将 my.cnf 分配给 _mysql 否则它将无法读取它(常见问题如果 my.cnf 文件存在但要么由 root 拥有，要么具有过于宽松的权限)
sudo chown _mysql/etc/my.cnf
- 更新配置文件以包含以下内容(在相关部分 - 如果不存在则添加它们)

[client]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4

- 重新启动 mysql(以下命令适用于 OS X):

sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

- 在 mysql 中运行相同的 SHOW VARIABLE 命令，结果是

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

...现在 allCountries.txt 文件加载应该可以正常工作了。