MySQL 時間範圍查詢兩大高效寫法：傳統 BETWEEN vs UNIX 時間戳優化

場景

統計每日訪問量、按小時分桶分析等「時間段」查詢極為常見。MySQL 時間類型包括 DATE、TIME、DATETIME、TIMESTAMP，儲存方式不一，但查詢邏輯相通。

方法一：傳統字符串比較（通用、直觀）

-- 查詢 2010-03-01 當天數據
SELECT COUNT(*) FROM sometable
WHERE datetime_col >= '2010-03-01 00:00:00'
  AND datetime_col <  '2010-03-02 00:00:00';

• 優點：可讀性強，無需轉換，適用於中小數據量。
• 缺點：字符串比較較慢，大表需建立索引且走索引範圍掃描。

方法二：UNIX 時間戳整數比較（高性能、大數據量首選）

核心函數

• UNIX_TIMESTAMP(datetime) → 整數秒數（從 1970-01-01 00:00:00 UTC）
• FROM_UNIXTIME(unixtime) → 還原為 DATETIME

示例

SELECT UNIX_TIMESTAMP('2010-03-01 00:00:00');  -- 1267372800
SELECT FROM_UNIXTIME(1267372800);              -- '2010-03-01 00:00:00'

查詢寫法

-- 假設表中 datetime_col 已存為 INT UNIX 時間戳
SELECT COUNT(*) FROM sometable
WHERE datetime_col >= UNIX_TIMESTAMP('2010-03-01 00:00:00')
  AND datetime_col <  UNIX_TIMESTAMP('2010-03-02 00:00:00');

或在應用層預先轉換再傳參，SQL 直接整數比較。

性能對比與選型建議

最佳實踐

1. 新建大表： 直接用 INT UNSIGNED NOT NULL 存 UNIX 時間戳，建立索引。
2. 既有表遷移： 新增 unix_ts INT 列，UPDATE SET unix_ts = UNIX_TIMESTAMP(datetime_col)，驗證後切換查詢邏輯。
3. 分區表： 以 unix_ts DIV 86400（天數）或 TO_DAYS(FROM_UNIXTIME(unix_ts)) 作為分區鍵。
4. 應用層： 統一使用 UTC 時間戳傳輸，前端按用戶時區渲染。