我正在尝试导入csv文件到MySQL数据库一切工作正常,除了datetime不加载。
我的csv文件日期时间格式是'19/05/2014 14:44:44'
和MySQL日期格式是'2014-02-18 09:44:30'
,而试图导入它来作为'0000-00-00 00:00:00'
我尝试了不同的方式,但我不明白我在哪里出错。
LOAD DATA LOCAL INFILE 'I:/finaltest12.csv' INTO TABLE tickets FIELDS terminated by ',' ENCLOSED BY '"' lines terminated by 'n' IGNORE 1 LINES (SiteId,@var1,Serial,DeviceId,AgentAID,VehicleRegistration,CarPark,SpaceNumber,GpsAddress,VehicleType,VehicleMake,VehicleModel,VehicleColour,IssueReasonCode,IssueReason,NoticeLocation,Points,Notes) SET DateTime = STR_To_DATE(@var1,'%yyyy-%MM-%dd %H:%i:%s')";
MySqlCommand cmd1 = new MySqlCommand(strLoadData, conn);
当我运行这个时,我得到了上面的错误致命错误遇到
您可以在excel中打开csv文件,然后在mysql格式中更改日期时间,然后再次保存为csv格式并上传。
你可以在你的脚本中做如下:
LOAD DATA LOCAL INFILE 'I:/finaltest12.csv' INTO TABLE tickets FIELDS terminated by ',' ENCLOSED BY '"' lines terminated by 'n' IGNORE 1 LINES (SiteId,@var1,Serial,DeviceId,AgentAID,VehicleRegistration,CarPark,SpaceNumber,GpsAddress,VehicleType,VehicleMake,VehicleModel,VehicleColour,IssueReasonCode,IssueReason,NoticeLocation,Points,Notes) SET DateTime = STR_To_DATE(@var1,'%d-%m-%Y %H:%i:%s');