MySQL VARCHAR 轉 DATE 類型排序與 Oracle 對比實操

MySQL VARCHAR 轉 DATE 類型排序與 Oracle 對比實操

業務中常遇到日期以字符串 (VARCHAR) 存儲，需按日期排序/篩選。本文記錄 MySQL STR_TO_DATE 轉換排序實戰、格式符號對照、以及 Oracle TO_DATE/TO_CHAR 對比用法。

一、場景還原

表 a，字段 class_time VARCHAR(20) 存儲格式：20170120 10:57:12 (YYYYMMDD HH:MI:SS)

二、MySQL STR_TO_DATE 轉換排序

2.1 語法結構

STR_TO_DATE(str, format)
-- str: 字符串日期
-- format: 格式字符串，需與數據實際格式完全匹配

2.2 格式符號對照表

2.3 實戰查詢：轉換後按日期倒序

SELECT 
  STR_TO_DATE(class_time, '%Y%m%d %H:%i:%s') AS dt,
  class_time
FROM a
ORDER BY dt DESC;

2.4 進階用法

• 篩選日期範圍： WHERE STR_TO_DATE(class_time, '%Y%m%d %H:%i:%s') BETWEEN '2017-01-01' AND '2017-01-31'
• 僅比較日期部分： DATE(STR_TO_DATE(...)) = '2017-01-20'
• 創建視圖/生成列 (5.7+) 避免重複轉換：

  ALTER TABLE a ADD COLUMN class_dt DATETIME 
    GENERATED ALWAYS AS (STR_TO_DATE(class_time, '%Y%m%d %H:%i:%s')) VIRTUAL;
  CREATE INDEX idx_class_dt ON a(class_dt);

三、Oracle 對比：TO_DATE / TO_CHAR

3.1 Oracle 格式模型差異

3.2 Oracle 實戰

-- VARCHAR 轉 DATE 再轉 VARCHAR (指定輸出格式)
SELECT TO_CHAR(TO_DATE(m.ma_datetime, 'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDD') 
FROM my_task m;

-- 關鍵點：TO_DATE 必須包含時分秒格式，否則報 ORA-01830
-- 錯誤示例: TO_DATE('2017-01-20', 'YYYY-MM-DD')  -- 缺少時分秒
-- 正確: TO_DATE('2017-01-20 10:57:12', 'YYYY-MM-DD HH24:MI:SS')

四、最佳實踐建議

1. 表設計階段： 日期類型字段務必使用 DATE/DATETIME/TIMESTAMP，拒絕 VARCHAR 存儲日期
2. 存量治理： 添加虛擬生成列 + 索引，逐步遷移應用層查詢
3. 跨庫遷移： 格式字符串映射表備查，ETL腳本統一轉換邏輯
4. 性能優化： 大表頻繁轉換排序極慢，必須建立函數索引/生成列索引