MySQL 字符串轉日期排序實戰：STR_TO_DATE 解決 VARCHAR 存儲時間亂序問題

問題背景

業務表因歷史原因將日期時間存為 VARCHAR（如 '20170120 10:59:31'），直接 ORDER BY 會按字典序排序，導致 '20170120...' < '20171201...' 但 '20170120...' > '20170119...' 等邏輯錯誤。

解決方案：STR_TO_DATE 標準化轉換

-- 單表查詢並按真實時間倒序
SELECT 
    STR_TO_DATE(class_time, '%Y%m%d %H:%i:%s') AS real_time,
    *
FROM a
ORDER BY real_time DESC;

格式符號對照表

進階用法：WHERE 條件過濾

-- 查詢某時間範圍
SELECT * FROM a
WHERE STR_TO_DATE(class_time, '%Y%m%d %H:%i:%s') 
      BETWEEN '2017-01-01 00:00:00' AND '2017-01-31 23:59:59';

性能優化建議

1. 添加虛擬列（MySQL 5.7+）：ALTER TABLE a ADD real_time DATETIME GENERATED ALWAYS AS (STR_TO_DATE(class_time, '%Y%m%d %H:%i:%s')) VIRTUAL; 並建立索引。
2. 數據清洗：離線腳本批量更新 UPDATE a SET class_time = DATE_FORMAT(STR_TO_DATE(class_time, '%Y%m%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s') 統一格式，再修改列類型為 DATETIME。
3. 避免函數索引失效：WHERE STR_TO_DATE(col, ...) > ... 導致全表掃描，必須通過虛擬列或應用層轉換後查詢。