日期列（数据类型

日期列（数据类型-timestamp）未从csv正确导入MySql(Date columns (datatype -timestamp) not imported correctly from csv into MySql)

我正在尝试使用LOAD DATA LOCAL INFILE将数据从csv文件导入MySQL。 除了具有时间戳作为其数据类型的日期列之外的所有列都将正确导入。 我收到错误1265：数据被截断为日期列，它为所有值插入00:00:00。这已被问过，但我没有找到一个完美的解决方案。 我也尝试过为这类问题发布的各种解决方案，但没有一个对我有用。

表创建语句：

CREATE TABLE MySchema.response ( `id` int, `version` int, `name` varchar(500), `date_created` timestamp, `last_updated` timestamp, `count` int, );

将数据加载到表中：

LOAD DATA LOCAL INFILE 'C:/response.csv' INTO TABLE MySchema.response FIELDS TERMINATED BY ',' optionally ENCLOSED by '"' ignore 1 lines

CSV文件中的示例数据

id版本名称date_created last_updated count 1,0，xyz，5/3/2013 1：18,5 / 3/2013：1：1,8 2,0，abc，5/3/2013 1：18,5 / 3/2013：1：1,8，1

I am trying to import data from a csv file into MySQL using LOAD DATA LOCAL INFILE. All columns except the date columns which have timestamp as their datatype are imported correctly. I am getting the error 1265: data truncated for date column and it inserts 0000-00-00 00:00:00 for all values.This has been asked before but I did not find a perfect solution for this. I have also tried various solutions posted for this type of question but none have worked for me.

table create statement :

CREATE TABLE MySchema.response ( `id` int, `version` int, `name` varchar(500), `date_created` timestamp, `last_updated` timestamp, `count` int, );

loading data into table:

LOAD DATA LOCAL INFILE 'C:/response.csv' INTO TABLE MySchema.response FIELDS TERMINATED BY ',' optionally ENCLOSED by '"' ignore 1 lines

Sample Data in CSV file

id version name date_created last_updated count 1, 0, xyz, 5/3/2013 1:18, 5/3/2013 1:18, 2 2, 0, abc, 5/3/2013 1:18, 5/3/2013 1:18, 1

最满意答案

样本中的日期列不是MySQL的默认格式，因此不会标识为日期。 您需要尝试类似以下内容，以便说明日期应如何解释：

LOAD DATA LOCAL INFILE 'C:/response.csv' INTO TABLE MySchema.response FIELDS TERMINATED BY ',' optionally ENCLOSED by '"' IGNORE 1 lines (id, version, name, @date_created, @last_updated, count) SET date_created = STR_TO_DATE(@date_created, '%d/%c/%Y %k:%i'), last_updated = STR_TO_DATE(@last_updated, '%d/%c/%Y %k:%i');

检查适合您情况的说明符的MySQL 日期格式文档 （可能是我在上面的示例中添加的那些）。

Date columns in your sample are not in MySQL's default format and thus are not identified as dates. You need to try something like the following, in order to state how the dates should be interpreted:

LOAD DATA LOCAL INFILE 'C:/response.csv' INTO TABLE MySchema.response FIELDS TERMINATED BY ',' optionally ENCLOSED by '"' IGNORE 1 lines (id, version, name, @date_created, @last_updated, count) SET date_created = STR_TO_DATE(@date_created, '%d/%c/%Y %k:%i'), last_updated = STR_TO_DATE(@last_updated, '%d/%c/%Y %k:%i');

Check MySQL date format documentation for specifiers that suit your case (probably the ones I added in the sample above).