mysql - 复杂上下文中的日期和日期时间问题

Question

将一个大的 csv 导入表后，我遇到了一个主要问题:csv 中日期的格式是 DD MM YYYY，当我尝试将其导入日期/日期时间时，它不起作用。好吧，我用 CHAR 类型而不是 DATE/DATETIME 来完成它，到目前为止它是有效的。 (我的示例:VisitTime CHAR(10) )

BUT here is the problem, with the data inside the CHAR I can't concatenate to make a datetime column from 2 columns ( my example is: VisitTime CHAR(10) which was supposed to be DATE but isn't working since it's wrong format DD MM YYYY and HourTime which is TIME. Bottom line: i need a column in another table VisitTimeandHour DATETIME which results from a concatenation of VisitTIME CHAR(10) and a HourTime TIME. A solution was maybe making all the columns that i talked about VARCHAR and it was i think no problem, but i wanna stick with date and datetime :) Waiting for solutions, thanks in advance.

如果我错了，请纠正我，但如果我尝试导入到变量中，然后转换它就可以了？顺便说一句，需要从 csv 转换的日期示例是:22/04/2005 )代码:

SET @var1 = NULL; load data infile 'xxxx/xxx/xxx/xxx/xxx' into table vizite_intermtest character set utf8 fields terminated by ',' lines terminated by '\n' ignore 1 lines (@var1,OraIntrare,NumePacient,PrenumePacient,NumeMedic,PrenumeMedic,Cabinet) SET VisitTime = STR_TO_DATE(@var1, '%d/%m/%y');

如果我尝试这样做，它会显示:截断的错误值 22/04/2005(我想导入的 VisitTime 列是 CHAR 类型)

LE: It works to import into a variable and to convert it but altough i put the / / / between them it still gives me the time format with - - - ... any ideas?

Answer 1

您应该坚持使用 DATE/DATETIME 字段，但告诉 MySQL 如何解释传入的数据。我不确定您如何导入数据，但将字符串解释为日期的函数是STR_TO_DATE:

STR_TO_DATE( '01 02 2003', '%d %m %Y' )

有关详细信息，请参阅以下内容: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date