91网首页-91网页版-91网在线观看-91网站免费观看-91网站永久视频-91网站在线播放

和許多數(shù)據(jù)庫初學者一樣，筆者在早期學習階段也曾嘗試使用字符串（如 VARCHAR）類型來存儲日期和時間，甚至一度認為這是一種簡單直觀的方法。畢竟，'YYYY-MM-DD HH:MM:SS' 這樣的格式看起來清晰易懂。

但是，這是不正確的做法，主要會有下面兩個問題：

1、空間效率：與 MySQL 內(nèi)建的日期時間類型相比，字符串通常需要占用更多的存儲空間來表示相同的時間信息。

2、查詢與計算效率低下：

• 比較操作復雜且低效：基于字符串的日期比較需要按照字典序逐字符進行，這不僅不直觀（例如，'2024-05-01' 會小于 '2024-1-10'），而且效率遠低于使用原生日期時間類型進行的數(shù)值或時間點比較。

• 計算功能受限：無法直接利用數(shù)據(jù)庫提供的豐富日期時間函數(shù)進行運算（例如，計算兩個日期之間的間隔、對日期進行加減操作等），需要先轉(zhuǎn)換格式，增加了復雜性。

• 索引性能不佳：基于字符串的索引在處理范圍查詢（如查找特定時間段內(nèi)的數(shù)據(jù)）時，其效率和靈活性通常不如原生日期時間類型的索引。

DATETIME 和 TIMESTAMP 是 MySQL 中兩種非常常用的、用于存儲包含日期和時間信息的數(shù)據(jù)類型。它們都可以存儲精確到秒（MySQL 5.6.4+ 支持更高精度的小數(shù)秒）的時間值。那么，在實際應用中，我們應該如何在這兩者之間做出選擇呢？

下面我們從幾個關鍵維度對它們進行對比：

DATETIME 類型存儲的是字面量的日期和時間值，它本身不包含任何時區(qū)信息。當你插入一個 DATETIME 值時，MySQL 存儲的就是你提供的那個確切的時間，不會進行任何時區(qū)轉(zhuǎn)換。

這樣就會有什么問題呢？ 如果你的應用需要支持多個時區(qū)，或者服務器、客戶端的時區(qū)可能發(fā)生變化，那么使用 DATETIME 時，應用程序需要自行處理時區(qū)的轉(zhuǎn)換和解釋。如果處理不當（例如，假設所有存儲的時間都屬于同一個時區(qū)，但實際環(huán)境變化了），可能會導致時間顯示或計算上的混亂。

TIMESTAMP 和時區(qū)有關。存儲時，MySQL 會將當前會話時區(qū)下的時間值轉(zhuǎn)換成 UTC（協(xié)調(diào)世界時）進行內(nèi)部存儲。當查詢 TIMESTAMP 字段時，MySQL 又會將存儲的 UTC 時間轉(zhuǎn)換回當前會話所設置的時區(qū)來顯示。

這意味著，對于同一條記錄的 TIMESTAMP 字段，在不同的會話時區(qū)設置下查詢，可能會看到不同的本地時間表示，但它們都對應著同一個絕對時間點（UTC 時間）。這對于需要全球化、多時區(qū)支持的應用來說非常有用。

下面實際演示一下！

建表 SQL 語句：

