Mars's Blog

MySQL時間型態的毫秒與微秒

一、說明

平常用到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 建立資料表

1
2
3
4
5
6
7
8
9
10
11
12
13
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 插入測試資料

一次查詢中插入一筆

1
2
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));

一次查詢中插入二筆

1
2
3
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()

1
2
3
4
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 查詢特定微秒範圍

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

3.2 排序與去重

1
2
3
4
5
6
-- 使用微秒精度排序,避免同一秒內的排序問題
SELECT * FROM test_microsecond
ORDER BY time6 DESC;

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

四、參考