萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL DATE_FORMAT 日期格式化完全手冊:60+ 格式符、實戰範例與效能考量

DATE_FORMAT(date, format) 是 MySQL 最常用的日期格式化函數,支援 60+ 格式符,靈活構建各類顯示需求。本文提供完整格式符參考表、實戰範例與效能避坑指南。

核心語法

DATE_FORMAT(date, format_string)
-- date: DATE/DATETIME/TIMESTAMP 欄位或表達式
-- format_string: 包含 % 格式符的字串模板

格式符完整分類參考表

年份相關

符號輸出範例說明
%Y20234 位數年份
%y232 位數年份 (00-99)

月份相關

符號輸出範例說明
%m01-122 位數月份 (補零)
%c1-12月份 (不補零)
%MJanuary英文月份全名
%bJan英文月份縮寫

日期相關

符號輸出範例說明
%d01-31月內天數 (補零)
%e1-31月內天數 (不補零)
%D1st, 2nd, 3rd英文序數後綴
%j001-366年內天數

時間相關 (24 小時制)

符號輸出範例說明
%H00-23小時 (補零)
%k0-23小時 (不補零)
%i00-59分鐘 (補零)
%S, %s00-59秒 (補零)
%f000000-999999微秒 (6 位)

時間相關 (12 小時制)

符號輸出範例說明
%h, %I01-12小時 (補零)
%l1-12小時 (不補零)
%pAM/PM上午/下午標記
%r02:30:45 PM12 小時制完整時間

星期相關

符號輸出範例說明
%WSunday星期全名
%aSun星期縮寫
%w0-6星期索引 (0=Sunday)
%U00-52週數 (週日為週首)
%u00-52週數 (週一為週首)
%V01-53ISO 週數 (週一為週首,首週至少 4 天)
%v01-53ISO 週數 (週日為週首)

特殊符號

符號輸出說明
%%%字面百分號
%THH:MM:SS等同 %H:%i:%s
%RHH:MM等同 %H:%i
%XYYYYISO 週對應的年 (配合 %V)
%xYYYYISO 週對應的年 (配合 %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 適度使用」,並建立「格式化下推應用層」的架構習慣,才能在功能與效能間取得平衡。

copyright © 萬盛學電腦網 all rights reserved