使用 LOAD DATA 时如何在 MySQL 中加载日期数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18927249/
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 load date data in MySQL when using LOAD DATA
提问by smarisetti
The default date format of a date column is YYYY-MM-DD HH:MM:SSin MySQL.
日期列的默认日期格式YYYY-MM-DD HH:MM:SS在 MySQL 中。
The data file that I am trying load from has a date field that has the date in DD-MON-YY HH:MM:SSformat. When I load this file using LOAD DATAcommand, the database gets confused and just makes all date entries to 0000-00-00 00:00:00or NULL
我尝试从中加载的数据文件有一个日期字段，其DD-MON-YY HH:MM:SS格式为日期。当我使用LOAD DATA命令加载此文件时，数据库会感到困惑，只会将所有日期条目设置为0000-00-00 00:00:00或 NULL
Here is the test I did using STR_TO_DATEoption and it doesn't work.
这是我使用STR_TO_DATE选项所做的测试，但它不起作用。
Test infile (test_temp.csv)
测试文件 (test_temp.csv)
c1, c2
07-JUN-12 22:50:19, "abc"
07-JUN-13 22:50:19, "bcd"
Test table (temp_test)
测试表（temp_test）
describe temp_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | datetime | YES | | NULL | |
| c2 | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
Data Load command:
数据加载命令：
load data
infile '/var/lib/mysql/DataSet-1/temp_test.csv'
ignore
into table temp_test
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
ignore 1 lines
(@var_c1,c2)
set c1 = STR_TO_DATE(@var_c1,'%d-%b-%y %h:%i:%s');
Output
输出
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
MySQL> show warnings;
+-------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------+
| Error | 1411 | Incorrect datetime value: '07-JUN-12 22:50:19' for function str_to_date |
| Error | 1411 | Incorrect datetime value: '07-JUN-13 22:50:19' for function str_to_date |
+-------+------+-------------------------------------------------------------------------+
MySQL> select * from temp_test;
+------+------+
| c1 | c2 |
+------+------+
| NULL | abc |
| NULL | bcd |
+------+------+
Is the problem with
问题是
- Input date column (Should it be
07-JUN-12or07-Jun-12) or - With my format string (
%d-%b-%y) or - Something else?
- 输入日期列（应该是
07-JUN-12还是07-Jun-12）或 - 使用我的格式字符串 (
%d-%b-%y) 或 - 还有什么？
回答by peterm
Your format string for STR_TO_DATE()is invalid. Hours in your sample data have 24-hour format (%Hor %k) instead of 12-hour (%h). You can see all possible date format specifiers here.
您的格式字符串STR_TO_DATE()无效。示例数据中的小时采用 24 小时制 (%H或%k) 而不是 12 小时制 ( %h)。您可以在此处查看所有可能的日期格式说明符。
Change
改变
%d-%b-%y %h:%i:%s
to
到
%d-%b-%y %H:%i:%s
^^
Your statement might look like this
您的声明可能如下所示
LOAD DATA INFILE '/path/to/temp_test.csv'
IGNORE INTO TABLE temp_test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' -- or '\n'
IGNORE 1 LINES
(@c1, c2)
SET c1 = STR_TO_DATE(@c1,'%d-%b-%y %H:%i:%s');
After loading with your sample data
加载示例数据后
mysql> select * from temp_test; +---------------------+------+ | c1 | c2 | +---------------------+------+ | 2012-06-07 22:50:19 | abc | | 2013-06-07 22:50:19 | bcd | +---------------------+------+ 2 rows in set (0.00 sec)
回答by ChuckCottrill
Since I had a similar problem, but the incoming date format was different, here is what you would do should your incoming date format be the following,
由于我遇到了类似的问题，但是传入的日期格式不同，如果传入的日期格式如下，您将执行以下操作，
Sample input date format,
样本输入日期格式，
Tuesday, May 24, 2016
Wednesday, May 25, 2016
You would need to use a different date format,
您需要使用不同的日期格式，
%W, %b %d, %Y
Here is a link to the mysql documentationand a list of date formats, but briefly,
Date part,
日期部分，
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%c Month, numeric (0..12)
%m Month, numeric (00..12)
%M Month name (January..December)
%b Abbreviated month name (Jan..Dec)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, ?-)
Weekday,
平日，
%W Weekday name (Sunday..Saturday)
%a Abbreviated weekday name (Sun..Sat)
%w Day of the week (0=Sunday..6=Saturday)
%j Day of year (001..366)
Time
时间
%T Time, 24-hour (hh:mm:ss)
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%k Hour (0..23)
%l Hour (1..12)
%i Minutes, numeric (00..59)
%S Seconds (00..59)
%s Seconds (00..59)
%f Microseconds (000000..999999)
%p AM or PM
There are other patterns which are not listed above.
还有上面没有列出的其他模式。
LOAD DATA INFILE '/path/to/temp_test.csv'
IGNORE INTO TABLE temp_test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' #use '\n' for linux
IGNORE 1 LINES
(@var_col1, col2)
SET col1 = STR_TO_DATE(@var_col1,'%d-%b-%y %H:%i:%s');
回答by rahul mandloi
LOAD DATA INFILE '/path/to/temp_test.csv'
IGNORE INTO TABLE temp_test
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' -- or '\n'
IGNORE 1 LINES
(@c1, c2)
SET c1 = IF(CHAR_LENGTH((@c1)) = 0, NULL, (CONCAT(SUBSTRING(@c1e,7,4),SUBSTRING(@c1,3,4),SUBSTRING(@c1,1,2),SUBSTRING(@c1,11,9))));
