When working with an existing table where the ID column is an auto-increment field, importing data through LOAD DATA INFILE can pose a challenge. This article provides an efficient solution to handle such scenarios.
For clarity, consider the following table structure:
--------------------- ID | AField | BField| ---------------------
where ID is an auto-increment field.
To create a CSV file that enables the database to auto-populate the ID field with auto-increment numbers, the ideal approach is to include only the non-auto-increment columns in the CSV. Subsequently, explicitly set the ID column to NULL in the LOAD DATA INFILE statement.
This approach ensures that the database generates the auto-increment numbers for the ID field during the import process. For example, consider the following CSV:
afieldvalue, bfieldvalue
The LOAD DATA INFILE statement would look like this:
LOAD DATA INFILE '/tmp/data.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' (AField, BField) SET ID = NULL;
By following this approach, you can seamlessly populate the auto-increment ID field when importing data through LOAD DATA INFILE in MySQL.
The above is the detailed content of How to Populate Auto-Increment Fields with LOAD DATA INFILE in MySQL?. For more information, please follow other related articles on the PHP Chinese website!