我一直试图将文件拉入表中,唯一遇到的问题是导入日期(当前为'MM/DD/YY'
格式)。不管我尝试什么,它们最终看起来都像'0008-12-14'
。该日期是.csv文件中的8/12/14
。我试过了
SET ShipDate = STR_TO_DATE(@ShipDate,'%Y%m%d')
但随后将它们导入为
NULL
。表字段设置为DATE
。这是代码:$query = <<<eof
LOAD DATA LOCAL INFILE 'file.csv'
INTO TABLE HS_import
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(AmountPaid,
ShipDate)
SET ShipDate = STR_TO_DATE(@ShipDate,'%Y%m%d')
eof;
最佳答案
您STR_TO_DATE的格式错误。它应该指定INPUT格式而不是OUTPUT格式:
SET ShipDate = STR_TO_DATE(@ShipDate,'%d%m%y')
所以整个事情看起来像
LOAD DATA LOCAL INFILE 'file.csv'
INTO TABLE HS_import
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(AmountPaid, @ShipDate)
SET ShipDate = STR_TO_DATE(@ShipDate,'%d%m%y')
关于php - 从CSV加载数据本地INFILE日期格式,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29042114/