MySQL中如何使用SQL函数格式化查询出的日期

在MySQL中处理日期格式化时，`DATE_FORMAT()` 是最直接、最可控的解决方案。不要试图用 `STR_TO_DATE()` 或隐式转换凑合——它专为输出而设计，参数明确，兼容性出色。下图能帮你快速建立直观理解：

**一句话总结：`DATE_FORMAT()` 接收日期值和格式字符串两个参数，格式符严格区分大小写，例如 `%Y` 表示四位年份，`%y` 表示两位；`%m` 代表补零月份，`%c` 代表不补零月份。该函数仅适用于 SELECT 列表或应用展示层，切勿在 WHERE 条件中套用，否则会导致索引失效。** ### DATE_FORMAT() 的基本用法与常见格式符 看看几个常用组合，你就能感受到它的灵活性： - `DATE_FORMAT(created_at, '%Y-%m-%d')` → `2024-05-21` - `DATE_FORMAT(updated_at, '%Y年%c月%d日 %H:%i')` → `2024年5月21日 14:30` - `DATE_FORMAT(NOW(), '%W, %M %e, %Y')` → `Tuesday, May 21, 2024` 注意细节：`%e` 和 `%d` 都表示日，但 `%e` 不补零（适合中文场景），`%d` 补零；`%H` 是24小时制，`%h` 是12小时制（需要配合 `%p` 显示 AM/PM）。这些差异在实际开发中容易踩坑，尤其是在跨国项目中。 ### 为什么不能用 CAST 或 CONVERT 直接转日期字符串？ 这两个函数本质上是类型转换，并非格式化工具。例如 `CAST(created_at AS CHAR)` 依赖 MySQL 默认输出格式（通常是 `YYYY-MM-DD HH:MM:SS`），无法自定义分隔符、顺序或本地化内容。更危险的是：`CONVERT(created_at, CHAR)` 在某些版本或严格模式下可能触发隐式截断或警告；而 `DATE_FORMAT()` 明确只做字符串拼接，不改变原始值语义，也不影响索引使用（只要它不包裹在 WHERE 条件的字段上）。 典型误用场景： - 想显示“2024/05/21”，却写 `REPLACE(CAST(created_at AS CHAR), '-', '/')` —— 多余且不可靠 - 在 `WHERE` 子句里写 `DATE_FORMAT(created_at, '%Y%m') = '202405'` —— 这会让索引失效，应改用 `created_at >= '2024-05-01' AND created_at < '2024-06-01'` ### 处理 NULL 值和时区偏移的注意事项 `DATE_FORMAT()` 对 `NULL` 输入直接返回 `NULL`，不会报错，但如果你希望统一显示为“未知”或空字符串，需要配合 `IFNULL()` 或 `COALESCE()`： ```sql SELECT IFNULL(DATE_FORMAT(pay_time, '%m/%d/%Y'), '—') AS formatted_date FROM orders; ``` 时区方面，`DATE_FORMAT()` 使用会话当前时区（由 `time_zone` 系统变量决定）。如果字段存储的是 UTC 时间，但你想按用户本地时间显示，先用 `CONVERT_TZ()` 转换再格式化： - `DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', '+08:00'), '%Y-%m-%d %H:%i')` - 避免在应用层重复做时区转换，也别依赖客户端系统时钟 另外，`DATE_FORMAT()` 返回的是 `CHAR` 类型，长度取决于格式字符串，MySQL 会自动估算；如果用于排序或比较，请确保逻辑合理——字符串排序和日期排序结果可能不同。 真正容易被忽略的点是：格式化永远发生在查询结果端，不影响存储或索引。别为了“看着顺眼”而在 WHERE 或 JOIN 条件里套用它，那只会拖慢查询。格式化只应该出现在 SELECT 列表或应用展示层。