[[404586]]

本文轉載自微信公眾號「五月君」，作者五月君。轉載本文請聯系五月君公眾號。

日期類型是我們在數據庫操作中一個較為常見的數據類型，TIMESTAMP 類型相信使用的朋友也不少，但是你真的了解它嗎?

本文介紹在 MySQL 中使用 TIMESTAMP 類型遇到的一些潛在問題，最大時間限制相當于埋在未來的坑、因為系統的一些默認規則觸發日期自動更新、默認系統時區的性能問題，發現問題的同時，后面也推薦了一些在日期上個人認為不錯的方案，供參考。

安裝 MySQL

推薦 Docker 的方式本機安裝一個 MySQL，步驟也很簡單，如下所示，對于學習還是很方便的，已安裝的可忽略。

$ docker pull mysql 
$ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql 
$ docker exec -it mysql-test /bin/sh 
$ mysql -h localhost -u root -p 

一個埋在未來的坑

假設，未來 2038 年某天的你，執行了一條 SQL 更新了一個時間，第一次值為 '2038-01-19 03:14:07' 成功了，第二次值為 '2038-01-19 03:14:08' 報錯了說傳的值是無效的，中間僅差了一秒，看著挺正常的一個 SQL 啊!Why?

# 第 1 次更新 
$ UPDATE user SET birthday = '2038-01-19 03:14:07'  WHERE id = 1; 
Query OK, 0 rows affected (0.01 sec) 
Rows matched: 1  Changed: 0  Warnings: 0 
 
# 第 2 次更新 
$ UPDATE user SET birthday = '2038-01-19 03:14:08'  WHERE id = 1; 
 
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'birthday' at row 1 

在 MySQL 中，由于 TIMESTAMP 類型占用的空間為 4 個字節，理論上其能夠存儲最大的日期為 “2038-01-19 03:14:07”，而在 MySQL 5.6 之后占用的內存空間為 7 個字節，可以精確到毫秒、微秒，但是這個最大日期并沒有被改變。

所以我們上面多設置了一秒，就報錯了，對于系統而言，哪怕多一點也是不行的，超了就是超了。

這個限制在 MySQL 官方 11.2.2 The DATE, DATETIME, and TIMESTAMP Types[1] 也有描述：

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. 

小心 TIMESTAMP 的自動更新

假設一張表有 name、birthday 這些字段，這里的自動更新是指當你修改了表中 name 這個字段，但是最后發現 birthday 這個字段被更新為了系統的當前時間。

并且這種情況并不總是會出現，它和 MySQL 系統里的一個規則 **explicit_defaults_for_timestamp** 有關,默認情況下該參數的值為 OFF。

通過以下命令查看。

$ show variables like '%explicit_defaults_for_timestamp%'; 
+---------------------------------+-------+ 
| Variable_name                   | Value | 
+---------------------------------+-------+ 
| explicit_defaults_for_timestamp | OFF   | 
+---------------------------------+-------+ 

但是，這里容易潛在的埋一些坑，有些 MySQL 鏡像直接將這個值改為了 ON 就是禁用了功能。例如，通過上面 Docker 方式安裝的就已經禁用了該功能。

問題復現

為了復現和講解這個問題，現在我需要將這個功能給放開，使用如下命令。

$ SET @@SESSION.explicit_defaults_for_timestamp = 'ON'; 

首先，讓我們先創建一個數據庫，和一個 user 表，注意下目前對生日字段的定義為 birthday TIMESTAMP NOT NULL。

$ CREATE DATABASE test; 
$ CREATE TABLE user( 
  id BIGINT NOT NULL AUTO_INCREMENT, 
  name VARCHAR(20) NOT NULL, 
  birthday TIMESTAMP NOT NULL, 
  PRIMARY KEY (id) 
); 

執行 DESC user; 命令，查看當前的表結構，發現 birthday 字段 Extra 這一列多了一些定義，Why?

