使用不同的日期格式将 CSV 导入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8163079/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Importing a CSV to MySQL with different date format
提问by enchance
I'm doing my first CSV import into MySQL and noticed that the date in the CSV has the format 31-Jan-2011. How can I convert this to 2011-01-31so I can place it in the DATE datatype? The first thing which came to mind is let PHP do the conversion then insert it into a 2nd table but I'm guessing that's...not right.
我正在将我的第一个 CSV 导入 MySQL 并注意到 CSV 中的日期格式为31-Jan-2011. 如何将其转换为2011-01-31这样我可以将它放在 DATE 数据类型中？想到的第一件事是让 PHP 进行转换，然后将其插入到第二个表中，但我猜这...不对。
回答by Devart
You can replace format during importing data from the CSV file, for example -
您可以在从 CSV 文件导入数据期间替换格式，例如 -
LOAD DATA INFILE 'file_name.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
(id, column2, column3, @date_time_variable) -- read one of the field to variable
SET date_time_column = STR_TO_DATE(@date_time_variable, '%d-%b-%Y'); -- format this date-time variable
It will format the string like '31-Jan-2011' to a correct DATETIME data type.
它将像 '31-Jan-2011' 这样的字符串格式化为正确的 DATETIME 数据类型。
More information here - LOAD DATA INFILE Syntax.
此处的更多信息 - LOAD DATA INFILE 语法。
回答by Rush
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d');
+------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d') |
+------------------------------------------------+
| 2009-10-04 |
+------------------------------------------------+
1 row in set (0.00 sec)
回答by Fagan Walker
If the file isn't too big, load the CSV into Excel and use the formatting commands there to change the date representation.
如果文件不是太大，将 CSV 加载到 Excel 中并使用那里的格式化命令来更改日期表示。
