How Can I Import a CSV File into MySQL When Column Orders Don't Match?

Importing Data into MySQL from a CSV File

Importing data from a CSV file into MySQL is often encountered, especially when dealing with large datasets. However, what happens when the CSV file's columns are not in the same order as the target MySQL table's columns?

Automating Column Assignment

To address this issue, the LOAD DATA INFILE command provides a solution. By specifying the column names explicitly, we can control which CSV column corresponds to each target table column. Consider the following syntax:

LOAD DATA INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

• 'abc.csv': The path to the CSV file
• abc: The target table name
• FIELDS TERMINATED BY ',': Specifies that fields in the CSV file are separated by commas
• ENCLOSED BY '"': Indicates that field values are enclosed in double quotes
• LINES TERMINATED BY 'rn': Specifies that lines in the CSV file end with a carriage return and line feed
• IGNORE 1 LINES: Skips the first line of the CSV file, which may contain headers
• (col1, col2, col3, col4, col5...): A comma-separated list of target table column names

For MySQL 8.0 users, the LOCAL keyword is set to False by default, which introduces security risks. To overwrite this, follow the instructions in the MySQL documentation. However, this does not resolve the security issue but acknowledges that you are willing to take the risk.

The above is the detailed content of How Can I Import a CSV File into MySQL When Column Orders Don't Match?. For more information, please follow other related articles on the PHP Chinese website!