DESC user; 
+----------+-------------+------+-----+-------------------+-----------------------------------------------+ 
| Field    | Type        | Null | Key | Default           | Extra                                         | 
+----------+-------------+------+-----+-------------------+-----------------------------------------------+ 
| id       | bigint      | NO   | PRI | NULL              | auto_increment                                | 
| name     | varchar(20) | NO   |     | NULL              |                                               | 
| birthday | timestamp   | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | 
+----------+-------------+------+-----+-------------------+-----------------------------------------------+ 

這一塊有個默認的規則，當 explicit_defaults_for_timestamp 這個規則開啟時，創建表指定的 TIMESTAMP 類型的第一列，如果沒有顯示的使用 NULL 或 DEFAULT 或 ON UPDATE 聲明，表創建成功之后，會自動為我們帶上 **DEFAULT_GENERATED on update CURRENT_TIMESTAMP** 屬性聲明。對應我們的示例就是上面定義的 birthday TIMESTAMP NOT NULL。

如果設置為這樣子，意思是修改數據，會把該類型對應的字段變為數據庫當前的系統日期。

改規則下，并且一張表中僅有一個字段可以擁有該特性，如果設置兩個會報錯。

$ CREATE TABLE user( 
  birthday TIMESTAMP NOT NULL, 
  utime TIMESTAMP NOT NULL, 
); 
 
// 運行之后會得到一個 show variables like '%explicit_defaults_for_timestamp%'; 錯誤。 

往 user 表中插入一條數據。

$ INSERT INTO user(name, birthday) VALUES('Tom', NOW(6)); 

假設，目前時間點為 T1(當前 T1 的時間為 2021-01-01 06:10:27)，查看當前 user 表中的數據。

$ SELECT * FROM user; 
+----+------+---------------------+ 
| id | name | birthday            | 
+----+------+---------------------+ 
|  1 | Tom  | 2021-06-06 06:10:27 | 
+----+------+---------------------+ 

假設，目前時間點為 T2(當前 T2 的時間為 2021-01-01 06:13:06) 更新 user 表中的 name 為 Tom2，看返回結果 Changed: 1 更新是成功的。

