How to LOAD DATA INFILE in mysql with first col being Auto Increment?

Question

Currently, We have a table similar to this:

--------------------- ID | AField | BField| --------------------- 

The ID is Auto Increment

How do I create a CSV that can let the database auto populate the ID field with auto increment number?

We've tried the following CSV but it doesn't work:

afieldvalue, bfieldvalue (With Column definition but still doesn't work) 0,afieldvalue,bfieldvalue  NULL,afieldvalue,bfieldvalue 

Answer 1

The best thing to do is just include the 2 non-auto-increment columns in the CSV, and then explicitly set the ID column to NULL in the load data infile statement.

Something like this:

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