Welcome to ZaiZheLe Developer Zone-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

Import datetime field correctly into mysql table using load data infile

I'm facing problem for datetime fields while importing file using mysql command load data local infile, but if I import the same file using Table Data Import Wizard in MySQL Workbench it works perfectly. But this option is extremely slow. It's been more than 2 hrs only 10% data imported for only 12k data sample. As I found out that load data local infile command is much faster, I'm going for it but stuck currently for datetime field.

Sample data file content: (Last 2 fields are created_at and updated_at fields which are having problem while importing)

740c02a0-54d9-11eb-b1aa-1312597791fc,RAMKKUMAR,[email protected],2018-16-05 21:05:00,2018-22-11 4:47:00

Sample Code:

load data local infile 'C:/Newwamp64/bin/mysql/mysql5.7.26/bin/for.csv' 
    into table mytable.users 
    FIELDS OPTIONALLY ENCLOSED BY '"'  
    TERMINATED BY ',' 
    LINES TERMINATED BY '
' 
    IGNORE 1 LINES 
        SET created_at = STR_TO_DATE(@created_at, '%Y-%d-%m %H:%i:%s');

Result All fileds are fine, except following two -

created_at -> NULL

updated_at -> 0000-00-00 00:00:00


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
等待大神解答

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to ZaiZheLe Developer Zone-Open, Learning and Share
...