MySQL高级函数之：`STR_TO_DATE()`：其在字符串转日期时的`%`格式化参数。

好的，下面是一篇关于MySQL高级函数STR_TO_DATE()及其格式化参数的讲座式技术文章，内容详细，包含代码示例，逻辑严谨，并以易于理解的语言进行表述。

MySQL高级函数 STR_TO_DATE() 详解及格式化参数精讲

大家好，今天我们来深入探讨MySQL中一个非常实用且强大的函数——STR_TO_DATE()。这个函数的主要功能是将字符串转换为日期或日期时间类型。虽然功能听起来简单，但它的灵活性和适用性非常广泛，尤其是在处理各种格式不统一的日期字符串时。STR_TO_DATE() 函数的核心在于其使用的格式化参数，这些参数决定了如何解析输入的字符串。理解并熟练运用这些参数，是掌握 STR_TO_DATE() 函数的关键。

1. STR_TO_DATE() 函数的基本语法

STR_TO_DATE() 函数的基本语法如下：

STR_TO_DATE(str, format)

其中：

• str：是要转换的字符串，包含日期或日期时间信息。
• format：是格式化字符串，用于指定 str 的日期时间格式。这个格式化字符串告诉 MySQL 如何解析 str 中的年、月、日、时、分、秒等信息。

2. 格式化参数详解

format 参数使用一系列以 % 开头的格式化字符来表示不同的日期时间部分。下面我们详细讲解各个常用的格式化参数及其含义。

格式化字符	含义	示例 (基于日期 ‘2023-10-27 14:30:45’)
%Y	四位数的年份 (例如：2023)	2023
%y	两位数的年份 (例如：23)	23
%m	两位数的月份 (01-12)	10
%c	月份，不带前导零 (1-12)	10
%d	两位数的日期 (01-31)	27
%e	日期，不带前导零 (1-31)	27
%H	24小时制的小时 (00-23)	14
%k	24小时制的小时，不带前导零 (0-23)	14
%h 或 %I	12小时制的小时 (01-12)	02
%l	12小时制的小时，不带前导零 (1-12)	2
%i	分钟 (00-59)	30
%s 或 %S	秒 (00-59)	45
%p	AM 或 PM	PM
%r	12小时制的时间，格式为 hh:mm:ss AM/PM	02:30:45 PM
%T	24小时制的时间，格式为 hh:mm:ss	14:30:45
%w	星期几 (0=Sunday, 1=Monday, …, 6=Saturday)	(根据2023-10-27实际星期几决定)
%a	星期几的缩写 (Sun, Mon, …, Sat)	(根据2023-10-27实际星期几决定)
%W	星期几的全名 (Sunday, Monday, …, Saturday)	(根据2023-10-27实际星期几决定)
%b	月份的缩写 (Jan, Feb, …, Dec)	Oct
%M	月份的全名 (January, February, …, December)	October
%j	一年中的第几天 (001-366)	(2023-10-27 是第几天)
%%	字面上的 % 字符	%
%U	一年中的第几周，星期天是每周的第一天 (00-53)	(根据2023-10-27所在周数决定)
%u	一年中的第几周，星期一是每周的第一天 (00-53)	(根据2023-10-27所在周数决定)
%X	用于 %V 的年份，星期天是每周的第一天。用于大多数年份的逻辑周计算。	(涉及复杂的周计算，具体取决于年份和周数)
%x	用于 %v 的年份，星期一是每周的第一天。用于大多数年份的逻辑周计算。	(涉及复杂的周计算，具体取决于年份和周数)
%V	一年中的第几周，星期天是每周的第一天，用于 %X；范围 01-53。如果包含 1 月 1 日的那周在新的一年有 4 天或更多天，则为第 1 周；否则，它是前一年第 53 周，而下一年是第 1 周。	(涉及复杂的周计算，具体取决于年份和周数)
%v	一年中的第几周，星期一是每周的第一天，用于 %x；范围 01-53。如果包含 1 月 1 日的那周在新的一年有 4 天或更多天，则为第 1 周；否则，它是前一年第 53 周，而下一年是第 1 周。	(涉及复杂的周计算，具体取决于年份和周数)

3. 常见用法示例

下面我们通过一些具体的例子来演示 STR_TO_DATE() 的用法。

• 示例1：将 YYYY-MM-DD 格式的字符串转换为日期

SELECT STR_TO_DATE('2023-10-27', '%Y-%m-%d');
-- 输出: 2023-10-27

在这个例子中，我们使用 %Y-%m-%d 格式化字符串来告诉 MySQL，输入的字符串的格式是 “四位数年份-两位数月份-两位数日期”。

• 示例2：将 MM/DD/YYYY 格式的字符串转换为日期

SELECT STR_TO_DATE('10/27/2023', '%m/%d/%Y');
-- 输出: 2023-10-27

这里，我们使用 %m/%d/%Y 格式化字符串来匹配 MM/DD/YYYY 格式。

• 示例3：将 DD.MM.YYYY 格式的字符串转换为日期

SELECT STR_TO_DATE('27.10.2023', '%d.%m.%Y');
-- 输出: 2023-10-27

这个例子演示了如何处理使用点号作为分隔符的日期字符串。

• 示例4：将包含时间的字符串转换为日期时间

SELECT STR_TO_DATE('2023-10-27 14:30:45', '%Y-%m-%d %H:%i:%s');
-- 输出: 2023-10-27 14:30:45

