MySQL函数：`DATE_FORMAT()`与`STR_TO_DATE()`灵活地格式化和解析日期时间字符串。

好的，我们开始。

MySQL日期时间字符串的艺术：DATE_FORMAT()与STR_TO_DATE()

大家好，今天我们来深入探讨MySQL中两个非常重要的日期时间函数：DATE_FORMAT()和STR_TO_DATE()。这两个函数分别负责将日期时间值格式化为字符串，以及将字符串解析为日期时间值。 掌握它们对于处理数据库中的日期时间数据至关重要。

DATE_FORMAT()：将日期时间值转化为字符串

DATE_FORMAT()函数允许你以各种自定义格式将日期时间值转换为字符串。它的基本语法如下：

DATE_FORMAT(date, format)

• date: 要格式化的日期时间值。它可以是DATE、DATETIME、TIMESTAMP等类型。
• format: 一个字符串，指定所需的输出格式。这个字符串包含特殊的格式化代码，每个代码代表日期时间值的不同部分。

常用的日期时间格式化代码

下面是一些最常用的DATE_FORMAT()格式化代码：

格式代码	描述
%Y	四位数的年份 (例如: 2023)
%y	两位数的年份 (例如: 23)
%m	两位数的月份 (01-12)
%c	月份 (1-12)
%d	两位数的日 (01-31)
%e	日 (1-31)
%H	24小时制的小时 (00-23)
%h	12小时制的小时 (01-12)
%i	分钟 (00-59)
%s	秒 (00-59)
%f	微秒 (000000-999999)
%p	AM 或 PM
%W	星期几的完整名称 (例如: Sunday)
%a	星期几的缩写名称 (例如: Sun)
%M	月份的完整名称 (例如: January)
%b	月份的缩写名称 (例如: Jan)
%j	一年中的第几天 (001-366)
%U	一年中的第几周，星期日是每周的第一天 (00-53)
%u	一年中的第几周，星期一是每周的第一天 (00-53)
%w	星期几 (0=星期日, 1=星期一, …, 6=星期六)
%X	一年中的第几周，星期日是每周的第一天。返回年份，其中周从星期日开始，即使它属于上一年。
%x	一年中的第几周，星期一是每周的第一天。返回年份，其中周从星期一开始，即使它属于上一年。
%%	字面上的 ‘%’ 字符

DATE_FORMAT()示例

假设我们有一个名为 orders 的表，其中包含一个 order_date 列（数据类型为 DATETIME），存储订单的日期和时间。

1. 格式化为 YYYY-MM-DD:

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date FROM orders;

这条SQL语句将 order_date 列格式化为 YYYY-MM-DD 格式，例如 2023-10-27。

2. 格式化为 MM/DD/YYYY:

SELECT DATE_FORMAT(order_date, '%m/%d/%Y') AS formatted_date FROM orders;

这条SQL语句将 order_date 列格式化为 MM/DD/YYYY 格式，例如 10/27/2023。

3. 包含时间和 AM/PM:

SELECT DATE_FORMAT(order_date, '%Y-%m-%d %h:%i:%s %p') AS formatted_date FROM orders;

这条SQL语句将 order_date 列格式化为 YYYY-MM-DD hh:mm:ss AM/PM 格式，例如 2023-10-27 03:30:45 PM。

4. 包含星期几和月份名称:

SELECT DATE_FORMAT(order_date, '%W, %M %e, %Y') AS formatted_date FROM orders;

这条SQL语句将 order_date 列格式化为 Weekday, Month Day, Year 格式，例如 Friday, October 27, 2023。

5. 仅提取年份和月份:

SELECT DATE_FORMAT(order_date, '%Y-%m') AS formatted_date FROM orders;

这条SQL语句将 order_date 列格式化为 YYYY-MM 格式，例如 2023-10。 这在按月份汇总数据时非常有用。

STR_TO_DATE()：将字符串转化为日期时间值

STR_TO_DATE()函数的作用与DATE_FORMAT()相反。它将一个字符串按照指定的格式解析为日期时间值。它的基本语法如下：

STR_TO_DATE(str, format)

• str: 要解析的字符串。
• format: 一个字符串，指定输入字符串的格式。 它使用与 DATE_FORMAT() 相同的格式化代码。

STR_TO_DATE()示例

假设我们有一个名为 events 的表，其中包含一个 event_date_string 列（数据类型为 VARCHAR），存储事件的日期字符串。

1. 将 YYYY-MM-DD 格式的字符串转换为 DATE 类型:

SELECT STR_TO_DATE('2023-10-27', '%Y-%m-%d') AS event_date;

这条SQL语句将字符串 '2023-10-27' 转换为 DATE 类型的值。

