MySQL加载日期格式为mm / dd / yyyy [英] MySQL load dates in mm/dd/yyyy format
问题描述
我有一个MySQL加载脚本几乎可以工作,它是完美的除了日期列,这不是一个MySql友好的格式。
load data infile'/Users/pfarrell/sandbox/waybase/folklore/Titles_1976.csv'
into table fix76
以','结尾的字段
由'''
忽略1行
(patentId,USPatentNum,title,grantDate,filedDate)
问题是我的日期是以mm / dd / yyyy格式,看起来像str_to_date
函数是我想要的,但我不知道如何在加载命令中使用它。
我想例如:
grantDate = STR_TO_DATE(something,'%m /%d /%Y'),
$
解决方案将日期字符串转换为用户定义的变量,然后使用
STR_TO_DATE(@date,'%m /%d /%Y')
将它们转换为MySQL日期。 >
请尝试以下操作:
load data infile'/ Users / pfarrell / sandbox / wayb
到表fix76
以','结尾的字段
被''包含
忽略1行
(patentId,USPatentNum,标题,@grantDate,@filedDate)
设置grantDate = STR_TO_DATE(@grantDate,'%m /%d /%Y'),
filedDate = STR_TO_DATE(@filedDate,'%m /%d / %Y')
I've got a MySQL load script that almost works, it is perfect except for the date columns, which are not in a MySql friendly format.
load data infile '/Users/pfarrell/sandbox/waybase/folklore/Titles_1976.csv' into table fix76 fields terminated by ',' enclosed by '"' ignore 1 lines ( patentId, USPatentNum, title, grantDate, filedDate)The problem is that my dates are in mm/dd/yyyy format. Looks like the str_to_date function is what I want, but I can't figure out how to use it in the load command.
I'm envisioning something like:grantDate = STR_TO_DATE(something, '%m/%d/%Y'),but that doesn't work.
解决方案You can load the date strings into user-defined variables, and then use
STR_TO_DATE(@date, '%m/%d/%Y')
to convert them to MySQL dates.Try this:
load data infile '/Users/pfarrell/sandbox/waybase/folklore/Titles_1976.csv' into table fix76 fields terminated by ',' enclosed by '"' ignore 1 lines ( patentId, USPatentNum, title, @grantDate, @filedDate) set grantDate = STR_TO_DATE(@grantDate, '%m/%d/%Y'), filedDate = STR_TO_DATE(@filedDate, '%m/%d/%Y')
这篇关于MySQL加载日期格式为mm / dd / yyyy的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!