How to Load Data with Auto Increment ID in MySQL Without Duplicate or Incorrect Values?

Loading Data with Auto Increment ID in MySQL

When importing data into a MySQL table with an auto-increment ID column, it's essential to handle the auto-increment mechanism correctly. If not handled appropriately, importing data can lead to duplicate or incorrect ID values.

Problem:

You have a table with an auto-increment ID column and want to load data from a CSV file. However, when you attempt to import a CSV file with the ID column included, the database does not auto-populate the ID field.

Solution:

To ensure that the database auto populates the ID field with auto-increment numbers, do not include the ID column in the CSV file. Instead, explicitly set the ID column to NULL in the LOAD DATA INFILE statement.

Example:

Consider the following CSV file with only the non-auto-increment columns:

afieldvalue,bfieldvalue

Now, use the following LOAD DATA INFILE statement to import this data into your table:

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

By setting ID = NULL, you instruct the database to auto-assign auto-increment numbers to the ID column. This approach ensures the correct handling of auto-increment values during data loading.

The above is the detailed content of How to Load Data with Auto Increment ID in MySQL Without Duplicate or Incorrect Values?. For more information, please follow other related articles on the PHP Chinese website!