mysql - csv导入没有导入行

Question

尝试将 csv 格式的货币导入数据库时​​出现错误:

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\currencies.csv'
    INTO TABLE `test`.`currencies`
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
    LINES TERMINATED BY '\r\n' IGNORE 1 LINES
    (`alpha3`, `number`, `decimals`, `name`, `countries`);

示例 csv:

alpha3,number,decimals,name,countries
AED,784,2,"United Arab Emirates dirham","[""ARE""]"
AFN,971,2,"Afghan afghani","[""AFG""]"
ALL,008,2,"Albanian lek","[""ALB""]"
AMD,051,2,"Armenian dram","[""ARM""]"

我认为是因为国家字段。

<小时/>

创建表的脚本:

 CREATE TABLE `currencies` (
    `alpha3` CHAR(3) NOT NULL,
    `number` CHAR(3) NULL DEFAULT NULL,
    `decimals` TINYINT(1) NULL DEFAULT NULL,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `countries` TEXT NULL,
    PRIMARY KEY (`alpha3`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

Answer 1

对我有用:

mysql> LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\Temp\\currencies.csv'
    ->  INTO TABLE `try`.`currencies`
    ->     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
    ->     LINES TERMINATED BY '\r\n' IGNORE 1 LINES
    ->     (`alpha3`, `number`, `decimals`, `name`, `countries`);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from currencies;
+--------+--------+----------+-----------------------------+-----------+
| alpha3 | number | decimals | name                        | countries |
+--------+--------+----------+-----------------------------+-----------+
| AED    | 784    |        2 | United Arab Emirates dirham | ["ARE"]   |
| AFN    | 971    |        2 | Afghan afghani              | ["AFG"]   |
| ALL    | 008    |        2 | Albanian lek                | ["ALB"]   |
| AMD    | 051    |        2 | Armenian dram               | ["ARM"]   |
+--------+--------+----------+-----------------------------+-----------+
4 rows in set (0.00 sec)

我在 Windows 上使用版本 5.6.12。您使用什么版本和操作系统？ (我怀疑版本是否重要。)