使用 LOAD DATA INFILE 导入 MySQL 表时如何跳过 CSV 文件中的列?

2021-11-20 00:00:00 csv mysql load-data-infile

我有一个有 11 列的 CSV 文件，我有一个有 9 列的 MySQL 表.

I've got a CSV file with 11 columns and I have a MySQL table with 9 columns.

CSV 文件如下所示:

The CSV file looks like:

col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11

MySQL 表如下所示:

and the MySQL table looks like:

col1, col2, col3, col4, col5, col6, col7, col8, col9

我需要将 CSV 文件的第 1-8 列直接映射到 MySQL 表的前 8 列.然后我需要跳过 CSV 文件中接下来的两列，然后将 CSV 文件的第 11 列映射到 MySQL 表的第 9 列.

I need to map the columns 1-8 of CSV file directly to the first 8 columns of the MySQL table. I then need to skip the next two columns in the CSV file and then map column 11 of CSV file to column 9 of MySQL table.

目前我正在使用以下 SQL 命令:

At the moment I am using the following SQL command:

LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'

但是上面的代码将CSV文件的前9列映射到了MySQL表的9列.

But the above code maps the first 9 columns of CSV file to the 9 columns in the MySQL table.

推荐答案

来自 Mysql 文档:

您还可以通过以下方式丢弃输入值将其分配给用户变量和不将变量分配给表列:

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'  
INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);

相关文章