MySQL 時間範圍查詢兩大核心方法：BETWEEN vs UNIX_TIMESTAMP 效能對比

場景定義

統計 2010-03-01 00:00:00 至 2010-03-02 00:00:00（左閉右開區間）的訪問記錄筆數。

方法一：原生日期時間比較（直觀但效能一般）

SELECT COUNT(*) FROM access_log
WHERE dt >= '2010-03-01 00:00:00'
  AND dt < '2010-03-02 00:00:00';

-- 或使用 BETWEEN（含邊界，需精確到秒）
SELECT COUNT(*) FROM access_log
WHERE dt BETWEEN '2010-03-01 00:00:00' AND '2010-03-01 23:59:59';

缺點：日期時間比較涉及字串解析、時區轉換、字符集比較，無法充分利用數值索引範圍掃描優化。

方法二：UNIX 時間戳整數比較（高效能推薦）

核心函數

UNIX_TIMESTAMP(datetime)  -- 日期時間 → 整數秒數
FROM_UNIXTIME(unixtime)   -- 整數秒數 → 日期時間

轉換演示

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

查詢寫法

-- 方案 A：SQL 內轉換（索引需建立在原 datetime 欄位，優化器可用範圍掃描）
SELECT COUNT(*) FROM access_log
WHERE dt >= UNIX_TIMESTAMP('2010-03-01 00:00:00')
  AND dt < UNIX_TIMESTAMP('2010-03-02 00:00:00');

-- 方案 B：應用層預轉換（推薦，SQL 純整數比較）
-- PHP 範例
$start = strtotime('2010-03-01 00:00:00');  // 1267372800
$end   = strtotime('2010-03-02 00:00:00');  // 1267459200
$sql = "SELECT COUNT(*) FROM access_log WHERE dt >= $start AND dt < $end";

進階：資料庫層面存儲為整數（徹底解決）

-- 表結構調整
ALTER TABLE access_log
  MODIFY dt INT UNSIGNED NOT NULL COMMENT 'UNIX timestamp',
  ADD INDEX idx_dt (dt);

-- 查詢變為純整數範圍掃描，極快
SELECT COUNT(*) FROM access_log WHERE dt BETWEEN 1267372800 AND 1267459199;

效能對比測試（百萬行測試表）

時區陷阱與最佳實踐

1. 統一用 UTC 存儲：應用層轉換用戶時區，資料庫只存 UTC 時間戳
2. 避免時區依賴函數：NOW()、SYSDATE() 受 time_zone 影響，跨時區部署易出錯
3. 索引策略：時間範圍查詢欄位必建索引，複合索引將時間欄位放前綴
4. 分區表：按時間範圍分區（RANGE COLUMNS(dt)），實現分區裁剪

常見需求快速模板

-- 今天
WHERE dt >= UNIX_TIMESTAMP(CURDATE()) AND dt < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY)

-- 最近 7 天
WHERE dt >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY))

-- 本月
WHERE dt >= UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01'))
  AND dt < UNIX_TIMESTAMP(DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y-%m-01'))

-- 任意區間（應用層傳入 $start_ts, $end_ts）
WHERE dt >= $start_ts AND dt < $end_ts

延伸閱讀

• 深度解析 MySQL 查詢快取機制
• MySQL 無重複查詢的實現
• MySQL 中 SELECT 命令的特殊用法