MySQL 无法使用本地加载数据导入以 \r\r 结尾的 CSV 行

Question

我正在尝试导入应该为我提供城市数据库的服务提供的CSV。

csv格式的城市的方式格式为：

CC_FIPS FULL_NAME_ND
AN  Aixas
AN  Aixirivall
AN  Aixovall
AN  Andorra la Vella
AN  Ansalonga

所以我想出了以下 mysql 查询：

LOAD DATA LOCAL
INFILE 'GEODATASOURCE-CITIES-FREE.TXT'
INTO TABLE `city`
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES
(cc_fips,full_name_nd);

它们都被导入，但full_name_nd列下的条目后面有一个空格。因为如您所见，每行末尾都有额外的rn。

我可以cat short.txt | od -c确认

0000000   C   C   _   F   I   P   S  t   F   U   L   L   _   N   A   M
0000020   E   _   N   D  r  n   A   N  t   A   i   x   a   s  r  n
0000040  r  n   A   N  t   A   i   x   i   r   i   v   a   l   l  r
0000060  n  r  n   A   N  t   A   i   x   o   v   a   l   l  r  n
0000100  r  n   A   N  t   A   n   d   o   r   r   a       l   a
0000120   V   e   l   l   a  r  n  r  n   A   N  t   A   n   s   a
0000140   l   o   n   g   a  r  n  r  n   A   N  t   A   n   y   o
0000160   s  r  n  r  n   A   N  t   A   r   a   n   s  r  n  r
0000200  n   A   N  t   A   r   i   n   s   a   l  r  n  r  n   A
0000220   N  t   A   u   b   i   n   y   a  r  n  r  n   A   N  t

因此，使用rnrn修改查询：

LOAD DATA LOCAL
INFILE 'GEODATASOURCE-CITIES-FREE.TXT'
INTO TABLE `city`
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rnrn'
IGNORE 1 LINES
(cc_fips,full_name_nd);

但是该查询不起作用:(

Query OK, 0 rows affected (0.40 sec)
Records: 0  Deleted: 0  Skipped: 0  Warnings: 0

我什至尝试过

LOAD DATA LOCAL
INFILE 'GEODATASOURCE-CITIES-FREE.TXT'
INTO TABLE `city`
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES
(cc_fips,@col_with_spaces)
SET full_name_nd = TRIM(@col_with_spaces);

但显然，TRIM()不适用于r、n或其他特殊空格。

希望有人能帮忙。

问候

Answer 1

不确定这是否有效，因为我还没有测试过，但是您是否尝试过替换特殊字符。像这样：

LOAD DATA LOCAL
INFILE 'GEODATASOURCE-CITIES-FREE.TXT'
INTO TABLE `city`
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn'
IGNORE 1 LINES
(cc_fips,@col_with_spaces)
SET full_name_nd = TRIM(REPLACE(@col_with_spaces, 'rn', ''));

如果不起作用，请尝试使用""而不是"\r"。

Answer 2

解决了。 在使用我在问题中发布的 SQL 查询之前，先在'GEODATASOURCE-CITIES-FREE.TXT'上使用dos2unix工具解决了它。期待任何能解释为什么将其转换为unix行结尾可以解决它的人。