How to Populate Auto Increment Columns with LOAD DATA INFILE in MySQL?

Populating Auto Increment Columns with LOAD DATA INFILE

When loading data into MySQL tables with the LOAD DATA INFILE command, handling auto-increment columns can be a bit tricky. By default, if a value is not specified for an auto-increment column, the database inserts an appropriate sequential value. However, if you want the database to automatically populate the auto-increment column with values, you can use a specific approach.

To create a CSV that allows auto-population of ID columns using auto increment numbers, follow these steps:

1. Include only the non-auto-increment columns (AField and BField) in your CSV file.
2. Specify the column names and types in the CSV file header (optional but recommended).
3. In the LOAD DATA INFILE statement, explicitly set the ID column to NULL, as shown in the example below:

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
(AField, BField)
SET ID = NULL;

By following this approach, the database will automatically populate the ID column with the appropriate sequential values, starting with 1.

The above is the detailed content of How to Populate Auto Increment Columns with LOAD DATA INFILE in MySQL?. For more information, please follow other related articles on the PHP Chinese website!