表 jz_user 有 create_time INT UNSIGNED 存儲 Unix 時間戳,需查詢「2013-04-01 00:00:00 到 2013-04-30 23:59:59」創建的用戶。
| 寫法 | SQL 示例 | 能否用索引 | 說明 |
|---|---|---|---|
| 函數包裹列(最差) | WHERE DATE_FORMAT(FROM_UNIXTIME(create_time),'%Y-%m-%d') BETWEEN '2013-04-01' AND '2013-04-30' | ❌ 全表掃描 | 列上套函數導致索引失效 |
| 隱式轉換 | WHERE create_time BETWEEN '2013-04-01' AND '2013-04-30' | ❌ 字符串轉數字,可能全掃 | 類型不匹配觸發隱式 CAST |
| UNIX_TIMESTAMP 轉換常量(推薦) | WHERE create_time BETWEEN UNIX_TIMESTAMP('2013-04-01 00:00:00') AND UNIX_TIMESTAMP('2013-04-30 23:59:59') | ✅ 索引範圍掃描 | 常量預計算,列無運算,Sargable |
| 直接寫數字(最快) | WHERE create_time BETWEEN 1364745600 AND 1367337599 | ✅ 索引範圍掃描 | 無任何函數調用,極致性能 |
| >= 和 < 組合(最佳實踐) | WHERE create_time >= 1364745600 AND create_time < 1367424000 | ✅ 索引範圍掃描 | 避免 BETWEEN 邊界歧義,含首尾精確控制 |
UNIX_TIMESTAMP()、STR_TO_DATE()、DATE() 等均在常量上執行。BETWEEN 包含邊界,時間範圍查詢推薦 >= start AND < end_of_next_day,避免毫秒級數據遺漏/重複。| 函數 | 返回類型 | 示例 |
|---|---|---|
NOW() | DATETIME | 2024-01-15 14:30:00 |
CURRENT_DATE | DATE | 2024-01-15 |
UNIX_TIMESTAMP() | INT | 1705309800 (當前時間戳) |
UNIX_TIMESTAMP('2024-01-15') | INT | 1705248000 (指定日期 00:00:00) |
FROM_UNIXTIME(ts) | DATETIME | 2024-01-15 14:30:00 |
TO_DAYS('2024-01-15') | INT | 738532 (從年份 0 起的天數) |
DATE_SUB(NOW(), INTERVAL 30 DAY) | DATETIME | 30 天前 |
-- 假設文章表 article(view_count INT, create_time INT)
SELECT * FROM article
WHERE create_time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
ORDER BY view_count DESC
LIMIT 10;
DATE_SUB(NOW(), INTERVAL 30 DAY) 計算 30 天前 00:00:00,UNIX_TIMESTAMP() 轉為時間戳常量。create_time 直接比較,用上索引。WHERE YEAR(create_time)=2024 → 全表掃描。改用 WHERE create_time >= UNIX_TIMESTAMP('2024-01-01') AND create_time < UNIX_TIMESTAMP('2025-01-01')。WHERE create_time > '2024-01-01' 依賴 SQL 模式 NO_ZERO_DATE 等,行為不確定,永遠顯式用 UNIX_TIMESTAMP()。time_zone、應用層、JVM/容器時區統一,避免時間戳偏移。MySQL 日期範圍查詢核心:**常量側做轉換,列側保持原樣**。對時間戳整數列,直接用 BETWEEN 或 >= AND < 配合 UNIX_TIMESTAMP() 預計算常量,配合索引實現毫秒級千萬行範圍查詢。