MySQL CSV导入 - 如果时间戳有毫秒，输入的日期为0000-00-00 00:00:00？

问题描述

我目前有大量CSV要导入到MySQL数据库。文件包含每个记录的时间戳，格式为（例如）：

  2011-10-13 09:36 ：02.297000000 
  

我知道MySQL错误＃8523，它表示在datetime字段中存储毫秒不支持。尽管如此，我希望datetime字段在秒后截断记录，而不是输入为空白。

我已经将问题缩小到毫秒而不是csv等的格式化），因为

  2011-10-13 09:36:02 
 
 
 
 
任何人都可以建议一种方式我可以得到这个数据导入没有零？我有太多的CSV，每个手动进入和调整时间戳的长度/格式。
 
 
 
我应该指出，虽然毫秒将是一个很好的，他们对我的申请不是必要的，所以我会很高兴的解决方案，让我可以很容易地截断数字并导入他们。
 
 
 
谢谢！ 
 
 
编辑：为了说明，我使用以下命令导入CSV：
  mysqlimport --fields-enclosed-by =--fields-terminated-by =，--lines-terminated-by =\\\
--columns = id，@ x，Pair， - 本地获取[文件] .csv 
  
这是非常快速的导入记录 - 我有大约50m 
解决方案
我不知道如何导入CSV文件但我的方法是写一个脚本（php / perl）来读取每个文件，向上取整或将时间戳修剪为秒，并在DATABASE上执行INSERT语句。
 
 
 
像
 <？php 
 $ file = fopen（your.csv，r ）; 
 mysql_connect（$ ip，$ user，$ pass）; 
 
 while（！feof（$ file））
 {
 $ line = explode（'，'，fgets 
 mysql_query（INSERT INTO TABLE1（ID，DATE）values（。$ line [0]。，.substr（$ line [1]，0,19）。 
} 
 fclose（$ file）; 
？> 
  
从命令行执行此操作，它应该执行作业
 
I currently have a large number of CSVs to import to a MySQL database. The files contain timestamps for each record, which are in the format (for example): 
2011-10-13 09:36:02.297000000
I am aware of the MySQL bug #8523, which indicates that storing milliseconds in a datetime field is not supported. Despite this, I would have expected the datetime field to truncate the record after the seconds, instead of being entered as blank.
I have narrowed down the problem to the milliseconds (as opposed to the formatting of the csv etc.), since
2011-10-13 09:36:02
imports correctly.
Could anyone suggest a way that I can get this data imported without zeros? I have too many CSVs to go into each manually and adjust the length/formatting of the timestamps.
I should point out that while milliseconds would be a nice-to-have, they are not necessary to my application, so I would be happy with a solution that allows me to easily truncate the numbers and import them.
Thanks!
EDIT: To clarify, I am importing the CSVs using the following command:
mysqlimport --fields-enclosed-by="" --fields-terminated-by="," --lines-terminated-by="\n" --columns=id,@x,Pair,Time -p --local gain [file].csv
This is very fast for importing the records - I have around 50m to import, so reading each line in is not a great option.
 解决方案 
I don't know how are you importing the CSVs but the way I would do is to write a script (php/perl) to read each file, round up or trim the time stamp to seconds and execute INSERT statements on the DATABASE.
Something like
<?php
$file=fopen("your.csv","r");
mysql_connect ($ip, $user, $pass);

while(!feof($file))
{
   $line = explode(',',fgets($file));
   mysql_query("INSERT INTO TABLE1 (ID, DATE) values (".$line[0].", ".substr($line[1],0,19).")");
}
fclose($file);
?>
Execute this from the command line and it should do the job
                        
这篇关于MySQL CSV导入 - 如果时间戳有毫秒，输入的日期为0000-00-00 00:00:00？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！