1. 簡介
MySQL 中處理日期的功能，在資料庫操作中也極為重要。例如，在按日期彙總銷售資料，或搜尋過去一定期間的記錄等情境中，日期的比較是必不可少的。
本文將詳細說明 MySQL 日期操作與比較的基礎、應用範例，甚至是最佳化效能的方法。目標是讓從初學者到中級者的各個層級使用者都能受益。
2. MySQL 日期資料類型的概述
日期資料類型的種類與特徵
MySQL 中，有以下 3 種主要日期資料類型。以下將簡單說明各自的特徵。
- DATE
- 儲存內容: 年月日（
YYYY-MM-DD） - 特徵: 不包含時間資訊，因此適合用於簡單的日期管理。
- 使用範例: 生日、截止日期。
- DATETIME
- 儲存內容: 年月日與時刻（
YYYY-MM-DD HH:MM:SS） - 特徵: 包含時刻資訊，因此適合用於記錄精確的日期時間。
- 使用範例: 建立日期時間、最終更新日期時間。
- TIMESTAMP
- 儲存內容: 年月日與時刻（
YYYY-MM-DD HH:MM:SS） - 特徵: 依賴時區，因此方便不同地區之間的日期時間管理。
- 使用範例: 記錄資料、交易記錄。
資料類型的選擇標準
- 如果不需要時刻，則選擇 DATE。
- 如果想要包含時刻，則根據應用程式的時區需求選擇 DATETIME 或 TIMESTAMP。
範例查詢
以下是各資料類型的範例。
CREATE TABLE events (
event_id INT AUTO_INCREMENT PRIMARY KEY,
event_date DATE,
event_datetime DATETIME,
event_timestamp TIMESTAMP
);3. 日期的比較方法
基本比較運算子的使用方法
在 MySQL 中，使用以下運算子進行日期比較。
=（等於）
- 取得與指定日期一致的資料。
SELECT * FROM events WHERE event_date = '2025-01-01';>/<（大於/小於）
- 搜尋指定日期前後的資料。
SELECT * FROM events WHERE event_date > '2025-01-01';<=/>=（小於等於/大於等於）
- 搜尋包含指定日期的範圍。
SELECT * FROM events WHERE event_date <= '2025-01-10';使用 BETWEEN 進行範圍搜尋
BETWEEN 運算子可以使用來輕鬆指定特定範圍內的日期。
SELECT * FROM events
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';注意事項：
- 由於
BETWEEN包含範圍的起始值和結束值，請確認範圍內包含的資料沒有遺漏。
4. 日期差的計算方法
DATEDIFF 函數的使用方法
DATEDIFF()函數可以用來計算兩個日期的差（天數）。
SELECT DATEDIFF('2025-01-10', '2025-01-01') AS days_difference;結果:
- 9 天
TIMESTAMPDIFF 函數的應用
TIMESTAMPDIFF()可以用來以年、月、日、小時等任意單位計算差異。
SELECT TIMESTAMPDIFF(MONTH, '2025-01-01', '2025-12-31') AS months_difference;結果:
- 11 個月
5. 日期的加減
使用 INTERVAL 子句的日期操作
MySQL 中，使用 INTERVAL 子句，可以輕鬆對日期進行加法或減法運算。藉此，可以建立取得特定期間前後日期的查詢。
日期的加法
這是使用 INTERVAL 加日期的範例。
SELECT DATE_ADD('2025-01-01', INTERVAL 7 DAY) AS plus_seven_days;結果:2025-01-08
日期的減法
使用 INTERVAL 減日期的情況也相同。
SELECT DATE_SUB('2025-01-01', INTERVAL 1 MONTH) AS minus_one_month;結果:2024-12-01
使用範例: 提醒系統
以下是為了自動發送提醒通知而取得 7 天前事件的查詢範例。
SELECT event_name, event_date
FROM events
WHERE event_date = DATE_SUB(CURDATE(), INTERVAL 7 DAY);以目前日期為基準的計算
CURDATE(): 傳回目前的日期（年-月-日）。NOW(): 傳回目前日期時間（年-月-日 時:分:秒）。
範例: 計算從今天起一週後的日期。
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week;6. 實踐性的查詢範例
取得特定日期的記錄
取得對應指定日期的活動。
SELECT *
FROM events
WHERE event_date = '2025-01-01';日期範圍內的資料提取
這是搜尋一週期間內活動的範例。
SELECT *
FROM events
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-07';以日期為基準的彙總
這是彙總活動每月註冊件數的查詢。
SELECT MONTH(event_date) AS event_month, COUNT(*) AS total_events
FROM events
GROUP BY MONTH(event_date);結果範例：
| event_month | total_events |
|---|---|
| 1 | 10 |
| 2 | 15 |
7. 效能最佳化
利用索引進行高效能搜尋
在日期欄位設定索引後，搜尋速度會大幅提升。
索引的建立
以下是設定event_date欄位索引的 SQL。
CREATE INDEX idx_event_date ON events(event_date);確認索引的效果
使用EXPLAIN可以確認查詢的執行計劃。
EXPLAIN SELECT *
FROM events
WHERE event_date = '2025-01-01';使用函數時的注意事項
在WHERE子句中使用函數時，索引可能會無效。
壞範例
在以下查詢中，因為使用了DATE()函數，所以無法利用索引。
SELECT *
FROM events
WHERE DATE(event_date) = '2025-01-01';改善範例
建議不使用函數，直接進行比較的方法。
SELECT *
FROM events
WHERE event_date >= '2025-01-01'
AND event_date < '2025-01-02';
8. 常見問題 (FAQ)
Q1: DATE 型與 DATETIME 型的差異是什麼？
- A1:
- DATE 型：僅保存年月日（
YYYY-MM-DD）。當不需要時間資訊時使用。 - DATETIME 型：保存年月日和時間（
YYYY-MM-DD HH:MM:SS）。當需要事件精確時間點時使用。
範例：
CREATE TABLE examples (
example_date DATE,
example_datetime DATETIME
);Q2: 使用 BETWEEN 指定日期範圍時的注意事項是？
- A2:
BETWEEN包含起始日和結束日，因此如果結束日未設定時間，可能無法取得預期的資料。
範例：
-- 此查詢可能無法取得「2025-01-01 23:59:59」的資料
SELECT *
FROM events
WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31';改善方法：明確設定結束日的 23:59:59，或進行忽略時間的比較會更好。
SELECT *
FROM events
WHERE event_date >= '2025-01-01' AND event_date < '2025-02-01';Q3: 時區的差異如何處理？
- A3:
在 MySQL 中，TIMESTAMP型依賴時區。另一方面，DATETIME型作為固定值儲存，因此不受時區影響。
時區設定確認範例：
SHOW VARIABLES LIKE 'time_zone';時區變更範例：
SET time_zone = '+09:00'; -- 日本標準時 (JST)Q4: 如何取得過去 30 天的資料？
- A4:
可以組合CURDATE()或NOW()與INTERVAL，來取得過去 30 天的資料。
範例：
SELECT *
FROM events
WHERE event_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);Q5: 如何改善日期比較的效能？
- A5:
- 建立索引： 在日期欄位設定索引。
- 避免使用函數： 在
WHERE子句中使用函數可能會使索引無效，因此使用直接比較。
9. 總結
文章要點
本文詳細說明了在 MySQL 中的日期操作與比較方法。重要的要點如下。
- 日期資料型（DATE、DATETIME、TIMESTAMP）的特徵與使用區分。
- 基本的比較方法（
=、>、<、BETWEEN等）。 - 日期差的計算方法（
DATEDIFF()、TIMESTAMPDIFF()）。 - 為了提升效能的索引活用與最佳查詢設計。
希望透過本文，能夠掌握在 MySQL 中有效率地進行日期操作，並建立實用查詢的技能。
