How to Efficiently Import CSV Data into MySQL Tables?

Database Operations: Importing CSV Data into MySQL Tables

Need to populate your MySQL database with data from a CSV file? Here's a detailed guide:

CSV File Input

Before importing, you'll need to ensure your CSV file is properly formatted. Remove any empty rows and adjust data alignments if necessary.

Using MySQL's LOAD DATA Command

MySQL provides the efficient LOAD DATA command for CSV imports. Here's the basic syntax:

LOAD DATA LOCAL INFILE 'file_path'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column_list)

Assigning Column Names

To set the first row of the CSV file as column names, you can use the LOAD DATA's IGNORE LINES option:

LOAD DATA LOCAL INFILE 'file_path'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(column_list)

Example

Let's import a CSV file named 'data.csv' into a table named 'user_data' with the columns 'name', 'age', and 'city':

LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE user_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, age, city)

This command will import the CSV data into the specified table, using the headers from the first row as column names.

The above is the detailed content of How to Efficiently Import CSV Data into MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!