MySQL時間型態的毫秒與微秒

1. 一、說明
2. 二、環境建置
   1. 2.1 時間型態
   2. 2.2 建立資料表
   3. 2.3 插入測試資料
3. 三、參考

一、說明

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

如果資料表沒有 unique 的需求(如Log)，可以使用適當精度的時間欄位當索引。

二、環境建置

2.1 時間型態

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的時間

三、參考

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