2. 将 MM/DD/YYYY 格式的字符串转换为 DATE 类型:

SELECT STR_TO_DATE('10/27/2023', '%m/%d/%Y') AS event_date;

这条SQL语句将字符串 '10/27/2023' 转换为 DATE 类型的值。

3. 将包含时间的字符串转换为 DATETIME 类型:

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

这条SQL语句将字符串 '2023-10-27 14:30:00' 转换为 DATETIME 类型的值。

4. 处理包含 AM/PM 的时间:

SELECT STR_TO_DATE('2023-10-27 03:30:00 PM', '%Y-%m-%d %h:%i:%s %p') AS event_datetime;

这条SQL语句将字符串 '2023-10-27 03:30:00 PM' 转换为 DATETIME 类型的值。

5. 更新表中的日期字符串列:

假设 events 表的 event_date_string 列包含 MM/DD/YYYY 格式的字符串，我们需要将其转换为 DATE 类型并存储在一个新的 event_date 列中。

ALTER TABLE events ADD COLUMN event_date DATE;

UPDATE events SET event_date = STR_TO_DATE(event_date_string, '%m/%d/%Y');

首先，我们向表中添加一个新的 DATE 类型的列 event_date。 然后，我们使用 UPDATE 语句，利用 STR_TO_DATE() 函数将 event_date_string 列中的字符串转换为 DATE 类型的值，并将其存储到 event_date 列中。

注意事项和常见问题

1. 格式字符串必须匹配: STR_TO_DATE() 函数要求输入的字符串格式与指定的格式字符串完全匹配。 如果格式不匹配，函数将返回 NULL。

   例如，如果你尝试使用 %Y-%m-%d 格式字符串解析 '2023/10/27'，函数将返回 NULL，因为分隔符不匹配。

2. 大小写敏感性: 某些格式代码（例如 %p）是大小写敏感的。 确保使用正确的大小写以匹配输入字符串。

3. 日期时间部分的缺失: 如果输入字符串缺少某些日期时间部分，STR_TO_DATE() 函数可能会根据缺失的部分设置默认值。 例如，如果字符串只包含日期，时间部分将默认为 00:00:00。

4. 无效的日期时间值: 如果输入字符串包含无效的日期时间值（例如，2月30日），STR_TO_DATE() 函数将返回 NULL。

5. NULL 值的处理: 如果输入字符串为 NULL，STR_TO_DATE() 函数也将返回 NULL。

6. 性能: 在大型数据集上使用 STR_TO_DATE() 函数可能会影响性能。 如果可能，尽量避免在查询中频繁使用它。 最好在数据导入或更新时进行转换，并将结果存储在适当的日期时间类型的列中。

7. 时区: DATE_FORMAT() 和 STR_TO_DATE() 函数受 MySQL 服务器时区的影响。 确保服务器时区设置正确，以避免意外的结果。可以使用 SELECT @@time_zone; 查询当前时区。

8. 与 CAST() 和 CONVERT() 的比较: 虽然 CAST() 和 CONVERT() 也可以用于日期时间类型的转换，但 STR_TO_DATE() 提供了更灵活的格式化选项。 CAST() 和 CONVERT() 通常使用预定义的格式，而 STR_TO_DATE() 允许你指定自定义格式。

更复杂的例子

1. 从包含日期的文件名中提取日期:

假设你有一个存储图像文件信息的表，其中文件名包含日期信息，例如 image_20231027.jpg。 你可以使用 SUBSTRING() 和 STR_TO_DATE() 函数从文件名中提取日期。

SELECT
    filename,
    STR_TO_DATE(SUBSTRING(filename, 7, 8), '%Y%m%d') AS image_date
FROM
    images
WHERE
    filename LIKE 'image_%';

这条SQL语句首先使用 SUBSTRING() 函数提取文件名中从第7个字符开始的8个字符（即日期部分），然后使用 STR_TO_DATE() 函数将其转换为 DATE 类型。

2. 将Unix时间戳转换为日期时间:

MySQL提供了FROM_UNIXTIME()函数，但如果你需要自定义格式，可以结合使用FROM_UNIXTIME()和DATE_FORMAT()。

SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp_column), '%Y-%m-%d %H:%i:%s') FROM my_table;

这里假设timestamp_column是一个存储Unix时间戳的列。

3. 处理不同格式的日期字符串:

有时候，你的数据源可能包含多种不同的日期格式。 你可以使用 CASE 语句和 STR_TO_DATE() 函数来处理这些不同的格式。

