mysql - 将数据加载到mysql中的日期时间结果为零

Question

有人可以帮我解决这个问题吗，我的结果表仅显示时间戳为零。我尝试将字段类型更改为数据时间和时间戳，但运气不佳

MYSQL

  LOAD DATA LOCAL INFILE 'vals.csv' 
  INTO TABLE vals
  FIELDS TERMINATED BY ","
  LINES TERMINATED BY "\n"
  IGNORE 1 LINES
  (@varTimeSt,NOMINAL,NAME,ID,VAL) SET DATETIME = STR_TO_DATE(@varTimeSt,'%d/%m/%Y %h:%i:%s');

CSV 文件

DATETIME,NAME,ID,VAL
"25/08/2016 02:00:00",tom,cNum6,12
"25/08/2016 02:00:00",Charles,cNum7,10.58
"25/08/2016 02:00:00",Donal,cNum8,10.18
"25/08/2016 02:00:00",Duncan,cNum7,10.31

结果表

DATETIME,NAME,ID,VAL
0000-00-00 00:00:00,tom,cNum6,12
0000-00-00 00:00:00,Charles,cNum7,10.58
0000-00-00 00:00:00,Donal,cNum8,10.18
0000-00-00 00:00:00,Duncan,cNum7,10.31

Answer 1

你的问题是你的日期不是 native mysql DATETIME 格式(%Y-%m-%d %H:%i:%s)，这个可以表示为:

mysql> SELECT CAST('25/08/2016 02:00:00' AS DATETIME);
+-----------------------------------------+
| CAST('25/08/2016 02:00:00' AS DATETIME) |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+
1 row in set, 1 warning (0,00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '25/08/2016 02:00:00' |
+---------+------+-------------------------------------------------+
1 row in set (0,00 sec)

解决方案是将它们转换为 MySQL 格式，这可以通过操作磁盘或源上的 DATETIME 列来完成。这可能比我在下面概述的“自给自足”解决方案更困难。

一旦数据进入数据库，就存在字符串到日期(STR_TO_DATE)函数来解决此类问题，这里是您的格式的演示。否则请参阅手册 here 。 (请参阅 DATE_FORMAT 部分，获取包含可用于不同日期格式的代码的表格。):

mysql> SELECT CAST(STR_TO_DATE('25/08/2016 02:00:00', '%d/%m/%Y %H:%i:%s') AS DATETIME);
+---------------------------------------------------------------------------+
| CAST(STR_TO_DATE('25/08/2016 02:00:00', '%d/%m/%Y %H:%i:%s') AS DATETIME) |
+---------------------------------------------------------------------------+
| 2016-08-25 02:00:00                                                       |
+---------------------------------------------------------------------------+
1 row in set (0,00 sec)

就我个人而言，在这种情况下，我会将数据加载到一个临时表中，该表接受日期字段作为字符串，然后使用 INSERT 和 STR_TO_DATE 函数来获取数据进入最终表。最终的解决方案(未经测试，仅供引用)如下所示:

CREATE TEMPORARY TABLE valsLoad (DATETIME TEXT,NAME TEXT,ID TEXT,VAL INT);

LOAD DATA LOCAL INFILE 'vals.csv' 
INTO TABLE valsLoad
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
IGNORE 1 LINES
(@varTimeSt,NOMINAL,NAME,ID,VAL) SET DATETIME = STR_TO_DATE(@varTimeSt,'%d/%m/%Y %h:%i:%s');

INSERT INTO vals
SELECT STR_TO_DATE(DATETIME, '%d/%m/%Y %H:%i:%m'), NAME, ID, VAL FROM valsLoad;

DROP TEMPORARY TABLE valsLoad;

如果您有任何疑问，请告诉我。

问候，

詹姆斯