MySQL 如何在使用MySQL的LOAD DATA LOCAL INFILE导入CSV时将字符串日期更改为MySQL日期格式

对于MySQL用户来说，使用LOAD DATA LOCAL INFILE导入CSV是一种快速且方便的方法。但是，当CSV文件中包含日期字符串时，您可能会遇到日期格式不正确的问题，导致数据导入失败或导入到数据库中的数据与实际日期不匹配。本篇文章将详细介绍如何在使用MySQL的LOAD DATA LOCAL INFILE导入CSV时将字符串日期更改为MySQL日期格式。

阅读更多：MySQL 教程

1. CSV文件中日期格式的问题

常见的日期字符串格式有多种，如“YYYY/MM/DD”、“YYYY-MM-DD”、“MM/DD/YYYY”等。然而，MySQL要求日期格式必须为“YYYY-MM-DD”，否则会出现数据导入失败或数据与实际日期不匹配的情况。因此，如果CSV文件中的日期格式与MySQL要求的格式不一致，就需要将其转换为MySQL日期格式，才能保证数据正确导入到数据库中。

例如，下面是一个包含不同日期字符串格式的CSV文件示例：

1,2021/05/01,2021-06-01,05/07/2021
2,2022/01/07,2022-02-07,01/13/2022
3,2023/03/15,2023-04-15,03/22/2023

其中，“2021/05/01”、“2022/01/07”和“2023/03/15”是“YYYY/MM/DD”格式的日期字符串，“2021-06-01”、“2022-02-07”和“2023-04-15”是“YYYY-MM-DD”格式的日期字符串，“05/07/2021”、“01/13/2022”和“03/22/2023”是“MM/DD/YYYY”格式的日期字符串。显然，如果直接将该CSV文件导入到MySQL数据库中，将会出现日期格式不匹配的问题。

2. MySQL转换日期格式的方法

要解决CSV文件中日期格式的问题，需要使用MySQL转换日期格式的方法。MySQl提供了许多内置函数来转换不同格式的日期字符串为MySQL日期格式。这里介绍三种常用的MySQL函数：STR_TO_DATE、DATE_FORMAT和FROM_UNIXTIME。

2.1 STR_TO_DATE函数

STR_TO_DATE函数可以将字符串转换为日期时间值，它需要两个参数。第一个参数是要转换的字符串，第二个参数是表示字符串的格式。例如，将“2021/05/01”转换为MySQL日期格式的函数语法如下：

STR_TO_DATE('2021/05/01', '%Y/%m/%d')

其中，’%Y/%m/%d’表示要转换的字符串的格式为“YYYY/MM/DD”。

2.2 DATE_FORMAT函数

DATE_FORMAT函数可以将日期时间值转换为指定格式的字符串，它需要两个参数。第一个参数是要转换的日期时间值，第二个参数是表示转换后的字符串格式。例如，将MySQL日期时间值“2021-05-01”格式化为“YYYY/MM/DD”的字符串格式的函数语法如下：

DATE_FORMAT('2021-05-01', '%Y/%m/%d')

其中，’%Y/%m/%d’表示转换后的字符串格式为“YYYY/MM/DD”。

2.3 FROM_UNIXTIME函数

FROM_UNIXTIME函数可以将UNIX时间戳转换为日期时间值或指定格式的字符串。UNIX时间戳是从1970年1月1日凌晨0点0分0秒（UTC）到指定时间所经过的秒数，它是一种跨平台的时间表示方式。我们可以将UNIX时间戳转换为MySQL日期时间值，然后再使用DATE_FORMAT函数将其格式化为指定格式的字符串。例如，将UNIX时间戳“1559333104”转换为“YYYY/MM/DD”格式的字符串的函数语法如下：

DATE_FORMAT(FROM_UNIXTIME(1559333104),'%Y/%m/%d')

其中，1559333104是一个UNIX时间戳，FROM_UNIXTIME函数将其转换为MySQL日期时间值，“%Y/%m/%d”是指定的字符串格式，DATE_FORMAT函数将其转换为“YYYY/MM/DD”格式的字符串。

3. 在使用LOAD DATA LOCAL INFILE导入CSV时转换日期格式

在使用LOAD DATA LOCAL INFILE导入CSV时，您可以使用MySQL转换日期格式的方法，将CSV文件中的日期字符串转换为MySQL日期格式。以下是具体的步骤：

3.1 创建表格

首先，需要创建一个用于存储CSV文件中数据的表格。例如，创建一个名为“example”的表格，具有三列：id、date1和date2，其中date1和date2分别存储字符串日期和MySQL日期格式。

CREATE TABLE example (
  id INT NOT NULL,
  date1 VARCHAR(50),
  date2 DATE
);

3.2 导入CSV文件

使用LOAD DATA LOCAL INFILE命令将CSV文件导入到表格中。在LOAD DATA LOCAL INFILE命令中，需要使用SET语句为date2赋值，将date1中的字符串日期转换为MySQL日期格式。例如，使用以下命令将上面提到的CSV文件导入到“example”表格中：

LOAD DATA LOCAL INFILE 'example.csv' 
INTO TABLE example 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS 
(id, date1) 
SET date2 = STR_TO_DATE(date1, '%Y/%m/%d');

其中，“FIELDS TERMINATED BY ‘,’”表示CSV文件中字段之间使用逗号分隔，“LINES TERMINATED BY ‘\n’”表示行之间使用换行符分隔，“IGNORE 1 ROWS”表示忽略CSV文件中的第一行，因为它包含的是列名而非数据。

在“(id, date1)”中，id和date1是CSV文件中的两列，它们分别对应于example表格中的id和date1列。

在“SET date2 = STR_TO_DATE(date1, ‘%Y/%m/%d’)”中，STR_TO_DATE函数将date1中的字符串日期转换为MySQL日期格式，并将其赋值给date2列。

3.3 查看表格内容

使用SELECT语句查询example表格的内容，可以验证导入的数据是否正确，以及date1中的字符串日期是否已经转换为了MySQL日期格式。

SELECT * FROM example;

查询结果如下：

id  date1       date2
1   2021/05/01  2021-05-01
2   2022/01/07  2022-01-07
3   2023/03/15  2023-03-15

从结果可以看出，date1中的字符串日期已经成功转换为了MySQL日期格式，并存储在date2列中。

总结

通过使用MySQL转换日期格式的方法，您可以将CSV文件中的字符串日期转换为MySQL日期格式，并成功导入到数据库中。在使用LOAD DATA LOCAL INFILE导入CSV时，可以将转换日期格式的代码与LOAD DATA LOCAL INFILE命令结合使用，以避免日期格式不匹配的问题。此外，根据您的具体需求和数据格式，可以选择适当的MySQL日期转换函数，如STR_TO_DATE、DATE_FORMAT和FROM_UNIXTIME等。