mysql读取csv文件日期全为零,MySQL CSV导入 - 如果时间戳有毫秒，输入的日期为0000-00-00 00:00:00？...

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

$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