MySQL & MariaDB的世界

MySQL DATETIME 資料類型深度解析：時間管理與效能最佳化

資料型別
目次

1. 什麼是 MySQL 的 DATETIME？

MySQL 的 DATETIME 是一種用於同時處理日期和時間的資料類型。在資料庫中管理日期和時間對於各種應用程式來說都至關重要，例如日誌記錄或預約系統。DATETIME 型別將日期和時間儲存在單一欄位中，並且可以儲存廣泛的值。範圍從 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'，並支援微秒。

2. MySQL 日期和時間資料類型概述

2.1 處理日期和時間的資料類型

MySQL 具有以下用於處理日期和時間的資料類型：
     
  • DATE: 處理年、月、日的資料類型。範圍從 '1000-01-01' 到 '9999-12-31'。
    •  
  • TIME: 僅處理時間的資料類型。範圍從 '-838:59:59' 到 '838:59:59'。
    •  
  • DATETIME: 結合日期和時間的資料類型。範圍從 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。
    •  
  • TIMESTAMP: 儲存 UNIX 時間戳記的資料類型。範圍從 '1970-01-01 00:00:01' 到 '2038-01-19 03:14:07'。

2.2 DATETIME 與 TIMESTAMP 的差異

DATETIME 和 TIMESTAMP 相似，但有以下差異：
     
  • 時區: DATETIME 儲存不依賴於時區的固定值。另一方面，TIMESTAMP 在儲存時會轉換為 UTC，並在擷取時轉換為伺服器的當前時區。因此，DATETIME 適用於不受時區影響的日期和時間（例如：事件日期和時間），而 TIMESTAMP 適用於與伺服器時區相關的資料，例如日誌記錄。
    •  
  • 儲存格式: DATETIME 以其原始格式儲存，而 TIMESTAMP 以 UNIX 時間戳記儲存。因此，TIMESTAMP 的資料時間表示會受到伺服器時區設定的影響。

3. 如何在 MySQL 中使用 DATETIME

3.1 建立 DATETIME 欄位

要建立 DATETIME 類型的欄位，請使用以下 SQL 語法。
CREATE TABLE sample_table (
    event_time DATETIME
);
在此範例中，我們在名為 sample_table 的表格中建立了一個名為 event_time 的 DATETIME 欄位。

3.2 插入 DATETIME 值

MySQL 的 DATETIME 值可以以各種格式插入。基本格式是 'YYYY-MM-DD HH:MM:SS'。例如：
INSERT INTO sample_table (event_time) VALUES ('2024-09-16 14:30:00');
還允許以下格式：
     
  • 'YY-MM-DD HH:MM:SS': 以兩位數字指定年份的格式。
    •  
  • 'YYYYMMDDHHMMSS': 不帶分隔符號的格式。
範例:
INSERT INTO sample_table (event_time) VALUES ('24-09-16 14:30:00');
INSERT INTO sample_table (event_time) VALUES (20240916143000);
以這些格式插入的資料將正確儲存。如果年份以兩位數字指定，則 '70-99' 將轉換為 1970-1999，'00-69' 將轉換為 2000-2069。

3.3 擷取 DATETIME 值

擷取 DATETIME 值時，MySQL 預設會以 'YYYY-MM-DD HH:MM:SS' 格式顯示。例如：
SELECT event_time FROM sample_table;
此查詢將以標準格式顯示表格中 DATETIME 欄位的值。

4. 微秒的處理

4.1 DATETIME 的精確度

在 MySQL 中，DATETIME 值可以包含微秒。您可以使用 fsp 選項指定精確度，並儲存 0 到 6 範圍內的微秒。例如，要建立一個具有 3 位微秒的欄位：
CREATE TABLE precise_times (
    event_time DATETIME(3)
);
在此範例中，event_time 欄位可以儲存多達 3 位微秒。

4.2 插入包含微秒的值

要插入包含微秒的 DATETIME 值，請執行以下操作：
INSERT INTO precise_times (event_time) VALUES ('2024-09-16 14:30:00.123');
此查詢將精確地儲存包含微秒的值。插入的微秒值將被儲存而不會截斷，並且在擷取時會保留精確度。

5. DATETIME 的最佳實踐

5.1 區分使用 DATETIME 和 TIMESTAMP

     
  • 使用 DATETIME 的時機: 不依賴於時區的固定日期和時間（例如：事件開始時間、預約日期）。
    •  
  • 使用 TIMESTAMP 的時機: 與伺服器時區相關的日期時間資料（例如：資料建立時間或更新時間）。

5.2 時區管理

由於 DATETIME 沒有時區概念，因此需要在應用程式端進行時區管理。另一方面，TIMESTAMP 會自動考慮伺服器時區來儲存和擷取值，因此適用於在全球不同時區運作。

6. 常見錯誤及其避免方法

6.1 零日期和無效值

在 MySQL 中，嘗試插入無效的 DATETIME 值時，將儲存 '0000-00-00 00:00:00' 這個零日期。由於這通常不是有效的日期，因此在資料輸入時需要進行驗證，以防止插入無效值。透過實作驗證以確保輸入資料符合適當的範圍和格式，可以防止儲存零日期。

6.2 精確度的誤用

指定微秒精確度時，使用不正確的精確度可能會導致意外結果。僅在需要時才指定微秒精確度，並仔細設定 fsp 的值。例如，如果應用程式不需要秒以下的精確度，則不需要為 DATETIME 欄位設定微秒。

7. 總結

本文詳細說明了 MySQL 的 DATETIME 型別。DATETIME 是一種非常方便的資料類型，用於同時處理日期和時間，並且適用於儲存不受時區影響的值。透過了解 DATETIME 和 TIMESTAMP 之間的差異、時區處理以及微秒的使用方法，可以有效地管理資料庫中的日期時間資料。此外，具備常見錯誤及其避免方法的知識，可以維持資料的一致性和可靠性。

8. 常見問題 (FAQ)

Q1: DATETIME 和 TIMESTAMP 的主要區別是什麼？

DATETIME 儲存不依賴於時區的固定日期和時間。例如，它適用於儲存預約日期或事件日期和時間等在任何時區都不會改變的日期和時間。另一方面，TIMESTAMP 以 UTC 為基準儲存，並在擷取時轉換為伺服器的時區。它適用於依賴於伺服器時區的日期時間資料，例如日誌記錄。

Q2: 如何在 DATETIME 中儲存微秒？

在建立 DATETIME 欄位時，您可以透過指定 fsp 的值來設定微秒的精確度。例如，指定 DATETIME(3) 可以儲存多達 3 位微秒。插入時使用包含微秒的值，並以適當的格式儲存。

Q3: 我應該使用 DATETIME 還是 TIMESTAMP？

這取決於您的用途。如果您想儲存固定日期和時間，請使用 DATETIME。另一方面，對於受伺服器時區影響的日期時間資料（例如資料建立時間或更新時間），請使用 TIMESTAMP。由於 TIMESTAMP 會自動進行時區轉換，因此它適用於需要在不同時區運作的應用程式。