使用 LOAD DATA INFILE 导入 MySQL 表时如何跳过 CSV 文件中的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2139069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
提问by Camsoft
I've got a CSV file with 11 columns and I have a MySQL table with 9 columns.
我有一个包含 11 列的 CSV 文件，我有一个包含 9 列的 MySQL 表。
The CSV file looks like:
CSV 文件如下所示：
col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11
and the MySQL table looks like:
和 MySQL 表看起来像：
col1, col2, col3, col4, col5, col6, col7, col8, col9
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.
我需要将 CSV 文件的第 1-8 列直接映射到 MySQL 表的前 8 列。然后我需要跳过 CSV 文件中的下两列，然后将 CSV 文件的第 11 列映射到 MySQL 表的第 9 列。
At the moment I am using the following SQL command:
目前我正在使用以下 SQL 命令：
LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
But the above code maps the first 9 columns of CSV file to the 9 columns in the MySQL table.
但是上面的代码将CSV文件的前9列映射到MySQL表的9列。
回答by grapefrukt
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);
回答by brzhang
step1.deal with awk.
step1.处理awk。
cat file.txt |awk '{print ,,...}'>new_file.txt
step2.load into mysql.
step2.加载到mysql中。
load data local infile 'new_file' into table t1(...)
the method below is simple,but not allowed in lower version of mysql.
下面的方法很简单，但在较低版本的mysql中不允许。
LOAD DATA INFILE 'file.txt'
INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);
回答by Ataboy Josef
@deemi:
@迪米：
The only way to ignore the @dummyis by setting the field's Default to AUTO INCREMENT.
So you can skip the field and just code like this,
忽略 的唯一方法@dummy是将字段的默认设置为AUTO INCREMENT。因此，您可以跳过该字段并像这样编写代码，
LOAD DATA INFILE 'file.txt'
INTO TABLE t1 (column2, column3, column4, column5);
//assumes that the fieldname column1is set to AUTO INCREMENTby default.
//假设字段名默认column1设置为AUTO INCREMENT。
回答by Ataboy Josef
I think there is one more change in the code:
我认为代码中还有一个变化：
The following SQL command:
以下 SQL 命令：
LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\n'
-will probably result in an data truncation error.
- 可能会导致数据截断错误。
So it is better to use LINES TERMINATED BY '\r\n'instead of LINES TERMINATED BY '\n'
所以最好使用LINES TERMINATED BY '\r\n'而不是LINES TERMINATED BY '\n'
SO the code will be:
所以代码将是：
LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\r\n'
