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

MySQL 高级函数 STR_TO_DATE()：字符串转日期格式化参数详解

大家好！今天我们来深入探讨 MySQL 中一个非常重要的日期时间函数：STR_TO_DATE()。这个函数的主要作用是将字符串转换为日期或日期时间类型，这在处理从外部系统导入的数据、用户输入数据等场景中非常常见。掌握 STR_TO_DATE() 及其格式化参数，能够帮助我们更灵活、准确地进行数据清洗和转换。

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

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

STR_TO_DATE(str, format)

• str：要转换的字符串。
• format：定义字符串 str 的格式，告诉 MySQL 如何解析该字符串。

该函数返回一个 DATE、DATETIME 或 NULL 值，具体取决于 format 参数以及 str 是否能够被成功解析。 如果str 为 NULL，则该函数返回 NULL。 如果 format 参数为 NULL 或为空字符串，则该函数返回 NULL。 如果 str 无法根据 format 参数解析，则该函数返回 NULL。

2. format 参数详解：格式化符号

format 参数是 STR_TO_DATE() 函数的核心，它使用一系列格式化符号来指定输入字符串的结构。 这些格式化符号告诉 MySQL 如何提取日期、时间和其他相关信息。 让我们详细了解一下这些符号：

格式化符号	描述	示例
%Y	四位数的年份 (例如: 2023)	2023
%y	两位数的年份 (例如: 23)	23
%m	月份，数字 (01..12)	01, 12
%c	月份，数字 (1..12)	1, 12
%M	月份，全名 (January..December)	January
%b	月份，缩写 (Jan..Dec)	Jan
%d	一个月中的第几天，数字 (01..31)	01, 31
%e	一个月中的第几天，数字 (1..31)	1, 31
%D	一个月中的第几天，带后缀 (1st, 2nd, 3rd, …)	1st, 2nd
%H	小时 (00..23)	00, 23
%k	小时 (0..23)	0, 23
%h	小时 (01..12)	01, 12
%I	小时 (01..12)	01, 12
%l	小时 (1..12)	1, 12
%i	分钟 (00..59)	00, 59
%s	秒 (00..59)	00, 59
%S	秒 (00..59)	00, 59
%f	微秒 (000000..999999)	000000, 999999
%p	AM 或 PM	AM, PM
%r	12小时制时间 (hh:mm:ss AM/PM)	10:30:00 AM
%T	24小时制时间 (hh:mm:ss)	10:30:00
%w	一周中的第几天 (0=Sunday..6=Saturday)	0, 6
%W	一周中的第几天，月份全名 (Sunday..Saturday)	Sunday
%a	一周中的第几天，缩写 (Sun..Sat)	Sun
%j	一年中的第几天 (001..366)	001, 366
%U	一年中的第几周，星期日是一周的第一天 (00..53)	00, 53
%u	一年中的第几周，星期一是一周的第一天 (00..53)	00, 53
%X	一年中的第几周，星期日是一周的第一天，’%X%V’ 可用于获取 ISO 周份年份	2023
%x	一年中的第几周，星期一是一周的第一天，’%x%v’ 可用于获取 ISO 周份年份	2023
%V	一年中的第几周，配合 ‘%X’ 使用，星期日是一周的第一天 (01..53)	01, 53
%v	一年中的第几周，配合 ‘%x’ 使用，星期一是一周的第一天 (01..53)	01, 53
%%	字面上的 ‘%’ 字符	%
%Y-%m-%d	常用格式：年-月-日 (例如: 2023-10-27)	2023-10-27

注意:

• 格式化符号区分大小写。
• 可以使用任何字符作为分隔符，只要它与格式化符号不冲突。 例如，可以使用 /, -, ., , 或空格。
• 如果 str 字符串中的分隔符与 format 参数中的分隔符不匹配，则 STR_TO_DATE() 函数可能无法正确解析字符串。
• %x, %X, %v, %V 通常配合使用来处理 ISO 周份年份的情况。

3. 示例演示

接下来，我们通过一些示例来演示 STR_TO_DATE() 函数的用法。

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

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

在这个例子中，我们将字符串 2023-10-27 转换为日期。 %Y-%m-%d 格式化参数告诉 MySQL，字符串的格式是年-月-日，年份是四位数，月份和日期都是两位数。

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

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

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

示例 3: 将带有时间的字符串转换为日期时间

SELECT STR_TO_DATE('2023-10-27 10:30:00', '%Y-%m-%d %H:%i:%s');
-- Output: 2023-10-27 10:30:00

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

示例 4: 使用月份名称

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

SELECT STR_TO_DATE('Oct 27, 2023', '%b %d, %Y');
-- Output: 2023-10-27

这里展示了如何使用月份的全名 (%M) 和缩写 (%b)。

示例 5: 处理 12 小时制时间

SELECT STR_TO_DATE('10:30:00 AM', '%h:%i:%s %p');
-- Output: NULL  (需要完整的日期信息才能正确转换成 DATETIME)

SELECT STR_TO_DATE('2023-10-27 10:30:00 AM', '%Y-%m-%d %h:%i:%s %p');
-- Output: 2023-10-27 10:30:00

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