CREATE TABLE`time_zone_test` (`id`bigint(20) NOTNULL AUTO_INCREMENT,`date_time` datetime DEFAULTNULL,`time_stamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) ENGINE=InnoDBDEFAULTCHARSET=utf8;

插入一條數(shù)據(jù)（假設當前會話時區(qū)為系統(tǒng)默認，例如 UTC+0）：

INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());

查詢數(shù)據(jù)（在同一時區(qū)會話下）：

SELECT date_time, time_stamp FROM time_zone_test;

結(jié)果：

+---------------------+---------------------+| date_time           | time_stamp          |+---------------------+---------------------+| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |+---------------------+---------------------+

現(xiàn)在，修改當前會話的時區(qū)為東八區(qū) (UTC+8):

SET time_zone = '+8:00';

再次查詢數(shù)據(jù)：

# TIMESTAMP 的值自動轉(zhuǎn)換為 UTC+8 時間+---------------------+---------------------+| date_time           | time_stamp          |+---------------------+---------------------+| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |+---------------------+---------------------+

擴展：MySQL 時區(qū)設置常用 SQL 命令

# 查看當前會話時區(qū)SELECT @@session.time_zone;# 設置當前會話時區(qū)SETtime_zone = 'Europe/Helsinki';SETtime_zone = "+00:00";# 數(shù)據(jù)庫全局時區(qū)設置SELECT @@global.time_zone;# 設置全局時區(qū)SETGLOBALtime_zone = '+8:00';SETGLOBALtime_zone = 'Europe/Helsinki';

下圖是 MySQL 日期類型所占的存儲空間（官方文檔傳送門：https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html）：

?

在 MySQL 5.6.4 之前，DateTime 和 TIMESTAMP 的存儲空間是固定的，分別為 8 字節(jié)和 4 字節(jié)。但是從 MySQL 5.6.4 開始，它們的存儲空間會根據(jù)毫秒精度的不同而變化，DateTime 的范圍是 5~8 字節(jié)，TIMESTAMP 的范圍是 4~7 字節(jié)。

TIMESTAMP 表示的時間范圍更小，只能到 2038 年：

• DATETIME：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'

• TIMESTAMP：'1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.999999' UTC

由于 TIMESTAMP 在存儲和檢索時需要進行 UTC 與當前會話時區(qū)的轉(zhuǎn)換，這個過程可能涉及到額外的計算開銷，尤其是在需要調(diào)用操作系統(tǒng)底層接口獲取或處理時區(qū)信息時。雖然現(xiàn)代數(shù)據(jù)庫和操作系統(tǒng)對此進行了優(yōu)化，但在某些極端高并發(fā)或?qū)ρ舆t極其敏感的場景下，DATETIME 因其不涉及時區(qū)轉(zhuǎn)換，處理邏輯相對更簡單直接，可能會表現(xiàn)出微弱的性能優(yōu)勢。

為了獲得可預測的行為并可能減少 TIMESTAMP 的轉(zhuǎn)換開銷，推薦的做法是在應用程序?qū)用娼y(tǒng)一管理時區(qū)，或者在數(shù)據(jù)庫連接/會話級別顯式設置 time_zone 參數(shù)，而不是依賴服務器的默認或操作系統(tǒng)時區(qū)。

除了上述兩種類型，實踐中也常用整數(shù)類型（INT 或 BIGINT）來存儲所謂的“Unix 時間戳”（即從 1970 年 1 月 1 日 00:00:00 UTC 起至目標時間的總秒數(shù)，或毫秒數(shù)）。

這種存儲方式的具有 TIMESTAMP 類型的所具有一些優(yōu)點，并且使用它的進行日期排序以及對比等操作的效率會更高，跨系統(tǒng)也很方便，畢竟只是存放的數(shù)值。缺點也很明顯，就是數(shù)據(jù)的可讀性太差了，你無法直觀的看到具體時間。

時間戳的定義如下：

數(shù)據(jù)庫中實際操作：

-- 將日期時間字符串轉(zhuǎn)換為 Unix 時間戳 (秒)mysql> SELECTUNIX_TIMESTAMP('2020-01-11 09:53:32');+---------------------------------------+| UNIX_TIMESTAMP('2020-01-11 09:53:32') |+---------------------------------------+|                            1578707612 |+---------------------------------------+1 row in set (0.00 sec)-- 將 Unix 時間戳 (秒) 轉(zhuǎn)換為日期時間格式mysql> SELECT FROM_UNIXTIME(1578707612);+---------------------------+| FROM_UNIXTIME(1578707612) |+---------------------------+| 2020-01-11 09:53:32       |+---------------------------+1 row in set (0.01 sec)

由于有讀者提到 PostgreSQL（PG） 的時間類型，因此這里拓展補充一下。PG 官方文檔對時間類型的描述地址：https://www.postgresql.org/docs/current/datatype-datetime.html。

PostgreSQL 時間類型總結(jié)

可以看到，PG 沒有名為 DATETIME 的類型：

• PG 的 TIMESTAMP WITHOUT TIME ZONE在功能上最接近 MySQL 的 DATETIME。它存儲日期和時間，但不包含任何時區(qū)信息，存儲的是字面值。

• PG 的TIMESTAMP WITH TIME ZONE (或 TIMESTAMPTZ) 相當于 MySQL 的 TIMESTAMP。它在存儲時會將輸入值轉(zhuǎn)換為 UTC，并在檢索時根據(jù)當前會話的時區(qū)進行轉(zhuǎn)換顯示。

對于絕大多數(shù)需要記錄精確發(fā)生時間點的應用場景，TIMESTAMPTZ是 PostgreSQL 中最推薦、最健壯的選擇，因為它能最好地處理時區(qū)復雜性。

MySQL 中時間到底怎么存儲才好？DATETIME?TIMESTAMP?還是數(shù)值時間戳？

并沒有一個銀彈，很多程序員會覺得數(shù)值型時間戳是真的好，效率又高還各種兼容，但是很多人又覺得它表現(xiàn)的不夠直觀。

《高性能 MySQL 》這本神書的作者就是推薦 TIMESTAMP，原因是數(shù)值表示時間不夠直觀。下面是原文：

?

每種方式都有各自的優(yōu)勢，根據(jù)實際場景選擇最合適的才是王道。下面再對這三種方式做一個簡單的對比，以供大家實際開發(fā)中選擇正確的存放時間的數(shù)據(jù)類型：

選擇建議小結(jié)：

• TIMESTAMP 的核心優(yōu)勢在于其內(nèi)建的時區(qū)處理能力。數(shù)據(jù)庫負責 UTC 存儲和基于會話時區(qū)的自動轉(zhuǎn)換，簡化了需要處理多時區(qū)應用的開發(fā)。如果應用需要處理多時區(qū)，或者希望數(shù)據(jù)庫能自動管理時區(qū)轉(zhuǎn)換，TIMESTAMP 是自然的選擇（注意其時間范圍限制，也就是 2038 年問題）。

• 如果應用場景不涉及時區(qū)轉(zhuǎn)換，或者希望應用程序完全控制時區(qū)邏輯，并且需要表示 2038 年之后的時間，DATETIME 是更穩(wěn)妥的選擇。

• 如果極度關注比較性能，或者需要頻繁跨系統(tǒng)傳遞時間數(shù)據(jù)，并且可以接受可讀性的犧牲（或總是在應用層轉(zhuǎn)換），數(shù)值時間戳是一個強大的選項。