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)

Impostor

Answer 1

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?

Impostor

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 – Sybil

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

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

Answer 2

this can do for u..

     load data local infile 'data.csv' into table tbl fields terminated by ','
     enclosed by '"'
     fields terminated by '\t'
     lines terminated by '\n'
     (a,b,c,d)

for more reference see this link

Sybil