MySQL VARCHAR 轉 DATE 類型實戰與 Oracle 對比指南

MySQL 方案：STR_TO_DATE 函數

將字符串按指定格式解析為 DATETIME/DATE 類型，支援格式化符號與 DATE_FORMAT 一致。

核心語法

STR_TO_DATE(str, format_mask)

實戰範例：排序 varchar 存儲的時間字符串

-- 表結構：class_time VARCHAR(20) 存 '20170101 12:00:00'
SELECT 
    STR_TO_DATE(class_time, '%Y%m%d %H:%i:%s') AS parsed_time
FROM a
ORDER BY parsed_time DESC;

常用格式符號對照表

Oracle 方案：TO_DATE + TO_CHAR 組合

-- ma_datetime VARCHAR2 存 '2024-01-01 12:00:00'
SELECT TO_CHAR(
           TO_DATE(ma_datetime, 'yyyy-MM-dd hh24:mi:ss'),
           'yyyymmdd'
       ) 
FROM my_task;

關鍵差異點

• Oracle 必須包含時分秒，否則報 ORA-01830；MySQL 可只解析日期部分
• Oracle 格式模型大小寫敏感（yyyy vs YYYY），MySQL 不敏感
• MySQL 可直接 ORDER BY STR_TO_DATE(...)，Oracle 需嵌套或用 CTE

避坑指南

1. 格式不匹配返回 NULL：STR_TO_DATE('abc', '%Y') → NULL，無報錯，需業務層校驗
2. 時區影響：解析無時區字符串時，按 time_zone 系統變量處理
3. 性能優化：高頻查詢建議添加虛擬列 dt_col DATETIME GENERATED ALWAYS AS (STR_TO_DATE(str_col, '...')) STORED 並建索引
4. 批量清洗：UPDATE t SET dt_col = STR_TO_DATE(str_col, '...') WHERE dt_col IS NULL

最佳實踐建議

• 新系統嚴禁用 VARCHAR 存日期：直接用 DATE/DATETIME/TIMESTAMP
• 歷史系統改造路徑：新增日期列 → 雙寫同步 → 切換查詢 → 刪舊列
• 跨庫遷移工具：DataX / Canal + 自定義轉換邏輯，而非手寫 SQL

類型正確，是數據質量的基石；格式統一，是系統互操作的前提。