MySQL Load Data Infile auto incremented ID value

Question

I want to insert an ID column to my table and get data of this table from a text file. For example my text file is like that:

12    1212    4989    121
121   23      123     110
789   99      234     544
...

and it has approximately 20M rows. I want to insert this data to a table and include an auto incremented ID value column for it. I will use "Load data infile", but I want to create my table as indicated below:

id    a     b       c       d
---   ---   ---     ---     ---  
1    12    1212    4989    121
2    121   23      123     110
3    789   99      234     544
...

How can I create this kind of table using mysql (workbench)

Solution

first, create table with column ID has auto increment property:

CREATE TABLE mytable (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     a INT NULL,
     b INT NULL,
     c INT NULL,
     d INT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

then you should load data into table with load data infile by giving column names:

LOAD DATA LOCAL INFILE 'C:/DATA/mydata.txt'
INTO TABLE test.mytable
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
(a, b, c, d) SET ID = NULL;

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

Comments

[sourcecode]

it was common sense to create table first.and also the "load infile" has only 4 columns i.e.autoincrement column is not going to fill

[Prince Paul]

If any of your field names include periods, you'll need to surround them with backticks.

[bob marti]

it is incremented but it is not an order.and how can i make a order