MySQL時間型態的毫秒與微秒

1. 一、說明
   1. 適用場景
   2. 效能考量
2. 二、環境建置
   1. 2.1 時間型態
   2. 2.1.1 TIMESTAMP vs DATETIME 時區差異
   3. 2.2 建立資料表
   4. 2.3 插入測試資料
3. 三、查詢範例
   1. 3.1 查詢特定微秒範圍
   2. 3.2 排序與去重
4. 四、參考

一、說明

平常用到MySQL時間欄位 DATETIME, TIMESTAMP, TIME 類型大多使用「秒」為最小單位，但其實這三種資料型態可以支援到小數下6位的精確度（微秒 microsecond）。

版本支援：MySQL 5.6.4 以上版本開始支援時間型態的微秒精度（fractional seconds）。

適用場景

微秒精度特別適合以下應用：

• 高頻交易系統：需記錄毫秒級的交易時間
• 日誌分析系統：Log 資料需要精確的時間戳記排序
• 競態條件處理：避免同一秒內多筆資料的排序問題

效能考量

• 儲存空間：不同精度的儲存需求相同（DATETIME 固定 8 bytes，TIMESTAMP 固定 4 bytes）
• 查詢效能：精度提高對查詢效能影響極小，但可作為索引提升查詢準確性
• 建議：如果資料表沒有 unique 的需求（如 Log），可以使用適當精度的時間欄位當索引

二、環境建置

2.1 時間型態

2.1.1 TIMESTAMP vs DATETIME 時區差異

• TIMESTAMP：儲存時轉換為 UTC，讀取時轉換回當前時區（受 time_zone 設定影響）
• DATETIME：不做時區轉換，儲存與讀取的值完全相同

2.2 建立資料表

CREATE TABLE `test_microsecond`(
  `pk` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `batch` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
  `time1` TIMESTAMP NULL DEFAULT NULL,
  `time2` DATETIME NULL DEFAULT NULL,
  `time3` TIMESTAMP(3) NULL DEFAULT NULL,
  `time4` DATETIME(3) NULL DEFAULT NULL,
  `time5` TIMESTAMP(6) NULL DEFAULT NULL,
  `time6` DATETIME(6) NULL DEFAULT NULL,
  `time7` DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY(`pk`),
  KEY `time6`(`time6`)
) ENGINE = InnoDB CHARSET = utf8;

欄位 time7 可採用預設值方式自動獲取內容

2.3 插入測試資料

一次查詢中插入一筆

INSERT INTO test_microsecond(`batch`, `time1`, `time2`, `time3`, `time4`, `time5`, `time6`) VALUES
(1, CURRENT_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP(3), NOW(3), CURRENT_TIMESTAMP(6), NOW(6));

一次查詢中插入二筆

INSERT INTO test_microsecond(`batch`, `time1`, `time2`, `time3`, `time4`, `time5`, `time6`) VALUES
(2, CURRENT_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP(3), NOW(3), CURRENT_TIMESTAMP(6), NOW(6)), 
(2, CURRENT_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP(3), NOW(3), CURRENT_TIMESTAMP(6), NOW(6));

CURRENT_TIMESTAMP()、NOW()取得的值皆為本次session的時間

一次查詢中插入三筆 - SYSDATE()

INSERT INTO test_microsecond(`batch`, `time1`, `time2`, `time3`, `time4`, `time5`, `time6`) VALUES
(3, CURRENT_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP(3), NOW(3), SYSDATE(6), NOW(6)), 
(3, CURRENT_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP(3), NOW(3), SYSDATE(6), NOW(6)), 
(3, CURRENT_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP(3), NOW(3), SYSDATE(6), NOW(6));

SYSDATE()取得的是系統時間，不同於CURRENT_TIMESTAMP()、NOW()取得的值皆為本次session的時間

三、查詢範例

3.1 查詢特定微秒範圍

-- 查詢特定微秒範圍的資料
SELECT * FROM test_microsecond
WHERE time6 >= '2020-11-02 20:30:00.123456'
  AND time6 < '2020-11-02 20:30:01.000000';

3.2 排序與去重

-- 使用微秒精度排序，避免同一秒內的排序問題
SELECT * FROM test_microsecond
ORDER BY time6 DESC;

-- 利用微秒精度作為唯一識別（配合其他欄位）
SELECT DISTINCT time6, batch FROM test_microsecond;

四、參考

• Data Type Storage Requirements
• 每日頭條-mysql 時間精確到毫秒微秒的處理方法