MySQL导入CSV转换日期格式 [英] MySQL importing a CSV converting the date format
问题描述
我正在将数据从csv
导入到MySQL
表.我需要将日期格式从String
转换为Date
.
从开始格式到结局格式:
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
我已经成功完成了:
select str_to_date('Mon Feb 04 00:00:00 UTC 2011', '%a %b %d %k:%i:%s UTC %Y');
现在我正在编写脚本来执行从csv
的所有导入,有两列的日期要转换,但是我在set
部分遇到了MySQL
语法异常
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.
我的SQL
脚本:
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;
我在set
部分遇到语法异常.错误:
I'm stuck with a syntax exception on the set
part. The error:
错误1064(42000):您的SQL语法有错误;检查与您的MySQL服务器版本相对应的手册以获取正确的语法,以在'set data_spostamento = str_to_date(@data_spost,'%a%b%d%k:%i:%s UTC%Y')附近使用 在第5行显示 >
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 >
什么是正确的语法?
推荐答案
语法不正确.试试这个-
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')
这篇关于MySQL导入CSV转换日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!