这个例子展示了如何将包含日期和时间的字符串转换为 DATETIME 类型。

• 示例5：使用 12 小时制和 AM/PM 指示符

SELECT STR_TO_DATE('2023-10-27 02:30:45 PM', '%Y-%m-%d %h:%i:%s %p');
-- 输出: 2023-10-27 14:30:45

注意 %h (或 %I) 和 %p 的使用，它们分别表示 12 小时制的小时和 AM/PM 指示符。

• 示例6：包含月份名称的日期字符串

SELECT STR_TO_DATE('October 27, 2023', '%M %d, %Y');
-- 输出: 2023-10-27

这个例子展示了如何解析包含月份全名的日期字符串。

• 示例7：处理不带前导零的日期和月份

SELECT STR_TO_DATE('2023-1-7', '%Y-%c-%e');
-- 输出: 2023-01-07

%c 和 %e 用于匹配不带前导零的月份和日期。

• 示例8：从包含星期几的字符串中提取日期

SELECT STR_TO_DATE('Friday, October 27, 2023', '%W, %M %d, %Y');
-- 输出: 2023-10-27

这个例子演示了如何忽略字符串中的星期几信息。

4. 注意事项和常见问题

• 格式化字符串必须与输入字符串的格式完全匹配。 任何不匹配都可能导致 STR_TO_DATE() 返回 NULL。
• STR_TO_DATE() 对大小写敏感。 例如，%M 匹配月份的全名（如 "October"），而 %m 匹配两位数的月份 (如 "10")。
• 如果输入字符串无法解析为有效的日期或日期时间，STR_TO_DATE() 将返回 NULL。
• 在处理包含时间信息的字符串时，请务必指定正确的时制 (12 小时制或 24 小时制) 和 AM/PM 指示符。
• 当处理包含特殊字符（如 -, /, .）的日期字符串时，需要在格式化字符串中包含这些字符。
• 不同数据库系统对日期格式的支持可能有所不同。 这里讨论的是 MySQL 中的 STR_TO_DATE() 函数。
• 性能考虑： 虽然 STR_TO_DATE() 功能强大，但在大量数据处理时，频繁使用它可能会影响性能。如果可能，最好在数据导入或预处理阶段将日期字符串转换为日期类型，而不是在查询时动态转换。

5. 实际应用场景

STR_TO_DATE() 函数在实际应用中非常广泛。以下是一些常见的应用场景：

• 数据清洗和转换： 当从外部系统导入数据时，日期格式可能不统一。可以使用 STR_TO_DATE() 将这些字符串转换为统一的日期格式。
• 报表生成： 在生成报表时，可能需要将存储为字符串的日期数据转换为日期类型，以便进行日期范围的筛选和排序。
• 数据分析： 在进行数据分析时，需要对日期数据进行处理和计算。STR_TO_DATE() 可以将字符串日期转换为日期类型，以便进行各种日期相关的分析操作。
• 用户界面： 有时候用户输入的是字符串形式的日期，在将这些数据存储到数据库之前，需要使用 STR_TO_DATE() 将其转换为日期类型。
• 日志分析： 日志文件中通常包含大量的日期时间信息，这些信息通常以字符串的形式存在。可以使用 STR_TO_DATE() 将这些字符串转换为日期时间类型，以便进行日志分析。

6. 高级用法和技巧

• 结合 CASE 语句处理多种日期格式： 如果数据源包含多种不同的日期格式，可以使用 CASE 语句结合 STR_TO_DATE() 来处理：

SELECT
    CASE
        WHEN str LIKE '%-%-%' THEN STR_TO_DATE(str, '%Y-%m-%d')
        WHEN str LIKE '%/%/%' THEN STR_TO_DATE(str, '%m/%d/%Y')
        ELSE NULL
    END AS formatted_date
FROM
    your_table;

这个例子演示了如何根据字符串的格式选择不同的格式化字符串。

• 创建自定义函数简化转换： 如果经常需要将特定格式的字符串转换为日期，可以创建一个自定义函数来简化操作：

DELIMITER //
CREATE FUNCTION string_to_date_custom(str VARCHAR(255))
RETURNS DATE
BEGIN
    RETURN STR_TO_DATE(str, '%d.%m.%Y');
END //
DELIMITER ;

-- 使用自定义函数
SELECT string_to_date_custom('27.10.2023');

• 利用 MySQL 的隐式转换： 有时候 MySQL 可以隐式地将某些格式的字符串转换为日期类型，但这种方式依赖于 MySQL 的配置和字符串的格式。建议始终使用 STR_TO_DATE() 显式地进行转换，以避免不确定性和潜在的错误。

7. 总结：理解格式化参数是关键

STR_TO_DATE() 函数是 MySQL 中一个非常强大的工具，可以将各种格式的日期字符串转换为日期或日期时间类型。熟练掌握各种格式化参数，能够灵活地处理各种复杂的日期字符串。在实际应用中，应根据具体的日期格式选择合适的格式化字符串，并注意处理可能出现的错误和异常情况。

这篇文章详细介绍了STR_TO_DATE()函数的语法和用法，重点讲解了各种格式化参数，并通过多个实例演示了如何在实际应用中使用该函数。希望通过这篇文章，你能对STR_TO_DATE()函数有更深入的理解和掌握。