MySQL:将csv导入带引号的表中

Question

我有一个 csv，其中每一行都以引号开头，也以引号结尾。将 csv 加载到表中时如何忽略行开头和结尾的引号？

LOAD DATA LOCAL INFILE '/path/data.csv' 
INTO TABLE test1 
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\"\n'
IGNORE 1 ROWS;

我已经尝试过

OPTIONALLY ENCLOSED BY '"'

但这指的是每个特定字段而不是整行。

Answer 1

正如 Shadow 和 Barmar 所评论的，答案就在 the documentation :

If all the input lines have a common prefix that you want to ignore, you can use LINES STARTING BY 'prefix_string' to skip the prefix and anything before it. If a line does not include the prefix, the entire line is skipped. [...] The FIELDS TERMINATED BY, LINES STARTING BY, and LINES TERMINATED BY values can be more than one character.

因此，使用:

LOAD DATA LOCAL INFILE '/path/data.csv' 
INTO TABLE test1 
    FIELDS TERMINATED BY ';' 
    LINES STARTING BY '"'
    LINES TERMINATED BY '"\n'
    IGNORE 1 ROWS;