如何利用`STR_TO_DATE()`函数将字符串转换为日期？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

`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 的日期/时间格式。这个格式字符串必须与 str 的实际格式完全匹配，否则转换结果将为 NULL。

2. 格式字符串 (Format String) 的构成

STR_TO_DATE() 的核心在于格式字符串。MySQL 提供了丰富的格式符，用于描述日期和时间的各个部分。下面是一些常用的格式符及其含义：

格式符	含义	示例
`%Y`	四位年份 (例如：2023)	2023
`%y`	两位年份 (例如：23)	23
`%m`	两位月份 (01-12)	01, 12
`%c`	月份 (1-12)	1, 12
`%d`	两位日期 (01-31)	01, 31
`%e`	日期 (1-31)	1, 31
`%H`	24 小时制的小时 (00-23)	00, 23
`%k`	24 小时制的小时 (0-23)	0, 23
`%h`	12 小时制的小时 (01-12)	01, 12
`%I`	12 小时制的小时 (01-12)	01, 12
`%l`	12 小时制的小时 (1-12)	1, 12
`%i`	分钟 (00-59)	00, 59
`%s`	秒 (00-59)	00, 59
`%S`	秒 (00-59)	00, 59
`%p`	AM 或 PM	AM, PM
`%W`	星期几的完整名称 (例如：Sunday, Monday)	Sunday, Monday
`%a`	星期几的缩写名称 (例如：Sun, Mon)	Sun, Mon
`%M`	月份的完整名称 (例如：January, February)	January, February
`%b`	月份的缩写名称 (例如：Jan, Feb)	Jan, Feb
`%j`	一年中的天数 (001-366)	001, 366
`%%`	字面值 `%`	%

3. STR_TO_DATE() 函数的使用示例

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

示例 1: 转换日期字符串

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

示例 2: 转换日期时间字符串

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

示例 3: 处理包含星期几的日期字符串

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

示例 4: 从表中转换日期字符串

假设我们有一个名为 orders 的表，其中包含一个名为 order_date_str 的 VARCHAR 类型的列，存储了订单日期字符串。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date_str VARCHAR(20)
);

INSERT INTO orders (order_date_str) VALUES
('2023-10-26'),
('2023/10/27'),
('October 28, 2023');

SELECT order_id, order_date_str, STR_TO_DATE(order_date_str, '%Y-%m-%d') AS order_date
FROM orders
WHERE STR_TO_DATE(order_date_str, '%Y-%m-%d') IS NOT NULL;  -- 只显示可以成功转换的行

-- 如果日期格式不统一，需要使用 CASE 语句进行判断
SELECT order_id, order_date_str,
       CASE
           WHEN order_date_str LIKE '____-__-__' THEN STR_TO_DATE(order_date_str, '%Y-%m-%d')
           WHEN order_date_str LIKE '____/%_/%_' THEN STR_TO_DATE(order_date_str, '%Y/%m/%d')
           WHEN order_date_str LIKE '% %_, ____' THEN STR_TO_DATE(order_date_str, '%M %d, %Y')
           ELSE NULL
       END AS order_date
FROM orders;

4. STR_TO_DATE() 函数的注意事项

格式字符串必须与字符串的格式完全匹配。 即使只有一个字符不匹配，转换结果也会是 NULL。
区分大小写。 例如，%Y 表示四位年份，而 %y 表示两位年份。
分隔符必须一致。 如果字符串中使用的是 / 分隔符，那么格式字符串也必须使用 / 分隔符。
处理 NULL 值。 如果 str 是 NULL，那么 STR_TO_DATE() 函数也会返回 NULL。
时区问题。 STR_TO_DATE() 函数不会处理时区信息。如果需要处理时区，需要在转换之后进行时区转换。
性能考虑。 在大型数据集上使用 STR_TO_DATE() 函数可能会影响性能。如果可能，最好在数据导入时就将字符串转换为日期/时间类型。对于频繁查询的列，尽量避免在 WHERE 子句中使用 STR_TO_DATE()，可以考虑创建基于计算列的索引（MySQL 5.7 及更高版本支持）。

5. 处理多种日期格式：更加灵活的转换

在实际应用中，我们经常会遇到日期格式不统一的情况。 STR_TO_DATE() 函数本身无法直接处理多种格式，但我们可以结合 CASE 语句或其他字符串处理函数，来实现更灵活的转换。

以下是一些常用的处理技巧：

使用 CASE 语句: 如上面的例子所示，CASE 语句可以根据不同的日期格式应用不同的格式字符串。
结合 REPLACE() 函数: 如果只是分隔符不同，可以使用 REPLACE() 函数将分隔符替换为统一的格式，然后再使用 STR_TO_DATE() 函数。例如：

SELECT STR_TO_DATE(REPLACE('2023/10/27', '/', '-'), '%Y-%m-%d'); -- 将 '/' 替换为 '-'

自定义函数: 可以创建一个自定义函数，用于检测日期字符串的格式，并根据格式选择合适的格式字符串。这种方法可以提供更高的灵活性，但需要一定的编程技巧。

示例：自定义函数处理多种日期格式

DELIMITER //
CREATE FUNCTION parse_date(date_str VARCHAR(255))
RETURNS DATE
DETERMINISTIC
BEGIN
    DECLARE result DATE;

    -- 尝试 YYYY-MM-DD 格式
    SET result = STR_TO_DATE(date_str, '%Y-%m-%d');
    IF result IS NOT NULL THEN
        RETURN result;
    END IF;

    -- 尝试 YYYY/MM/DD 格式
    SET result = STR_TO_DATE(date_str, '%Y/%m/%d');
    IF result IS NOT NULL THEN
        RETURN result;
    END IF;

    -- 尝试 MM/DD/YYYY 格式
    SET result = STR_TO_DATE(date_str, '%m/%d/%Y');
    IF result IS NOT NULL THEN
        RETURN result;
    END IF;

    -- 尝试 Month DD, YYYY 格式
    SET result = STR_TO_DATE(date_str, '%M %d, %Y');
    IF result IS NOT NULL THEN
        RETURN result;
    END IF;

    -- 如果所有格式都不匹配，则返回 NULL
    RETURN NULL;
END //
DELIMITER ;

-- 使用自定义函数
SELECT order_id, order_date_str, parse_date(order_date_str) AS order_date
FROM orders;

DROP FUNCTION parse_date; -- 删除函数 (可选)

6. STR_TO_DATE() 与 DATE_FORMAT() 的对比

STR_TO_DATE() 函数用于将字符串转换为日期/时间类型，而 DATE_FORMAT() 函数则用于将日期/时间类型格式化为字符串。它们是两个互补的函数，经常一起使用。

STR_TO_DATE(): String -> Date/Datetime
DATE_FORMAT(): Date/Datetime -> String

例如，我们可以先使用 STR_TO_DATE() 将字符串转换为日期类型，然后再使用 DATE_FORMAT() 将日期类型格式化为特定的字符串格式：

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

7. STR_TO_DATE() 在数据清洗中的应用

在数据清洗过程中，STR_TO_DATE() 函数经常被用于将不规范的日期字符串转换为标准的日期类型，以便进行后续的数据分析和处理。

例如，假设我们从外部导入了一份包含大量日期字符串的数据，这些日期字符串的格式可能各不相同。我们可以使用 STR_TO_DATE() 函数结合 CASE 语句或其他字符串处理函数，将这些日期字符串转换为标准的日期类型，然后将转换后的数据存储到数据库中。

8. STR_TO_DATE() 的替代方案

虽然 STR_TO_DATE() 函数非常实用，但在某些情况下，可能需要考虑使用替代方案。例如：

在应用程序层面进行转换: 如果应用程序能够识别日期字符串的格式，可以在应用程序层面进行转换，然后再将日期类型的数据存储到数据库中。这种方法可以减轻数据库的压力，提高性能。
使用 ETL 工具: 如果需要处理大量的数据，可以使用 ETL (Extract, Transform, Load) 工具，这些工具通常提供更强大的数据转换功能。

9. 错误处理：当转换失败时

当 STR_TO_DATE() 无法将字符串转换为日期时，它会返回 NULL。因此，在使用 STR_TO_DATE() 函数时，务必注意处理 NULL 值，以避免出现意外的错误。

以下是一些常用的错误处理技巧：

使用 IFNULL() 函数: 可以使用 IFNULL() 函数将 NULL 值替换为默认的日期值。

SELECT IFNULL(STR_TO_DATE('invalid date', '%Y-%m-%d'), '1900-01-01'); -- 如果转换失败，则返回 '1900-01-01'

在 WHERE 子句中使用 IS NOT NULL: 可以使用 IS NOT NULL 过滤掉无法转换的行。如之前的例子所示。
使用 TRY_CONVERT() 函数 (SQL Server): SQL Server 提供了 TRY_CONVERT() 函数，该函数在转换失败时不会抛出错误，而是返回 NULL。 MySQL 没有直接对应的函数，但可以通过自定义函数实现类似的功能。

10. 格式字符串的更多细节

除了上面列出的常用格式符之外，MySQL 还提供了其他一些格式符，用于处理更复杂的日期/时间格式。

%U: 一年中的周数 (00-53)，星期日是一周的第一天。
%u: 一年中的周数 (00-53)，星期一是一周的第一天。
%V: 一年中的周数 (01-53)，星期日是一周的第一天；与 %X 一起使用。
%v: 一年中的周数 (01-53)，星期一是一周的第一天；与 %x 一起使用。
%X: 与 %V 一起使用，表示年份，星期日是一周的第一天。
%x: 与 %v 一起使用，表示年份，星期一是一周的第一天。
%T: 等价于 %H:%i:%s。
%r: 等价于 %h:%i:%s %p。
%D: 带有英语后缀的日期 (1st, 2nd, 3rd, 等等)。

11. 性能优化：减少 STR_TO_DATE() 的使用

STR_TO_DATE() 函数在执行时需要进行字符串解析和类型转换，这可能会消耗一定的资源。为了提高查询性能，可以考虑以下优化措施：

尽量在数据导入时进行转换: 如果数据源是外部文件或系统，最好在数据导入到数据库之前，就将日期字符串转换为日期/时间类型。这样可以避免在查询时重复进行转换。
创建计算列并建立索引 (MySQL 5.7 及更高版本): 如果需要在查询时对日期字符串进行转换，可以创建一个计算列，并在该列上建立索引。计算列会存储转换后的日期值，索引可以加速查询。

ALTER TABLE orders ADD COLUMN order_date DATE AS (STR_TO_DATE(order_date_str, '%Y-%m-%d'));
CREATE INDEX idx_order_date ON orders (order_date);

避免在 WHERE 子句中直接使用 STR_TO_DATE(): 尽量避免在 WHERE 子句中直接使用 STR_TO_DATE() 函数，因为这会导致索引失效。如果必须使用，可以考虑使用范围查询或预先计算日期范围。

12. 灵活转换，应对多种日期格式

STR_TO_DATE() 是一个强大的工具，但必须理解其局限性。通过结合 CASE 语句，REPLACE() 函数，自定义函数等手段，可以更加灵活地处理各种复杂的日期格式。

13. 注意事项和性能优化

要记住，格式字符串必须与字符串的格式完全匹配，否则转换结果将为 NULL。另外，在处理大量数据时，要注意 STR_TO_DATE() 的性能影响，并采取相应的优化措施。尽可能在数据导入时就进行转换，或者创建计算列并建立索引。

STR_TO_DATE() 函数详解：字符串日期转换的艺术

发表回复 取消回复

`STR_TO_DATE()` 函数详解：字符串日期转换的艺术

发表回复取消回复