LOAD DATA INFILE 轻松地将 YYYYMMDD 转换为 YYYY-MM-DD?

2021-11-20 00:00:00 mysql

我有一个要导入的 INFILE，但日期格式为:

Hi I have an INFILE I want to import, but the dates are of the form :

AADR,20120403,31.43,31.43,31.4,31.4,1100
AAU,20120403,2.64,2.65,2.56,2.65,85700
AAVX,20120403,162.49,162.49,154.24,156.65,2200

是否有任何简单的方法可以将日期转换为2012-04-03"，而无需先使用 perl 脚本打开它，转换日期，然后再次将文件写回?

Is there any easy way to convert the dates to be '2012-04-03' without having to do something like open it first with a perl script, convert the dates, and then writing the file back out again?

TIA !!

推荐答案

这一步加载和转换，不需要另一个表.有关详细信息，请参阅手册.

This loads and converts in one step, no need for another table. For more information see the manual.

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
FIELDS TERMINATED BY ',' 
(column1, @var1, column3, ...)
SET column2 = STR_TO_DATE(@var1,'%Y%m%d')

相关文章