MySQL #1411 – 在 INSERT INTO…SELECT 中 str_to_date 函数的日期格式不正确|极客教程

MySQL #1411 – 在 INSERT INTO…SELECT 中 str_to_date 函数的日期格式不正确

在数据库操作中，经常需要将文本类型的日期转换为日期类型。MySQL 中提供了 str_to_date 函数来实现这一功能。然而，当使用 INSERT INTO…SELECT 语句时，在对日期进行转换时可能会出现错误。

阅读更多：MySQL 教程

描述错误

在使用下列 INSERT INTO…SELECT 语句时：

INSERT INTO mytable (date_column)
SELECT STR_TO_DATE(date_column_text, '%d/%m/%Y') FROM mysource

当我们的日期文本是 “01/02/2022″，日期格式设置为 “%d/%m/%Y” 时，将会得到以下错误信息：

Error Code: 1411. Incorrect datetime value: '01/02/2022' for function str_to_date at row 1

错误代码 #1411 意味着 str_to_date 函数无法将文本转换为日期，那么我们该如何解决这个错误呢？

分析错误原因

在分析具体原因之前，需要先了解 DATE 和 DATETIME 数据类型在 MySQL 中的区别。

DATE：表示日期，格式为 “YYYY-MM-DD”，例如 “2022-02-01″。
DATETIME：表示日期和时间，格式为 “YYYY-MM-DD HH:MM:SS”，例如 “2022-02-01 15:23:45″。

STR_TO_DATE 函数的作用是将文本转换为日期或时间类型，需要指定输入文本的日期格式，并返回 DATE 或 DATETIME 类型的结果。在上述例子中，我们指定了日期格式为 “%d/%m/%Y”，即 “日/月/年” 的格式。但是，MySQL 只接受 “年-月-日” 的日期格式，所以导致了错误的发生。

解决错误方法

为了解决这个错误，我们需要确保将日期转换为正确的日期格式。

在使用 STR_TO_DATE 函数时，需要指定 DATE 或 DATETIME 的目标格式，因此我们可以使用 DATE_FORMAT 函数将日期转换为 MySQL 支持的标准日期格式，例如 “YYYY-MM-DD”：

INSERT INTO mytable (date_column)
SELECT STR_TO_DATE(DATE_FORMAT(date_column_text, '%Y-%m-%d'), '%Y-%m-%d') FROM mysource

这样，我们就可以将日期正确转换为 DATE 类型。

如果需要转换为 DATETIME 类型，则需要指定正确的时间格式。例如，将时间文本 “15:23:45” 转换为 DATETIME 类型，格式为 “%Y-%m-%d %H:%i:%s”：

INSERT INTO mytable (datetime_column)
SELECT STR_TO_DATE(CONCAT(date_column_text, ' 15:23:45'), '%Y-%m-%d %H:%i:%s') FROM mysource

此处，我们使用了 CONCAT 函数，将日期和时间字符串组合起来。这里，时间字符串是固定的，因此我们可以指定一个固定的时间来表示。

总结

在本文中，我们介绍了 MySQL 中 str_to_date 函数在 INSERT INTO…SELECT 中出现错误的问题。具体原因是由于日期格式不正确导致的。为了解决这个问题，我们需要将日期转换为 MySQL 支持的标准日期格式，并根据需要指定相应的时间格式。这样，我们就可以在数据库操作中顺利地对日期进行处理。