MySQL 毫秒級時間存儲方案：拆分秒與微秒、VARCHAR 全量存、哈希索引三種模式對比

MySQL 5.6.4 之前，DATETIME/TIMESTAMP 僅支援秒級精度，無法原生存儲毫秒/微秒。雖然 5.6.4+ 已支援 DATETIME(6)/TIMESTAMP(6)，但歷史版本升級成本高、或受限於舊框架，仍需兼容方案。本文介紹三種主流變通模式並對比優劣。

方案一：拆分存儲（秒部分 + 微秒部分）

利用 TIMESTAMP 存秒，MEDIUMINT 存微秒（0~999999），聯合主鍵保證唯一性。

CREATE TABLE mysql_microsecond (
  log_time_prefix TIMESTAMP NOT NULL DEFAULT 0,
  log_time_suffix MEDIUMINT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

ALTER TABLE mysql_microsecond ADD PRIMARY KEY (log_time_prefix, log_time_suffix);

-- 寫入示例
SET @a = CONVERT(CONCAT(NOW(), '.222009'), DATETIME);
INSERT INTO mysql_microsecond
SELECT DATE_FORMAT(@a, '%Y-%m-%d %H:%i:%s'), DATE_FORMAT(@a, '%f');

-- 查詢還原
SELECT log_time_prefix, log_time_suffix,
       CONCAT(log_time_prefix, '.', LPAD(log_time_suffix, 6, '0')) AS full_time
FROM mysql_microsecond;

• 優點：可直接用時間函數篩選秒級範圍，索引友好。
• 缺點：應用層需自行拼裝/拆解，增加代碼複雜度。

方案二：VARCHAR 全量存儲格式化字符串

CREATE TABLE log_time_str (
  log_time VARCHAR(26) NOT NULL,  -- '2024-05-18 12:34:56.123456'
  PRIMARY KEY (log_time)
) ENGINE=InnoDB;

• 優點：讀寫簡單，人類可讀，無需轉換。
• 缺點：無法直接做時間範圍運算，字符串比較效率低，佔用空間大（26 字節 vs 8+3 字節）。

方案三：哈希值作主鍵，原始時間另存

CREATE TABLE log_hash (
  time_hash CHAR(32) NOT NULL,        -- MD5/SHA1 時間字符串
  log_time_prefix TIMESTAMP NOT NULL,
  log_time_suffix MEDIUMINT NOT NULL,
  PRIMARY KEY (time_hash)
) ENGINE=InnoDB;

• 優點：定長主鍵，InnoDB 聚簇索引無頁分裂，寫入性能極佳。
• 缺點：查詢需先算哈希，範圍掃描不可用，適合「單點查詢、寫多讀少」場景。

對比總結表

建議選型

• MySQL 5.6.4+：直接用 DATETIME(6)/TIMESTAMP(6)，原生支援微秒，最省心。
• 舊版本且需範圍查詢：採用方案一（拆分存儲）。
• 純粹寫入日誌、極少讀取：方案三（哈希主鍵）性能最強。
• 快速原型/異構遷移：方案二（VARCHAR）最靈活。

核心原則：優先用原生類型；不可用時，根據查詢模式在「空間、範圍查詢、寫入吞吐」三角中取捨。