mysql - 无法使用 LOAD DATA 将时间戳加载到 MySQL 表中

Question

输入数据如下所示:

Incident ID,CR Number,Dispatch Date / Time
201073514,16016688,04/05/2016 10:07:48 PM
201073496,16016678,04/05/2016 08:53:35 PM
...
201073481,16016656,04/05/2016 06:47:01 PM

运行加载后

LOAD DATA  INFILE 'C:\\Temp\\orders.csv' INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

我尝试了不同的 Dispatch_Date_Time 类型，例如 DATE/DATETIME/TIMESTAMP，但它总是显示为零:

mysql> select Dispatch_Date_Time from orders limit 5;
+---------------------+
| Dispatch_Date_Time  |
+---------------------+
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)

我做错了什么？

Answer 1

你可以这样使用它:

LOAD DATA  INFILE 'C:\\Temp\\orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(c1, c2, @c3)
SET c3 = STR_TO_DATE(@c3,'%d/%m/%Y %I:%i:%s %p');