mysql - 使用LOAD DATA时如何在MySQL中加载日期数据

Question

在 MySQL 中，日期列的默认日期格式是 YYYY-MM-DD HH:MM:SS。

我尝试从中加载的数据文件有一个日期字段，该字段的日期格式为 DD-MON-YY HH:MM:SS。当我使用 LOAD DATA 命令加载此文件时，数据库变得困惑，只是将所有日期条目设为 0000-00-00 00:00:00 或 NULL

这是我使用 STR_TO_DATE 选项进行的测试，但它不起作用。

测试文件 (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 '\r\n'
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

STR_TO_DATE() 的格式字符串无效。示例数据中的小时采用 24 小时格式(%H 或 %k)而不是 12 小时格式(%h)。您可以看到所有可能的日期格式说明符 here .

改变

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

到

%d-%b-%y %H:%i:%s
         ^^

你的陈述可能是这样的

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');

加载样本数据后

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)