注意，当只提供时间信息而没有日期信息时，STR_TO_DATE() 无法返回有效的 DATETIME 值，而是返回 NULL。 当提供了完整的日期和时间信息时，%p 可以正确地处理 AM/PM 指示符，并将时间转换为 24 小时制。

示例 6: 处理不规范的日期格式

假设你有一个包含不规范日期格式的数据表，例如，日期可能以 DD.MM.YYYY 或 MM-DD-YYYY 格式存储。 你可以使用 CASE 语句和 STR_TO_DATE() 来处理这些不同的格式。

CREATE TABLE dates (
    date_string VARCHAR(20)
);

INSERT INTO dates (date_string) VALUES
('27.10.2023'),
('10-27-2023'),
('2023-10-28');

SELECT
    date_string,
    CASE
        WHEN date_string LIKE '%-%' THEN STR_TO_DATE(date_string, '%m-%d-%Y')
        WHEN date_string LIKE '%.%' THEN STR_TO_DATE(date_string, '%d.%m.%Y')
        ELSE STR_TO_DATE(date_string, '%Y-%m-%d')
    END AS converted_date
FROM
    dates;

这个例子首先创建了一个名为 dates 的表，其中包含一个 date_string 列，用于存储不同格式的日期字符串。 然后，它使用 CASE 语句检查 date_string 的格式，并根据格式选择合适的 STR_TO_DATE() 函数和格式化参数。

示例 7: 从Unix时间戳转换

虽然STR_TO_DATE不直接处理Unix时间戳，但你可以结合FROM_UNIXTIME函数来实现转换。

SELECT FROM_UNIXTIME(1698403200);
-- output: 2023-10-27 00:00:00

如果你需要将Unix时间戳先转换为字符串，再用STR_TO_DATE，通常是不必要的，直接使用FROM_UNIXTIME即可。

示例 8: 处理包含毫秒的时间戳

MySQL 5.6.4 之后支持毫秒，可以使用 %f 格式化符号来解析包含毫秒的时间戳。

SELECT STR_TO_DATE('2023-10-27 10:30:00.123', '%Y-%m-%d %H:%i:%s.%f');
-- Output: 2023-10-27 10:30:00.123000

示例 9: 使用 %x, %X, %v, %V处理ISO周份年份

SELECT STR_TO_DATE('2023-W43-5', '%X-W%v-%w');
-- Output: 2023-10-27

SELECT STR_TO_DATE('2024-W01-1', '%X-W%v-%w');
-- Output: 2024-01-01

SELECT STR_TO_DATE('2023-W52-7', '%X-W%v-%w');
-- Output: 2023-12-31

在这个例子中，%X 表示 ISO 周份年份，%v 表示 ISO 周数 (01-53)， %w 表示一周中的第几天 (0=Sunday..6=Saturday)。 注意，%X 和 %v 总是成对使用，以确保正确解析 ISO 周份年份。

4. STR_TO_DATE() 的注意事项

• 错误处理: 如果 STR_TO_DATE() 无法解析字符串，它将返回 NULL。 因此，在使用 STR_TO_DATE() 之前，最好验证输入字符串的格式是否正确。 可以使用 CASE 语句或正则表达式来检查字符串的格式。
• 性能: STR_TO_DATE() 函数的性能可能受到输入字符串的复杂性和数据量的影响。 如果需要处理大量数据，可以考虑使用其他方法来提高性能，例如，在应用程序层进行数据转换。
• 时区: STR_TO_DATE() 函数不处理时区。 如果需要处理时区，可以使用 CONVERT_TZ() 函数。

5. 实际应用场景

• 数据导入: 从 CSV 文件、Excel 文件或其他数据源导入数据时，日期和时间通常以字符串形式存储。 使用 STR_TO_DATE() 函数可以将这些字符串转换为日期或日期时间类型。
• 用户输入: 从 Web 表单或其他用户界面获取日期和时间数据时，用户输入的格式可能不一致。 使用 STR_TO_DATE() 函数可以标准化用户输入，并将其转换为数据库可以存储的格式。
• 数据清洗: 在数据分析和报表生成中，经常需要清洗和转换数据。 STR_TO_DATE() 函数可以帮助你将不规范的日期和时间数据转换为标准格式。
• 日志分析: 分析应用程序或系统日志时，日期和时间通常以字符串形式存储。 使用 STR_TO_DATE() 函数可以方便地对日志数据进行时间相关的分析。
• 数据集成: 当从不同的数据源集成数据时，日期和时间的格式可能不同。 使用 STR_TO_DATE() 函数可以将不同格式的日期和时间数据转换为统一格式。

6. 总结

STR_TO_DATE() 是一个非常强大的 MySQL 函数，用于将字符串转换为日期或日期时间类型。 掌握其格式化参数，可以灵活地处理各种不同的日期和时间格式。 通过结合 CASE 语句、正则表达式和其他函数，可以更好地处理不规范的日期格式和错误情况。

7. 灵活转换，数据格式不再是阻碍

STR_TO_DATE() 通过 format 参数提供了对字符串日期转换的强大控制，使我们能够处理各种各样的日期字符串格式，确保数据的准确性和一致性。熟练掌握这些格式化符号，可以应对各种实际场景中的数据转换需求。