CSV日期格式到MySQL日期格式 [英] CSV date format to MySQL date format
问题描述
当我尝试将CSV文件导入MySQL时，所有日期均变为0000-00-00.
When I try to import my CSV file to MySQL, all the date become 0000-00-00.
CSV日期格式为DD/MM/YYYY，类似于16/11/2016.
CSV date format is DD/MM/YYYY like this 16/11/2016.
MySQL格式为:YYYY/MM/DD.我正在使用phpmyadmin导入此.如何解决此日期转移?
MySQL format is: YYYY/MM/DD. I am using phpmyadmin to import this. How to solve this date transfer?
推荐答案
格式DD/MM/YYYY在MySQL中不是有效日期.您可以结合使用LOAD DATA和STR_TO_DATE将日期字符串解析为MySQL可以识别的实际日期:
The format DD/MM/YYYY is not a valid date in MySQL. You could use LOAD DATA along with STR_TO_DATE to parse the date strings into actual dates which MySQL can recognize:
LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE yourTable FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
(
col1, col2, @var1, col4
)
SET date_col = STR_TO_DATE(@var1, '%d/%m/%Y')
以上假设您的表格有4列，并且您从CSV文件中读取的第三列是麻烦的日期.这里的技巧是，每个日期字符串都将被动态映射为有效的日期类型，从而为您提供所需的结果.
The above assumes that your table has 4 columns, and that the third column you are reading in from your CSV file is the troublesome date. The trick here is that each date string will get mapped on the fly into a valid date type, leaving you with the result you want.
这篇关于CSV日期格式到MySQL日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
