mysql - 如何将数据从 CSV 导入 MySQL？

Question

我有以下结构的 csv 文件:

    A   BA0011  U206    NAME    0000000000000149.00     000000.00  0000000000000118.93  S   N   N
    A   BB0011  U206    NAME    0000000000000150.00     000000.00  0000000000000118.93  S   N   N
    A   BC0011  U206    NAME    0000000000000151.00     000000.00  0000000000000118.93  S   N   N
    A   BD0011  U206    NAME    0000000000000152.00     000000.00  0000000000000118.93  S   N   N
    A   BE0011  U206    NAME    0000000000000153.00     000000.00  0000000000000118.93  S   N   N

使用以下 MySQL 函数如何导入 MySQL 表中的第一列和第三列:

LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
  FIELDS TERMINATED BY \t' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

我为我的英语道歉!

提前致谢!

Answer 1

您可以指定列并将不需要的列标记为'@dummy'。

LOAD DATA INFILE 'data.csv'
INTO TABLE t1
(column1, @dummy, column2, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy)
FIELDS TERMINATED BY '\t' ENCLOSED BY 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

根据需要替换t1、column1 和column2。要设置不在数据文件中的其他列，您可以这样做:

LOAD DATA INFILE 'data.csv'
INTO TABLE t1
(column1, @dummy, column2, @dummy, @val, @dummy, @dummy, @dummy, @dummy, @dummy)
FIELDS TERMINATED BY '\t' ENCLOSED BY 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
SET column3 = "test", column4 = CURRENT_TIMESTAMP, column5 = @val/10;

如需进一步引用，我建议您查看 MySQL reference .