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)

score 19 · Answer 1 · edited May 23 '17 at 11:46

19

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?

edited May 23 '17 at 11:46

Community

1
1

answered Dec 29 '12 at 16:55

JoshuaJeanThree

1,382
2
22
41

1

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 – sourcecode Dec 29 '12 at 17:00
If any of your field names include periods, you'll need to surround them with backticks. – shacker Oct 28 '14 at 18:26
it is incremented but it is not an order.and how can i make a order – bob marti Mar 27 '17 at 13:52

sourcecode · Answer 2 · 2012-12-29T16:59:16.103

0

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

edited Dec 29 '12 at 16:59

answered Dec 29 '12 at 16:53

sourcecode

1,802
2
15
17

MySQL Load Data Infile auto incremented ID value

2 Answers2