How to load date data using LOAD DATA in MySQL

Question

The default date format for a date column in MySQL is YYYY-MM-DD HH:MM:SS.

The data file I'm trying to load has a date field in the format of DD-MON-YY HH:MM:SS. When I load this file using the LOAD DATA command, the database gets confused and sets all date entries to 0000-00-00 00:00:00 or NULL .

This is my test using the STR_TO_DATE option but it doesn't work.

测试文件（test_temp.csv）

c1, c2
07-JUN-12 22:50:19, "abc"
07-JUN-13 22:50:19, "bcd"

测试表（temp_test）

describe temp_test;
 ------- ------------- ------ ----- --------- ------- 
| Field | Type        | Null | Key | Default | Extra |
 ------- ------------- ------ ----- --------- ------- 
| c1    | datetime    | YES  |     | NULL    |       |
| c2    | varchar(10) | YES  |     | NULL    |       |
 ------- ------------- ------ ----- --------- ------- 

数据加载命令：

load data
infile '/var/lib/mysql/DataSet-1/temp_test.csv'
ignore
into table temp_test
fields terminated by ','
enclosed by '"'
lines terminated by 'rn'
ignore 1 lines
(@var_c1,c2)
set c1 = STR_TO_DATE(@var_c1,'%d-%b-%y %h:%i:%s');

输出

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  |
 ------ ------ 

问题出在：

1. 输入日期列（应该是07-JUN-12还是07-Jun-12）或
2. 我的格式字符串（%d-%b-%y）或
3. 其他原因？

Answer 1

Your STR_TO_DATE() format string is invalid. The hours in your sample data use the 24-hour clock (%H or %k), not the 12-hour clock (%h). You can see all possible date format specifiers here.

Will

%d-%b-%y %h:%i:%s

change into

%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 using 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)