如何将数据从 CSV 导入 MySQL?

2021-12-09 00:00:00 csv import 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 	' ENCLOSED BY '"'
  LINES TERMINATED BY '
'
  IGNORE 1 LINES;

我为我的英语道歉！

提前致谢！

推荐答案

您可以指定列并将不需要的列标记为@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 '	' ENCLOSED BY 
LINES TERMINATED BY '
'
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 '	' ENCLOSED BY 
LINES TERMINATED BY '
'
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.

相关文章