mysql - 使用 MySQL 查询导入 CSV 文件(指定列)

Question

我试图将一个 CSV 文件导入到一个包含名为“id”的自动递增列的表中。我不想插入 id 列，因为它本身编号，但经过多次尝试后我无法让它工作。这是我尝试过的查询:

LOAD DATA LOCAL INFILE 'C:\\CaliberMap.csv'
INTO TABLE sefeed_calibermap 
(SiteCaliber,SE1Caliber,SE2Caliber,Other1Caliber,Other2Caliber,Other3Caliber,Other4Caliber)
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

当我执行查询时，我收到以下消息:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMI' at line 4

我被难住了!我在阅读了互联网上许多不同地方的例子后写了这个查询，所以语法看起来是正确的。

我正在运行 PhpMyAdmin 5.6.10 版。

Answer 1

我认为您对阅读的文档感到困惑。试试这个:

LOAD DATA LOCAL INFILE 'C:\\CaliberMap.csv'
INTO TABLE sefeed_calibermap     
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(SiteCaliber,SE1Caliber,SE2Caliber,Other1Caliber,Other2Caliber,Other3Caliber,Other4Caliber);

表格字段应该在最后。