SELECT
    CASE
        WHEN date_string LIKE '%-%-%' THEN STR_TO_DATE(date_string, '%Y-%m-%d')
        WHEN date_string LIKE '%/%/%' THEN STR_TO_DATE(date_string, '%m/%d/%Y')
        ELSE NULL  -- 处理无法识别的格式
    END AS parsed_date
FROM
    my_table;

这个例子假设 date_string 列可能包含 YYYY-MM-DD 或 MM/DD/YYYY 格式的字符串。CASE 语句根据字符串的格式选择相应的 STR_TO_DATE() 函数进行解析。

关于性能的更多讨论

正如前面提到的，在大型数据集上使用 STR_TO_DATE() 可能会影响性能。 以下是一些优化技巧：

1. 索引: 如果经常需要根据转换后的日期列进行查询，请在该列上创建索引。 这可以显著提高查询速度。

2. 预先计算: 如果可能，在数据导入或更新时预先计算日期时间值，并将其存储在适当的数据类型列中。 避免在查询时动态转换字符串。

3. 函数索引 (MySQL 5.7+): MySQL 5.7 及更高版本支持函数索引。 你可以创建一个基于 STR_TO_DATE() 函数的索引，以提高查询性能。

   CREATE INDEX idx_event_date ON events ((STR_TO_DATE(event_date_string, '%m/%d/%Y')));

   请注意，函数索引可能会增加写入操作的开销。

4. 避免在 WHERE 子句中使用函数: 尽量避免在 WHERE 子句中使用 STR_TO_DATE() 函数。 例如，不要这样做：

   SELECT * FROM events WHERE STR_TO_DATE(event_date_string, '%m/%d/%Y') > '2023-01-01';

   而应该尝试预先计算日期值，并将其用于比较：

   SELECT * FROM events WHERE event_date > '2023-01-01';  -- 假设 event_date 是 DATE 类型

5. 数据清洗和标准化: 在数据进入数据库之前，尽可能地清洗和标准化日期格式。 这可以减少以后需要进行的转换工作量。

高级技巧

1. 使用用户自定义函数 (UDF): 对于非常复杂的日期时间格式，你可以考虑编写用户自定义函数 (UDF) 来处理转换。 UDF 可以用 C 或 C++ 编写，并可以扩展 MySQL 的功能。

2. 结合使用其他字符串函数: STR_TO_DATE() 可以与其他字符串函数（例如 SUBSTRING(), REPLACE(), TRIM()）结合使用，以处理更复杂的日期字符串。

安全考虑

当处理用户提供的日期字符串时，务必小心，以避免 SQL 注入攻击。 始终对输入进行验证和转义，以防止恶意代码的执行。 如果可能，使用参数化查询或预处理语句来避免 SQL 注入风险。

例如，避免直接将用户输入插入到 STR_TO_DATE() 函数中：

-- 不安全！
SET @user_date_string = '...' ; -- 用户提供的字符串
SELECT STR_TO_DATE(@user_date_string, '%Y-%m-%d');

而应该使用参数化查询 (如果你的编程语言和数据库驱动程序支持)：

-- 安全
PREPARE stmt FROM 'SELECT STR_TO_DATE(?, "%Y-%m-%d")';
SET @date_string = '2023-10-27';
EXECUTE stmt USING @date_string;
DEALLOCATE PREPARE stmt;

不同数据库系统的差异

虽然 DATE_FORMAT() 和 STR_TO_DATE() 是 MySQL 中常用的函数，但其他数据库系统可能使用不同的函数来实现类似的功能。

例如：

• PostgreSQL: 使用 TO_CHAR() 代替 DATE_FORMAT()，使用 TO_DATE() 代替 STR_TO_DATE()。
• SQL Server: 使用 FORMAT() 代替 DATE_FORMAT()，使用 CONVERT() 或 TRY_CONVERT() 代替 STR_TO_DATE()。
• Oracle: 使用 TO_CHAR() 代替 DATE_FORMAT()，使用 TO_DATE() 代替 STR_TO_DATE()。

在跨数据库系统开发应用程序时，需要注意这些差异，并使用适当的函数或提供数据库抽象层来处理这些差异。

总结

DATE_FORMAT() 和 STR_TO_DATE() 是 MySQL 中处理日期时间字符串的强大工具。 掌握它们可以让你灵活地格式化和解析日期时间数据，并满足各种应用场景的需求。理解它们的语法、格式代码、注意事项和性能影响，可以帮助你编写更高效、更可靠的数据库应用程序。

最后：灵活处理日期时间格式，保障数据准确性

DATE_FORMAT()用于呈现，STR_TO_DATE()用于存储， 熟练运用能避免数据类型错误并提供更加友好的数据展示。在实际应用中，根据场景选择合适的格式化代码，并注意性能和安全性。