当使用LOAD DATA INFILE导入MySQL表时,如何跳过CSV文件中的列? [英] How to skip columns in CSV file when importing into MySQL table using 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档案如下:
col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11
如:
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命令:
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.
推荐答案
您还可以通过将
分配给用户变量和
不将变量分配给表
列来舍弃输入值:
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);
这篇关于当使用LOAD DATA INFILE导入MySQL表时,如何跳过CSV文件中的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!