How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE

I'm using MySQL's LOAD DATA LOCAL INFILE SQL statement to load data from a CSV file into an existing database table.

Here is an example SQL statement:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, address, dateOfBirth)

The third column in the CSV that maps to the dateOfBirth field currently has the date in the following format:

14-Feb-1开发者_JAVA技巧0

How can I modify the above SQL statement to format the date into MySQL's date format i.e. 2010-02-14?

I know how to convert a string date when using normal INSERT syntax using:

STR_TO_DATE('14-Feb-10', '%d-%b-%y')

You need to use the SET clause, along with a variable to reference the contents of the row at that column. In your column list, you assign your date column to a variable name. You can then use it in your SET statement. (Note, I haven't got MySQL in front of me to test this on.)

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, address, @var1)
set dateOfBirth = STR_TO_DATE(@var1, '%d-%b-%y')

See examples a way down the page at: http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/load-data.html (Not sure why this page seems to differ from the main documentation in that it actually contains an example of SET usage.)

A slightly more lengthy process that gives you a bit of testing flexibility:

• Use a VARCHAR(64) column callsed eg. mydate_text to hold the unformatted date you import.
• Load up/import the table putting the date into this plain text field

• Run a query like

  UPDATE mytable SET mydate = STR_TO_DATE(mydate_text, '%Y-%b-%d')

• If it all looks OK, drop your mydate_text column

• If it's not OK, simply try again with a new format.

The advantage to this technique is it lets you play around with the formats without having to re-import the table using the somewhat fussy MySQL LOAD DATA syntax, especially with length table columns. If you know exactly what you're doing, the answer above is best. If you're not an expert in MySQL, this technique can be helpful till you get your formats exactly right.