mysql 无法保存 n,mysql无法使用本地加载数据导入以\r\n\r\n结尾的csv行

csv格式的城市格式如下:

CC_FIPS FULL_NAME_ND

AN Aixas

AN Aixirivall

AN Aixovall

AN Andorra la Vella

AN Ansalonga

LOAD DATA LOCAL

INFILE 'GEODATASOURCE-CITIES-FREE.TXT'

INTO TABLE `city`

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES

(cc_fips,full_name_nd);

它们都被导入,但是

full_name_nd

后面有空格。因为你可以看到

\r\n

在每行的末尾。

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

所以修改查询,使用

\r\n\r\n

:

LOAD DATA LOCAL

INFILE 'GEODATASOURCE-CITIES-FREE.TXT'

INTO TABLE `city`

FIELDS TERMINATED BY '\t'

LINES TERMINATED BY '\r\n\r\n'

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 '\r\n'

IGNORE 1 LINES

(cc_fips,@col_with_spaces)

SET full_name_nd = TRIM(@col_with_spaces);

但显然,

TRIM()

\r

,

\n

或其他特殊空格。

当做,