MySQL 使用 DATE_FORMAT 修復日期格式錯誤數據

MySQL 日期格式批量修正：從 20150708 到 2015-07-08

開發測試階段常遇到日期格式不統一問題：同事造測試數據時將 VARCHAR(10) 日期欄位存為 20150708 格式，但業務邏輯期望 2015-07-08。面對數百條測試數據，逐條修改不現實，DATE_FORMAT 提供優雅解決方案。

一條 SQL 解決問題

UPDATE organ_book_resource obr
SET obr.day = DATE_FORMAT(obr.day, '%Y-%m-%d');

原理分析

DATE_FORMAT(date, format) 函數將日期/時間值按指定格式輸出。當輸入為字符串 '20150708' 時，MySQL 會嘗試隱式轉換為日期類型再格式化。由於 YYYYMMDD 是標準 ISO 格式變體，MySQL 能正確識別並轉換。

驗證效果

SELECT DATE_FORMAT('20150708', '%Y-%m-%d');
+----------------------------------------+
| DATE_FORMAT('20150708', '%Y-%m-%d')    |
+----------------------------------------+
| 2015-07-08                             |
+----------------------------------------+

替代方案對比

適用場景與注意事項

• 適用：字符串類型欄位、格式為 YYYYMMDD 或 YYYY-MM-DD 等標準格式、數據量中小
• 不適用：非標準格式（如 DD/MM/YYYY）、混合格式數據、DATE/DATETIME 類型欄位（應直接用 DATE_FORMAT 選查）
• 性能提示：大表更新建議分批次（LIMIT 1000 循環），避免長事務鎖表
• 預防為主：應用層統一使用 DATE/DATETIME 類型，入庫前格式化，避免字符串存日期

進階：異常數據檢測

更新前先檢查有多少條不符合預期格式：

SELECT day, COUNT(*)
FROM organ_book_resource
WHERE day NOT REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
GROUP BY day;

確認無誤後再執行更新，更新後再次驗證：

SELECT day FROM organ_book_resource
WHERE day NOT REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';
-- 應返回空結果集