How Can I Efficiently Convert YYYYMMDD Dates to YYYY-MM-DD Using LOAD DATA INFILE in MySQL?

Easily Convert YYYYMMDD to YYYY-MM-DD with LOAD DATA INFILE

Problem:

Importing data with dates in YYYYMMDD format into MySQL can be challenging. Users seek an efficient method to convert the dates to YYYY-MM-DD without external processing.

Solution:

LOAD DATA INFILE provides an elegant solution to achieve this conversion in one step. Consider the following INFILE example:

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

To load and convert the dates simultaneously, use the following syntax:

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

In this syntax, @var1 represents the YYYYMMDD date format, and STR_TO_DATE converts it to YYYY-MM-DD. This operation is executed during the data load process, eliminating the need for external processing.

Advantages:

1. Single-step import and conversion
2. Efficient and streamlined data loading

The above is the detailed content of How Can I Efficiently Convert YYYYMMDD Dates to YYYY-MM-DD Using LOAD DATA INFILE in MySQL?. For more information, please follow other related articles on the PHP Chinese website!