mysql 日期格式转换mm/dd,MySQL加载日期格式为mm / dd / yyyy

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')