MySQL中date、datetime、timestamp、time、year的差別

前言

表示時間值的日期和時間類型為

DATETIME、DATE、TIMESTAMP、TIME、YEAR

。

每個時間類型有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

時間類型

類型	大小(bytes)	範圍	格式	小數點精度支援	用途
YEAR	1	1901/2155	YYYY	年份值
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	[0,6]	時間值或持續時間
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	[0,6]	混合日期和時間值
TIMESTAMP	4	1970-01-01 00:00:00/2038 結束時間是第 2147483647 秒，中原標準時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日 淩晨 03:14:07	YYYYMMDD HHMMSS	[0,6]	混合日期和時間值，時間戳

精度處理

時間精度定義

定義支援小數點精度的長度為6位（最大支援6位精度）

CREATE TABLE `test` (
  `date` date DEFAULT NULL,
  `datetime` datetime(6) DEFAULT NULL,
  `timestamp` timestamp(6) NULL DEFAULT NULL,
  `time` time(6) DEFAULT NULL,
  `year` year(4) DEFAULT NULL
)
           

時間精度插入

• 插入2位，小于定義的6位，預設對缺失位補零

  INSERT INTO test (DATETIME) VALUES (‘2021-04-01 10:10:10.12’)

  

• 插入6位，等于定義的6位，插入正常

  INSERT INTO test (DATETIME) VALUES (‘2021-04-01 10:10:10.123456’)

  

• 插入9位，大于定義的6位，插入異常

  INSERT INTO test (DATETIME) VALUES (‘2021-04-01 10:10:10.123456789’)

  

當插入時間精度時，可以按照設定的精度進行插入，不要超過精度設定否則會出現異常問題，或者可以直接使用mysql函數

now()

配合精度來擷取時間，如

now(6)

總結

• time、datetime、timestamp都支援小數點0~6的精度，year、date記錄的是年份、年月日，是以不支援小數點時間精度
• datetime、timestamp都可以用來記錄年月日時分秒，也支援最大6位的小數點精度，但是timestamp占用4位元組存儲範圍較小，從系統長久使用、健壯性以及後續維護成本來說不建議使用該類型來記錄時間，datetime占用8位元組存儲範圍較大，一般我們采用datetime來進行時間資料存儲，能滿足大部分業務場景對時間範圍、精度的最大要求

參考

MySQL TIMESTAMP 時間精度問題

MySQL Date and Time Data Type Representation

MySQL 資料類型