mysql导⼊csv⽂件⽇期,MySQL导⼊CSV转换⽇期格式
I'm importing data from a csv to a MySQL table. I need to convert the date format from String to Date.
From Starting format to finale format:
Mon Feb 04 00:00:00 UTC 2011 ---> 2011-02-04 00:00:00
I've done it sucessfully:
select str_to_date('Mon Feb 04 00:00:00 UTC 2011', '%a %b %d %k:%i:%s UTC %Y');
Now I'm writing the script to do all the import from the csv, there are 2 columns with the date to be converted, but I'm stuck with a MySQL syntax exception on the set part.
My SQL script:
load data local infile 'movimento.csv' into table movimento
fields terminated by ',' lines terminated by '\n'
(id, anno, creditore, @data_pag, @data_spost, descrizione)
set data_pagamento = str_to_date(@data_pag, '%a %b %d %k:%i:%s UTC %Y')
set data_spostamento = str_to_date(@data_spost, '%a %b %d %k:%i:%s UTC %Y')
show warnings;
I'm stuck with a syntax exception on the set part. The error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set data_spostamento = str_to_date(@data_spost, '%a %b %d %k:%i:%s UTC %Y')
show' at line 5
>
mysql下载starting the serverWhat's the right syntax?
解决⽅案
The syntax is incorrect. Try this one -
LOAD DATA LOCAL INFILE 'movimento.csv' INTO TABLE movimento
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
(ID, anno, creditore, @data_pag, @data_spost, descrizione)
SET
data_pagamento = STR_TO_DATE(@data_pag, '%a %b %d %k:%i:%s UTC %Y'),
data_spostamento = STR_TO_DATE(@data_spost, '%a %b %d %k:%i:%s UTC %Y')

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。