DATE_FORMAT(date, format) 是 MySQL 最常用的日期格式化函數,支援 60+ 格式符,靈活構建各類顯示需求。本文提供完整格式符參考表、實戰範例與效能避坑指南。
核心語法
DATE_FORMAT(date, format_string)
-- date: DATE/DATETIME/TIMESTAMP 欄位或表達式
-- format_string: 包含 % 格式符的字串模板
格式符完整分類參考表
年份相關
| 符號 | 輸出範例 | 說明 |
%Y | 2023 | 4 位數年份 |
%y | 23 | 2 位數年份 (00-99) |
月份相關
| 符號 | 輸出範例 | 說明 |
%m | 01-12 | 2 位數月份 (補零) |
%c | 1-12 | 月份 (不補零) |
%M | January | 英文月份全名 |
%b | Jan | 英文月份縮寫 |
日期相關
| 符號 | 輸出範例 | 說明 |
%d | 01-31 | 月內天數 (補零) |
%e | 1-31 | 月內天數 (不補零) |
%D | 1st, 2nd, 3rd | 英文序數後綴 |
%j | 001-366 | 年內天數 |
時間相關 (24 小時制)
| 符號 | 輸出範例 | 說明 |
%H | 00-23 | 小時 (補零) |
%k | 0-23 | 小時 (不補零) |
%i | 00-59 | 分鐘 (補零) |
%S, %s | 00-59 | 秒 (補零) |
%f | 000000-999999 | 微秒 (6 位) |
時間相關 (12 小時制)
| 符號 | 輸出範例 | 說明 |
%h, %I | 01-12 | 小時 (補零) |
%l | 1-12 | 小時 (不補零) |
%p | AM/PM | 上午/下午標記 |
%r | 02:30:45 PM | 12 小時制完整時間 |
星期相關
| 符號 | 輸出範例 | 說明 |
%W | Sunday | 星期全名 |
%a | Sun | 星期縮寫 |
%w | 0-6 | 星期索引 (0=Sunday) |
%U | 00-52 | 週數 (週日為週首) |
%u | 00-52 | 週數 (週一為週首) |
%V | 01-53 | ISO 週數 (週一為週首,首週至少 4 天) |
%v | 01-53 | ISO 週數 (週日為週首) |
特殊符號
| 符號 | 輸出 | 說明 |
%% | % | 字面百分號 |
%T | HH:MM:SS | 等同 %H:%i:%s |
%R | HH:MM | 等同 %H:%i |
%X | YYYY | ISO 週對應的年 (配合 %V) |
%x | YYYY | ISO 週對應的年 (配合 %v) |
實戰範例集錦
常用顯示格式
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2023-10-15
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d'); -- 2023/10/15
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日'); -- 2023年10月15日
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2023-10-15 14:30:45
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %r'); -- 2023-10-15 02:30:45 PM
SELECT DATE_FORMAT(NOW(), '%W, %M %D, %Y'); -- Sunday, October 15th, 2023
中文本地化格式(需應用層配合)
-- 純 SQL 難以完美實現中文「年月日時分秒」,建議應用層格式化
-- 或使用 CASE 映射星期
SELECT
DATE_FORMAT(NOW(), '%Y年%m月%d日') AS date_part,
CASE DAYOFWEEK(NOW())
WHEN 1 THEN '周日' WHEN 2 THEN '周一' WHEN 3 THEN '周二'
WHEN 4 THEN '周三' WHEN 5 THEN '周四' WHEN 6 THEN '周五'
ELSE '周六' END AS weekday,
DATE_FORMAT(NOW(), '%H时%i分%s秒') AS time_part;
報表常用:年月、年週、季度標識
SELECT
DATE_FORMAT(`dt`, '%Y%m') AS `year_month`, -- 202310
DATE_FORMAT(`dt`, '%Y-%m') AS `year_month_dash`, -- 2023-10
DATE_FORMAT(`dt`, '%Y%u') AS `year_week`, -- 202341 (週一為首)
CONCAT('Q', QUARTER(`dt`)) AS `quarter`; -- Q4
排序/分組專用:保證字典序等於時間序
-- 錯誤:%m-%d 排序會導致 10-01 排在 2-01 前面
-- 正確:ISO 8601 格式
SELECT * FROM `logs` ORDER BY DATE_FORMAT(`dt`, '%Y-%m-%d %H:%i:%s');
效能與最佳實踐
⚠️ 索引殺手:WHERE 子句中對欄位使用 DATE_FORMAT
-- 慢查詢:全表掃描
SELECT * FROM `orders` WHERE DATE_FORMAT(`created_at`, '%Y-%m') = '2023-10';
-- 快查詢:索引範圍掃描
SELECT * FROM `orders`
WHERE `created_at` >= '2023-10-01'
AND `created_at` < '2023-11-01';
✅ 正確用法:SELECT 列表、ORDER BY、GROUP BY 中使用
-- SELECT 列表格式化輸出 ✅
SELECT `id`, DATE_FORMAT(`created_at`, '%Y-%m-%d') AS `date` FROM `orders`;
-- ORDER BY 格式化排序 ✅ (但建議直接 ORDER BY 原欄位)
SELECT * FROM `orders` ORDER BY DATE_FORMAT(`created_at`, '%Y%m%d%H%i%s');
-- GROUP BY 時間分桶 ✅
SELECT DATE_FORMAT(`created_at`, '%Y-%m') AS `month`, COUNT(*)
FROM `orders` GROUP BY `month`;
版本差異與相容性
- MySQL 5.7/8.0 格式符完全一致
- MariaDB 10.6+ 新增
%Q (季度縮寫 Q1-Q4)、%q (季度數字 1-4)
%c (不補零月份) 在嚴格 SQL_MODE 下可能行為不同,建議統一用 %m
替代方案對比
| 方案 | 優點 | 缺點 | 適用場景 |
| DATE_FORMAT | 功能最全、靈活、MySQL 原生 | CPU 密集、不可用於 WHERE 索引 | 報表輸出、SELECT 列表、GROUP BY 分桶 |
| 應用層格式化 | 零資料庫負擔、支援完整 i18n、時區處理 | 需傳輸原始時間戳、增加網路負載 | API 回應、前端顯示、複雜本地化 |
| 生成列 + 索引 | 可索引、查詢效能高 | 佔用儲存、寫入開銷 | 高頻按格式化值查詢/排序場景 |
DATE_FORMAT 是報表輸出的利器,但也是索引失效的常見兇手。牢記「WHERE 禁用,SELECT/GROUP BY/ORDER BY 適度使用」,並建立「格式化下推應用層」的架構習慣,才能在功能與效能間取得平衡。