How to Populate Auto-Increment IDs When Using LOAD DATA INFILE in MySQL?

Auto-Increment ID Population in MySQL LOAD DATA INFILE

When working with tables that utilize auto-increment primary keys, such as the ID column in the provided table, it can be challenging to populate data through LOAD DATA INFILE. The task requires careful handling to ensure that the ID field is properly auto-populated.

To address this issue, the most effective approach is to omit the ID column from the CSV file entirely. Instead, explicitly set the ID column to NULL in the LOAD DATA INFILE statement. This allows the database to automatically assign auto-increment values.

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

In this statement, the CSV file is specified at '/tmp/data.csv', and the table to be populated is 'your_table'. The FIELDS clause defines the non-auto-increment columns to be imported. The SET clause explicitly sets the ID column to NULL, enabling auto-increment population.

By employing this technique, you can seamlessly import data into tables with auto-increment primary keys, ensuring that the ID column is correctly auto-populated.

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