How to Import CSV Data into MySQL, Skipping the First Row and Auto-Incrementing the Primary Key?

Import CSV to MySQL: Skipping the First Row and Auto-Incrementing Primary Key

When importing data from a CSV file to a MySQL table, it may be necessary to skip the first row of the CSV file, which typically contains column headers, and also to have MySQL automatically generate values for a primary key column.

Skipping the First Row

To skip the first row of the CSV file during import, use the IGNORE keyword in the LOAD DATA statement:

LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE your_table
IGNORE 1 LINES
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Auto-Incrementing Primary Key

If your table's primary key column is set to auto-increment, you can leave it out of the CSV file, and MySQL will automatically generate values for it during import. To do this, specify NULL for the primary key column in the SET clause:

LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
SET>

Example

Consider the CSV file provided in the question, which is missing the first row (column headers) and the id column. To import this data into a MySQL table named advertisement with an auto-incrementing primary key column named id, use the following SQL statement:

LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE advertisement
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
SET>

The above is the detailed content of How to Import CSV Data into MySQL, Skipping the First Row and Auto-Incrementing the Primary Key?. For more information, please follow other related articles on the PHP Chinese website!