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

问题描述

我有一个要导入的 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')

这篇关于LOAD DATA INFILE 轻松地将 YYYYMMDD 转换为 YYYY-MM-DD?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！