$ UPDATE user SET name = 'Tom2' WHERE id = 1; 
Query OK, 1 row affected (0.02 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 

再次查詢，發現 birthday 字段的值被改變為了 T2 這個時間點，但是明明上面的 SQL 語句沒有寫更新 birthday 這個字段啊!Why?

$ SELECT * FROM user; 
+----+------+---------------------+ 
| id | name | birthday            | 
+----+------+---------------------+ 
|  1 | Tom2 | 2021-06-06 06:13:06 | 
+----+------+---------------------+ 

解決方案

當 explicit_defaults_for_timestamp 這個規則開啟時(其值為 OFF)，如果我們沒有對 TIMESTAMP 類型的字段顯性賦值，更新時系統會為我們默認設置為系統當前時間。

如果不清楚這個問題，查找起來簡直讓人崩潰，明明 SQL 語句沒有，還是被更新了。

大多數情況下，這并非我們想要的情況，怎么禁用?

方法一：修改系統參數

將 explicit_defaults_for_timestamp 的值修改為 'ON' 禁用掉該屬性。

正在運行的，可以使用 SET @@SESSION.explicit_defaults_for_timestamp = 'ON'; 修改。這里又一個坑，經測試驗證一旦表已創建，在設置是無效的。如果是在禁用該規則后創建的表，是可以的。

方法二：修改表結構

對于那些線上正在運行的無法修改的，總不能直接把表刪了再改吧。

當 explicit_defaults_for_timestamp 屬性為 OFF 的情況下也有兩種方法可以禁用，需要修改表結構。

// 指定該列為 NULL，例如 
$ ALTER TABLE user MODIFY birthday TIMESTAMP NULL。 
 
// 使用 DEFAULT 為該列指定一個默認值，例如 
$ ALTER TABLE user MODIFY birthday TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 

最后，根據 MYSQL 官網文檔 sysvar_explicit_defaults_for_timestamp[2] 的描述，這個非標準行為已被棄用，希望它們在未來的 MYSQL 版本中被刪除。確實挺坑的一個行為，如果不熟讀文檔，很容易踩坑。

TIMESTAMP 性能問題

TIMESTAMP 類型支持時區轉換，這個有利也有弊，當默認為操作系統時區時(time_zone=SYSTEM)，查詢系統 TIMESTAMP 類型的字段會調用系統時區做時區轉換。而這個系統時區需要加鎖來保證此時的操作系統時區沒有被修改。

當出現并發訪問時，勢必會出現資源競爭，多線程的上下文切換消耗，性能也會出現下降，下文我們做個性能測試。

查看當前的時區信息，time_zone=SYSTEM 表示此時是操作系統的時區。

$ show variables like "%time_zone%"; 
+------------------+--------+ 
| Variable_name    | Value  | 
+------------------+--------+ 
| system_time_zone | UTC    | 
| time_zone        | SYSTEM | 
+------------------+--------+ 

時區修改

MySQL 默認使用系統時區，修改方法大致分為兩種：使用 SQL 命令臨時修改，修改配置文件這種是永久修改。

# SQL 命令修改 
$ SET time_zone = 'Asia/Shanghai'; 
 
# 配置文件 
$ vim /etc/mysql/my.cnf 
default-time_zone = 'Asia/Shanghai' 

如果使用 Docker 的可以在 docker run 時修改。通過 -e TZ='Asia/Shanghai' 指定時區，但是這樣發現 雖然 SELECT NOW() 沒問題，但是執行 show variables like "%time_zone%" 命令 time_zone 還是顯示的 SYSTEM。

$ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -e TZ='Asia/Shanghai' mysql 

推薦修改文件，首先進入容器內執行 cat /etc/mysql/conf.d/mysql.cnf 命令查看默認配置，拷貝一份到自己的本機電腦，在執行 docker run 時掛載到容器內，這種方式好處是當你有多個配置需要修改時，都可以在配置文件里改。

配置文件也許是這樣的：

[mysqld] 
pid-file        = /var/run/mysqld/mysqld.pid 
socket          = /var/run/mysqld/mysqld.sock 
datadir         = /var/lib/mysql 
secure-file-priv= NULL 
default-time_zone = 'Asia/Shanghai' 
 
# Custom config should go here 
!includedir /etc/mysql/conf.d/ 

最終 docker run 命令如下：

# 注意 /${root}/mysql.cnf 這個是你本機的配置地址 
$ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /${root}/mysql.cnf:/etc/mysql/my.cnf mysql 

性能測試

MySQL 自帶了一個壓力測試工具 mysqlslap，可以模擬多個并發客戶端來對 MySQL 做壓力測試，還是挺不錯的，寫一些功能，想測試下基本的性能時還是可以用用的。

以下這個語句的意思是模擬 100 個客戶端并發，共執行 100,0000 萬次查詢。

# --number-of-queries 總的測試查詢次數 
# -c 并發量，模擬多個客戶端執行，下例模擬多個客戶端執行 “SELECT NOW()” 
# --create-schema 代表自定義的測試庫名稱，就是 MySQL 中的數據庫名稱 
$ mysqlslap -u root -p --number-of-queries=1000000 --create-schema=test -c 100 --query='SELECT NOW()' 

下面是基于 mysqlslap 做的性能測試結果，在不同的時區下，分別所耗時間，單位(秒)，很明顯系統時區耗時更長些，兩者直接的相差為 25%。這只是耗時上的差異，CPU 信息我沒有去看。還有不同的電腦，測試出來的性能差距也會有差異。

------	System	Asia/Shanghai	difference
Average number of seconds to run all queries	35.55s	28.42s	25%

日期該怎么選擇?

MySQL 中日期類型存儲通常有 3 中方案，使用 INT、TIMESTAMP、DATETIME 下面分別簡單總結下。

INT 類型

INT 類型來存儲日期類型，存儲的就是時間戳類型，例如 2021-01-01 06:10:27 的時間戳為 1609452627000。

數據庫實際存儲的是一串數字，這種好處是沒有時間上下范圍限制，性能也比 TIMESTAMP 好，但是這種性能是收效甚微的，一個不友好的問題是，當我們想查看數據做一些問題排查或數據分析時，通常不是很直觀的。

TIMESTAMP 類型

TIMESTAMP 類型在存儲時會先將本地時區時間轉換為 UTC 的時區時間，再講 UTC 時區時間轉為 4 字節 INT 類型存儲，本質是和 INT 一樣的，都是存儲為毫秒數。讀取時再次反向的轉換為時間戳 TIMESTAMP 類型，會做一些時間的格式化，看起來更直觀些。

TIMESTAMP 類型比較大的一個問題是有最大的時間限制，能夠有效存儲的時間范圍為 “'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'”，2038 年這個時間說遠也是很快的，這個是需要考慮的，別為將來埋坑。

TIMESTAMP 類型盡管 5.6 版本之后支持精確到微笑，毫秒后面 6 為，但是 2038 最大時間限制這個問題并沒有解決。

它還有一個筆者個人認為隱藏很的問題是，當你把一個字段的定義為 birthday DATETIME NOT NULL 且觸發了它的自動更新規則時，很容易掉坑里。可怕的是開發和生產環境配置不一致，這種問題前期就發現不了，除非踩過這個坑。

DATETIME 類型

DATETIME 這個類型是筆者比較推薦的，它占用 8 個字節，能存儲的精確度為微妙，聲明類型時通過 DATETIME(6) 指定。

它的時間范圍為 '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. 這個時間目前是夠我們用的了。當然，你要說我要存儲 “三國時期張飛” 什么時候出生，那這 160 年生日也是存儲不了的。

DATETIME 類型它不會存儲時區信息，當然這個問題，也不一定義非要在數據層解決不可，也不是什么大不了的問題，想做這種國際化的跨時區的，由中間層服務(Node.js 就很適合)統一解決也可。我認為這個日期類型它能解決上面我們使用 TIMESTAMP 遇到的那些問題。

修改上面 user 表結構，將日期類型統一聲明為 DATETIME 類型。

• birthday 字段由用戶自定義傳入，指定為非空，DATETIME 這樣聲明精確為秒。
• ctime 字段默認當前時間，僅在創建時指定，時間精確到微秒。
• utime 字段記錄每一次的更新時間，這個不受 explicit_defaults_for_timestamp 參數影響并且也是在我們顯示的定義了 ON UPDATE... 之后才觸發自動更新。

CREATE TABLE user( 
  id BIGINT NOT NULL AUTO_INCREMENT, 
  name VARCHAR(20) NOT NULL, 
  birthday DATETIME NOT NULL, 
  ctime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), 
  utime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), 
  PRIMARY KEY (id) 
); 

假設，我們插入一條數據，birthday 傳入 2039 年使用 DATETIME 是沒問題的，同時也可以看下 ctime、utime 時間，這個精確度也是我們定義的。

+----+------+---------------------+----------------------------+----------------------------+ 
| id | name | birthday            | ctime                      | utime                      | 
+----+------+---------------------+----------------------------+----------------------------+ 
|  1 | Tom  | 2039-01-01 22:00:28 | 2021-01-01 22:00:28.112048 | 2021-01-01 22:00:28.112048 | 
+----+------+---------------------+----------------------------+----------------------------+ 

參考資料

[1]11.2.2 The DATE, DATETIME, and TIMESTAMP Types: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

[2]sysvar_explicit_defaults_for_timestamp: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp