MySQL中「詭異」的TIMESTAMP數據類型

2021-02-20 網際網路後端架構


前    言

涉及MySQL的日常開發與運維過程中少不了和TIMESTAMP數據類型打交道，有時候TIMESTAMP類型看起來莫名其妙，測試環境都測試OK了上了預發就直接報錯了；有時候TIMESTAMP類型看起來又很詭異，表中的真實數據跟開發人員提交的表結構又南轅北轍並非所需要的；本篇文章將抽絲剝繭帶你重新認識一下這個熟悉又陌生的TIMESTAMP類型。

1、  TIMESTAMP影響因素之一—— MySQL時區簡介



1.1  GMT、UTC、CST簡介

GMT：格林尼治標準時間(Greenwich Mean Time)，簡稱GMT。是指位於英國倫敦郊區的皇家格林尼治天文臺的標準時間，格林尼治的經線被定義為本初子午線，作為計算地理的起點和世界標準"時區"的起點，即：零時區的時間。

UTC：協調世界時(Universal Time Coordinated)，簡稱UTC，又稱世界標準時間。取代GMT的新計量標準。

CST：北京時間(China Standard Time)，簡稱CST，又稱中國標準時間，即：東八區的時間。由於在時區劃分上，比協調世界時早了8個小時，固記為UTC+8


1.2  GMT和UTC的關係

GMT是之前的時間計量標準，是根據 地球自轉和公轉來計算時間，也就是太陽每天經過格林尼治天文臺的時間就是中午12點，誤差較大。

UTC是現用的時間計量標準，是根據 原子鐘來計算時間，誤差較小。

現在世界上最精確的原子鐘50億年會產生誤差1秒，可以說非常精確。而GMT因為是根據地球的轉動來計算時間的，而地球的自轉正在緩速變慢，所以使用GMT的話，總會有一天(可能很久很久)，中午12點，並不是一天太陽當頭照的時候，很可能就是早上或者晚上了。所以說UTC更加精確。當前看來兩者是約等於關係。


1.3  CST和GMT、UTC之間的關係

根據不同時區可以看到幾者之間的關係是：

CST=UTC+8小時

CST=GMT+8小時

UTC ≈GMT


1.4  舉個慄子，說說清楚：

時區的存在是因為地球上不同地方的人看到太陽升起的時間是不一樣的。假定北京人民在早上8:00看到了太陽剛剛升起，而此刻歐洲人民(假設東1區)還在夜裡，他們還需要再過7個小時才能看到太陽升起，所以此刻歐洲人民的手錶上顯示的是凌晨1:00，如果你強迫他們用北京時間那他們每天看到日出的時間就是15點。

即：東8區的北京人民的手錶顯示的8:00和東1區歐洲人民手錶顯示的1:00是相同的時刻：

"2017-06-29 08:00:00 +8:00" = "2017-06-29 01:00:00 +1:00"

     

2、 TIMESTAMP影響因素之二—— SQL_MODE簡介

SQL Mode定義了MySQL支持的SQL語法以及應該在數據上執行何種驗證檢查，常見的SQL_MODE歸類如下：


2.1 SQL語法支持類

ANSI：等同於REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE

IGNORE_SPACE：允許函數名和'(' 之間有空格

REAL_AS_FLOAT：將REAL視為FLOAT的同義詞，而不是DOUBLE的同義詞

PIPES_AS_CONCAT：將 '||' 視為字符串的連接操作符而非 '或' 運算符

ONLY_FULL_GROUP_BY：查詢欄位必須出現在聚合操作的欄位中

NO_AUTO_CREATE_USER：不自動創建用戶，即授權時候必須先創建用戶之後才可以授權

NO_TABLE_OPTIONS：SHOW CREATE TABLE 時不會輸出MySQL特有的語法部分,如ENGINE信息

ANSI_QUOTES：雙引號是識別符作用同反引號：SHOW CREATE TABLE時可以看到，UPDATE tab SET col1="a",此時的"a"會當做一個欄位名，而不是某個值

   

2.2  驗證檢查類


NO_ZERO_DATE：認為日期 '0000-00-00' 非法，跟是否設置後面的嚴格模式有關。

NO_ZERO_DATE且在嚴格模式下NO_ZERO_DATE自然滿足，INSERT和UPDATE全0不再被允許;INSERT IGNORE和UPDATE IGNORE全0被允許但是報warning

NO_ZERO_DATE且在非嚴格模式下INSERT和UPDATE和INSERT IGNORE和UPDATE IGNORE全0被允許但是報warning

NO_ZERO_IN_DATE：同NO_ZERO_DATE類似， 在年份非0的前提下，月和日是否允許為0，即2010-01-00或者2010-00-01是否合法

NO_ENGINE_SUBSTITUTION：在ALTER TABLE或CREATE TABLE時候，指定的ENGINE如果被禁用或未編譯直接拋錯

STRICT_TRANS_TABLES：嚴格模式，嚴格檢查在INSERT、UPDATE等操作出現少值或無效值該如何處理

線上常用的SQL_MODE有ANSI、STRICT_TRANS_TABLES，推薦使用嚴格模式

3、TIMESTAMP類型和DATETIME類型的比較


TypeStorage(Bytes)Minimum ValueMaximum Value是否跟時區相關是否有默認行為int4-21474836482147483647否否timestamp41970-01-01 00:00:01(UTC)2038-01-19 03:14:07(UTC)是是datetime81000-01-01 00:00:009999-12-31 23:59:59否否 

注意：從5.6.4版本開始，TIME，TIMESTAMP，DATTIME這三種類型增加了對小數秒的支持，timestamp存儲大小為4Bytes+小數部分；datetime存儲大小為5Bytes+小數部分。

詳見：

Date and Time Data Type Representation（https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html）

#驗證了TIMESTAMP類型採用INT存儲，具體差8小時原因，請查看1：時區部分
mysql> SELECT FROM_UNIXTIME(0);
+-+
| FROM_UNIXTIME(0) |
+-+
| 1970-01-01 08:00:00 |
+-+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2147483647);
+--+
| FROM_UNIXTIME(2147483647) |
+--+
| 2038-01-19 11:14:07 |
+--+
1 row in set (0.00 sec)

相同點：

a、 兩者都可以表示時間精確到秒且顯示格式都為：YYYY-MM-DD HH:MM:SS

不同點：

a、 TIMESTAMP長度4位元組而DATETIME長度8位元組，比較節省存儲空間

b、TIMESTAMP類型採用INT存儲，排序效率更高，查詢速度更快，方便計算

c、TIMESTAMP有效範圍是1970-2038，而DATETIME有效範圍是1000-9999

d、TIMESTAMP類型有默認行為，而DATETIME則沒有

e、TIMESTAMP類型會受時區的影響，而DATETIME則不會( 見案例六)

f、TIMESTAMP類型會受SQL_MODE的影響，而DATETIME則不會

4、TIMESTAMP類型的默認行為


4.1  默認行為規則如下：

a、 如果TIMESTAMP列沒有明確聲明NULL屬性，則默認是NOT NULL(如果是其他的數據類型，如果沒有明確聲明NULL屬性則默認為NULL)

b、表中的第一個TIMESTAMP欄位，如果沒有明確聲明NULL、DEFAULT、ON UPDATE會自動分配DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP屬性( 見案例一)

c、表中的第二個及以後TIMESTAMP欄位，如果沒有明確聲明NULL、DEFAULT會默認分配'0000-00-00 00:00:00'屬性( 見案例二)


4.2  使用規則如下：

     

a、在INSERT或者UPDATE語句中設置了TIMESTAMP欄位為NULL時，若該欄位允許為NULL，則結果為NULL；若該欄位不允許為NULL，則結果為當前的時間戳， 跟DEFAULT沒有關係( 見案例四)

b、在INSERT時若預設(即INSERT語句不指定該列的值)，則結果為默認值，具體值由DEFAULT決定( 見案例五)

c、若有一個欄位屬性是ON UPDATE CURRENT_TIMESTAMP，則修改該行的任何欄位都會更新此TIMESTAMP欄位為當前時間戳。

4.3  默認行為的特殊限制


a、 5.1和5.5版本每張表只能有一個DEFAULT CURRENT_TIMESTAMP類型的TIMESTAMP欄位，如果多了會報錯：there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

b、 5.6和5.7則沒有這個限制：每張表可以由多個DEFAULT CURRENT_TIMESTAMP類型的TIMESTAMP欄位

c、 參數explicit_defaults_for_timestamp(釋義:顯示指定TIMESTAMP類型)可以控制TIMESTAMP類型的默認行為，默認是OFF，即使用默認行為，不手動顯示指定

4.4  案例篇

默認行為規則+使用規則+特殊限制放在一起暈了沒？我們少廢話，上案例

案例一： 驗證了4.1-b的默認行為

mysql> #執行SQL
mysql> CREATE TABLE tab(
-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> db_create_time TIMESTAMP
-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.01 sec)

mysql> #SHOW CREATE TABLE查看
mysql> #5.5版本 & 5.6版本 & 5.7版本
mysql> SHOW CREATE TABLE tab\G
*************************** 1. row ***************************
Table: tab
Create Table: CREATE TABLE `tab` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`db_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

案例二： 驗證了4.1-bc的默認行為

mysql> #執行SQL
mysql> CREATE TABLE tab1(
-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> db_create_time TIMESTAMP,
-> db_update_time TIMESTAMP
-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.01 sec)

mysql> #SHOW CREATE TABLE查看
mysql> #5.5版本 & 5.6版本 & 5.7版本
mysql> SHOW CREATE TABLE tab1 \G
*************************** 1. row ***************************
Table: tab1
Create Table: CREATE TABLE `tab1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`db_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`db_update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

案例三： 驗證了4.3-a的特殊限制

mysql> #執行SQL
mysql> CREATE TABLE tab2(
-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> db_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> db_update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.01 sec)

mysql> #SHOW CREATE TABLE查看
mysql> #5.6版本 & 5.7版本
mysql> SHOW CREATE TABLE tab2 \G
*************************** 1. row ***************************
Table: tab2
Create Table: CREATE TABLE `tab2` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`db_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`db_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

#5.5版本報錯了(MySQL5.5版本不支持多個DEFAULT CURRENT_TIMESTAMP屬性的欄位)


ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause




案例四： 驗證了4.2-a的使用規則

#5.5版本 & 5.6版本 & 5.7版本
mysql> #執行SQL
mysql> CREATE TABLE tab3(
-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> db_create_time TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00'
-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.00 sec)


mysql> SHOW CREATE TABLE tab3 \G
*************************** 1. row ***************************
Table: tab3
Create Table: CREATE TABLE `tab3` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`db_create_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> INSERT INTO tab3 VALUES(1,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tab3;
+----+-+
| id | db_create_time |
+----+-+
| 1 | 2017-06-29 22:46:53 |
+----+-+
1 row in set (0.01 sec)

相關焦點

  • MySQL進階之路:日期類型datetime和timestamp區別在哪裡?
    一、相同點datetime和timestamp都可以表示 YYYY-MM-DD HH:MM:SS 這種年月日時分秒格式的數據。並且從MySQL5.6.4之後這兩者都可以包含秒後的小數部分，精度最高為微妙（6位）。
  • MySQL教程之MySQL數據類型詳解
    ，我們一定要為合適的列選取合適的數據類型，即到底用不用得到這種數據類型？舉個例子：一個枚舉欄位明明只有0和1兩個枚舉值，選用TINYINT就足夠了，但在開發場景下卻使用了BIGINT，這就造成了資源浪費簡單計算一下，假使該數據表中有100W數據，那麼總共浪費了700W字節也就是6.7M左右，如果更多的表這麼做了，那麼浪費的更多要知道，MySQL本質上是一個存儲，以Java為例，可以使用byte類型的地方使用了long類型問題不大，因為絕大多數的對象在程序中都是短命對象
  • mysql的datetime和timestamp的區別還挺多,需要注意細節
    timestamp默認值時not null，所以它的默認時間時CURRENT_TIMESTAMP，當行數據發生變化的時候採用的時當前時間。timestamp支持的範圍是1970-01-01 00:00:01到2038-01-19 03:14:07，在資料庫用整型的int型存儲，int型的範圍是2的32次方減1。
  • 由一條create語句的問題對比mysql和oracle中的date差別 (r7筆記第26天)
    這部分內容在MySQL官方文檔中也有說明。http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html那麼這個問題還是需要解決，怎麼解決呢。首先不可能為了這個操作先把資料庫升級到5.6那麼解決方法就有兩個。
  • MySQL--SQL的數據類型
    MySQL中定義數據欄位的類型對資料庫優化非常重要，前面介紹過Python的數據類型有6種，包含數字、字符串、列表、元組、集合、字典
  • 二面竟然被問到MySQL時間類型datetime、bigint及timestamp的查詢效率...
    問題：資料庫中可以用 datetime、bigint、timestamp 來表示時間，那麼選擇什麼類型來存儲時間比較合適呢？前期數據準備通過程序往資料庫插入 50w 數據CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `time_date` datetime NOT NULL, `time_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
  • mysql field json MySQL JSON 類型數據操作
    美麗"');-- 獲取map中某個key值，根據map中的值做為條件select obj->>'$."漫畫"' manhua,arr from test where obj->>'$."
  • MySQL Time Zone 時區問題
    時區的更改會影響MySQL時間函數的值，例如NOW(),CURDATE()等時區的更改會影響timestamp類型列的值，注意TIMESTAMP列的值從當前時區轉換為UTC用於存儲，當進行檢索的時候從UTC轉換到當前時區，所以當時區（或者會話時區）發生變化時，timestamp的值不同。
  • MySQL中按周統計數據
    =utf8mb4;初始化數據語句測試使用到的初始化數據使用如下的SQL進行初始化：/*如下SQL執行多次即可產生多條測試數據*/insertinto test(order_no, create_timestamp)
  • MySQL小知識點
    重點要說明的是timestamp和datetime欄位的區別。從表格中範圍欄位我們可以看出，timestamp是utc時間。也就是說：從這兩個特點，我們可以了解到，如果項目需要支持多時區，考慮timestamp。
  • MySQL技術內幕:時間和日期數據類型
    【IT168 技術】MySQL資料庫中有五種與日期和時間有關的數據類型，各種日期數據類型所佔空間如表2-1所示。　　在MySQL資料庫中，對日期和時間輸入格式的要求是非常寬鬆的，以下的輸入都可以視為日期類型。
  • 當Impala碰到由Hive生成的timestamp數據
    無論是寫入還是讀取數據，或者通過諸如from_unixtime()或unix_timestamp()之類的函數轉換為Unix時間戳或者從Unix時間轉換時。要將timestamp值轉換為date或者time，我們一般使用from_utc_timestamp()來進行轉換，但是對於Impala來說如果想轉換為OS的本地時區，一般你還要帶上時區參數比如CST，為了方便你也可以在Impala的配置中加上--use_local_tz_for_unix_timestamp_conversions=true。
  • 面試官:MySQL表設計要注意什麼?
    回答:在工作中表示枚舉的欄位，一般用tinyint類型。那為什麼不用enum類型呢？其次，做時間比較運算，你需要用STR_TO_DATE等函數將其轉化為時間類型，你會發現這麼寫是無法命中索引的。數據量一大，是個坑！(2)timestamp，該類型是四個字節的整數，它能表示的時間範圍為1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以後的時間，是無法用timestamp類型存儲的。
  • 新手入門MYSQL資料庫命令大全
    一、命令行連接資料庫Windows作業系統進入CMD命令行，進入mysql.exe所在目錄，運行命令mysql.exe -h主機名 -u用戶名 -p密碼注意：參數名與值之間沒有空格 ， 如：-h127.0.0.1
  • mysql大表中count()的用法以及mysql中count()的優化
    本篇文章給大家帶來的內容是關於mysql大表中count()的用法以及mysql中count()的優化，有一定的參考價值，有需要的朋友可以參考一下，希望對你有所幫助。一個單表中包含有6000w+的數據,然而你又不能拆分.需要分別統計表中有多少數據,A產品有多少,B產品有多少這幾個數據.
  • 如何將 MySQL 去重操作優化到極致?|CSDN 博文精選
    查詢語句開始前，先給變量初始化為數據中不可能出現的值，然後進入where子句從左向右判斷。先比較變量和欄位的值，再將本行created_time和item_name的值賦給變量，按created_time、item_name的順序逐行處理。
  • 如何將Oracle遷移至mysql?
    1）下載Navicat Premium版本，Navicat for mysql只支持連接mysql資料庫。如果需要支持oracle連接，還需要配置OCI，選擇工具—選項—OCI，選擇對應路徑下文件：配置完成後關閉Navicat再重新打開。
  • mysql show processlist Time為負數的思考
    ，也就是如果出現負數的時候直接顯示為0，但是官方版中沒有這樣做，可能出現負數。2、從庫單Sql線程和Worker線程其實不管單Sql線程還是Worker線程都是執行Event的，這裡的start_time將會被設置為Event header中timestamp的時間（query event/dml event），這個時間實際就是主庫命令發起的時間。
  • 在MySQL中使用XML數據—數據格式化
    +----------------------------------------------+----------------------------------------+　　注意MySQL使用的是表達式作為列的標題，為了使用列欄位名格式化結果集的標題，我們需要使用Prepared Statement，Prepared Statement是一種特殊類型的
  • 技術分享 | mysql show processlist Time 為負數的思考
    in dispatch_command (thd=0x7ffe7c000c90, com_data=0x7fffec03fd70, command=COM_QUERY) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1247可以看到這個函數沒有實參，因此 start_time