如何将数据从CSV导入MySQL？

问题描述：

我有一个csv文件，其结构如下：

I have csv file with following structure:

    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表中导入第一列和第三列：

Using following MySQL Function How I can import first and third column in MySQL table:

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

我对我的英语表示歉意！

I apologize for my English!

提前感谢！

答

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

You can specify the columns and mark the unnecessary columns as '@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 。要设置不在数据文件中的其他列，您可以这样做：

Replace t1, column1 and column2 as you like. To set other columns which are not in the data file, you can do it like this:

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参考。

For further reference, I recommend you to take a look at the MySQL reference.

如何将数据从CSV导入MySQL？

相关推荐