如何在导入 CSV 时使用 MySQL 的 LOAD DATA LOCAL INFILE 将字符串日期更改为 MySQL 日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2238611/
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
How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE
提问by Camsoft
I'm using MySQL's LOAD DATA LOCAL INFILE SQL statement to load data from a CSV file into an existing database table.
我正在使用 MySQL 的 LOAD DATA LOCAL INFILE SQL 语句将数据从 CSV 文件加载到现有数据库表中。
Here is an example SQL statement:
下面是一个示例 SQL 语句：
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:
CSV 中映射到 dateOfBirth 字段的第三列当前具有以下格式的日期：
14-Feb-10
How can I modify the above SQL statement to format the date into MySQL's date format i.e. 2010-02-14?
如何修改上述 SQL 语句以将日期格式化为 MySQL 的日期格式，即2010-02-14？
I know how to convert a string date when using normal INSERT syntax using:
我知道如何在使用正常的 INSERT 语法时转换字符串日期：
STR_TO_DATE('14-Feb-10', '%d-%b-%y')
STR_TO_DATE('14-Feb-10', '%d-%b-%y')
回答by Alison R.
You need to use the SETclause, 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 SETstatement. (Note, I haven't got MySQL in front of me to test this on.)
您需要使用该SET子句以及一个变量来引用该列行的内容。在列列表中，将日期列分配给变量名称。然后您可以在您的SET语句中使用它。（注意，我面前没有 MySQL 来测试这个。）
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 SETusage.)
在页面下方查看示例：http: //mysql2.mirrors-r-us.net/doc/refman/5.1/en/load-data.html（不确定为什么此页面似乎与它实际上包含一个SET使用示例。）
回答by Pete855217
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.
- 使用名为例如的 VARCHAR(64) 列。mydate_text 保存您导入的未格式化日期。
- 加载/导入将日期放入此纯文本字段的表
运行一个查询，如
更新 mytable SET mydate = STR_TO_DATE(mydate_text, '%Y-%b-%d')
如果一切正常，请删除 mydate_text 列
- 如果不正常，只需使用新格式再试一次。
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.
这种技术的优点是它可以让您使用格式而不必使用有点繁琐的 MySQL LOAD DATA 语法重新导入表，尤其是长度表列。如果您确切地知道自己在做什么，那么上面的答案是最好的。如果您不是 MySQL 的专家，这项技术会很有帮助，直到您完全正确地获得格式。
