【緣起】
? ? ? 眾所周知，在進(jìn)行數(shù)據(jù)庫(kù)設(shè)計(jì)的時(shí)候，關(guān)于存儲(chǔ)到秒級(jí)的時(shí)間類(lèi)型字段，常用的有Timestamp和Datetime這2種。而這2種類(lèi)型的字段，在存儲(chǔ)格式以及存儲(chǔ)空間上，又有著很大的不同，列舉如下：
? ? ? 那么，在實(shí)際的數(shù)據(jù)庫(kù)設(shè)計(jì)中，在Timestamp和Datetime這2種秒級(jí)別的時(shí)間列類(lèi)型中，如何進(jìn)行選擇呢？讓我們看下面示例。
【行為差別】
? ? ? 為了方便對(duì)Timestamp和Datetime這2種列類(lèi)型的數(shù)據(jù)存儲(chǔ)行為進(jìn)行研究，我們創(chuàng)建如下t_timestamp_demo測(cè)試表。
? ? ? 其中，my_date為DATETIME日期類(lèi)型的列字段；create_time在新增數(shù)據(jù)時(shí)，自動(dòng)將當(dāng)前系統(tǒng)時(shí)區(qū)的時(shí)間戳，賦值給該列字段；update_time列字段，會(huì)在新增數(shù)據(jù)和當(dāng)前行數(shù)據(jù)發(fā)生修改時(shí)，自動(dòng)更新為當(dāng)前系統(tǒng)時(shí)區(qū)的時(shí)間(戳)。
? ? ? 下面，我們插入一條數(shù)據(jù)：
? ? ? 如上圖，通過(guò)只向my_data字段插入指定時(shí)間字符串'2099-12-24 18:18:18'后，該行數(shù)據(jù)的create_time和update_time字段，會(huì)自動(dòng)插入為當(dāng)前系統(tǒng)的時(shí)間(戳)（見(jiàn)上圖右下角時(shí)間）。
? ? ? 接下來(lái)，我們?cè)谛薷囊幌略摋l數(shù)據(jù)，如下：
? ? ? 如上圖，經(jīng)過(guò)對(duì)my_date字段進(jìn)行修改后（修改其它字段也可），update_time字段就會(huì)自動(dòng)更新為當(dāng)前系統(tǒng)的最新時(shí)間(戳)，這是因?yàn)樵趗pdate_time字段定義的時(shí)候，給了ON UPDATE CURRENT_TIMESTAMP的列特性。
【存儲(chǔ)限制】
? ? ? 接下來(lái)我們看下，Timestamp類(lèi)型的字段，所能夠存儲(chǔ)的時(shí)間范圍的上限和下限分別是什么，如下：
? ? ? 如上圖，我們修改了update_time的時(shí)間為'2038-01-19 11:14:07'，這是阿K在本地環(huán)境中能夠向Timestamp類(lèi)型的列字段，插入的最大的值。
? ? ? 接下來(lái)，我們?cè)俣嗉?秒，即將update_time列字段的時(shí)間，修改為'2038-01-19 11:14:08'，如下：
? ? ? 如上圖，我們看到Mysql會(huì)報(bào)錯(cuò)，說(shuō)'2038-01-19 11:14:08'這個(gè)時(shí)間值，在update_time列字段的存儲(chǔ)是不正確的，也就是說(shuō)'2038-01-19 11:14:07'，是Timestamp類(lèi)型的字段所能存儲(chǔ)的最大值了，那么為什么會(huì)是'2038-01-19 11:14:07'呢？
? ? ? 讓我們先看一下Mysql中，整數(shù)類(lèi)型的存儲(chǔ)和范圍，如下：
? ? ? 其中，Int類(lèi)型占用4個(gè)字節(jié)的空間，其有符號(hào)的存儲(chǔ)范圍為：-2147483648 ~ 2147483647。那么我們知道Timestamp類(lèi)型的字段，也是占用4個(gè)字節(jié)的存儲(chǔ)空間，那么，我們將'2038-01-19 11:14:07'換算成時(shí)間戳，如下：
? ? ? 如上圖，將'2038-01-19 11:14:07'換算成時(shí)間戳后，就是2147483647。這正好是Mysql中Int類(lèi)型字段所能存儲(chǔ)的最大值了，多1秒就會(huì)超出Int類(lèi)型的存儲(chǔ)空間大小，溢出報(bào)錯(cuò)。
? ? ? 那么Timestamp類(lèi)型的字段，所能存儲(chǔ)的時(shí)間最小值，又是多少呢？讓我們繼續(xù)往下看：
? ? ? 如上圖，我們將update_time字段修改為'1970-01-01 08:00:01'，這是阿K在本地環(huán)境所能存儲(chǔ)的Timestamp列類(lèi)型字段的最小值了。
? ? ? 再減1秒，就會(huì)報(bào)錯(cuò)，如下：
? ? ? 如上圖，我們看到'1970-01-01 08:00:00'這個(gè)時(shí)間值，在Mysql的Timestamp類(lèi)型的列字段update_time中，存儲(chǔ)是會(huì)報(bào)錯(cuò)的。也就是說(shuō)，Timestamp類(lèi)型的列字段，所能存儲(chǔ)的最小時(shí)間是'1970-01-01 08:00:01'，換算成時(shí)間戳，如下：
? ? ? 如上圖，由此可見(jiàn)，Timestamp類(lèi)型的列字段，能存儲(chǔ)的時(shí)間戳范圍，就是有符號(hào)Int類(lèi)型字段的1 ~ 2147483647，因?yàn)門(mén)imestamp列類(lèi)型字段，只能存儲(chǔ)0以上的時(shí)間戳數(shù)值。如果是unsigned無(wú)符號(hào)整數(shù)，則可以存儲(chǔ)的時(shí)間戳值為4294967295，也就是能存儲(chǔ)到2106年，如下：
? ? ? 如上圖，這可比有符號(hào)的Int類(lèi)型整數(shù)，所表示的最大時(shí)間戳2147483647(即2038年)，能多存68年呢?？上壳癟imestamp類(lèi)型的列字段，只能存儲(chǔ)到2147483647（2038年1月19日 11點(diǎn)14分07秒）。
【深究】
? ? ? 上面我們分析了Timestamp和Datetime在數(shù)據(jù)庫(kù)表中的存儲(chǔ)行為和大小限制。那么，Timestamp和Datetime究竟是如何存儲(chǔ)在數(shù)據(jù)庫(kù)表里面的呢？下面通過(guò)一個(gè)示例，來(lái)看看Timestamp和Datetime類(lèi)型的字段，在磁盤(pán)上面的表空間中，是如何存儲(chǔ)的吧。
? ? ? 如上圖，我們創(chuàng)建了2個(gè)表，t_one_datetime用來(lái)存儲(chǔ)Datetime類(lèi)型的數(shù)據(jù)，字段名為my_datetime。t_one_timestamp用來(lái)存儲(chǔ)Timestamp類(lèi)型的字段，字段名為my_timestamp。
? ? ? 接下來(lái)，我們?cè)谶@2個(gè)表中，分別插入1條數(shù)據(jù)，如下：
? ? ? 接下來(lái)，我們找到磁盤(pán)上表t_one_datetime和表t_one_timestamp的表空間文件(后綴名是.ibd)，打開(kāi)進(jìn)行對(duì)比，如下：
? ? ? 從上圖的元數(shù)據(jù)文件中，我們看到t_one_datetime表中，存儲(chǔ)的是：a7 af 32 c8 ee，這5個(gè)字節(jié)的數(shù)據(jù)。而t_one_timestamp表中，存儲(chǔ)的是：5f 90 fb 4f，這4個(gè)字節(jié)的數(shù)據(jù)。
? ? ? 那么這些數(shù)據(jù)到底代表著什么意思呢？讓我們先將t_one_timestamp里面存儲(chǔ)的數(shù)據(jù)，即：5f 90 fb 4f，轉(zhuǎn)換為十進(jìn)制數(shù)字，如下：
? ? ? 得到十進(jìn)制數(shù)字為:1603337039，將其進(jìn)行時(shí)間戳計(jì)算，如下：
? ? ? 這正好就是我們t_one_timestamp表中，my_timestamp字段存儲(chǔ)的時(shí)間'2020-10-22 11:23:59'。由此可見(jiàn)，Timestamp類(lèi)型的列字段，確實(shí)是以4字節(jié)的時(shí)間戳整數(shù)的方式存儲(chǔ)在表空間里的。
? ? ? 那么t_one_datetime中，數(shù)據(jù)：a7 af 32 c8 ee，這5個(gè)字節(jié)存儲(chǔ)的是什么東西呢？讓我們先看下Mysql官網(wǎng)上對(duì)Datetime類(lèi)型的解釋?zhuān)缦拢?/p>
? ? ? 這里我們看到，從MySQL 5.6.4開(kāi)始，Datetime的存儲(chǔ)空間變成了5個(gè)字節(jié)了(準(zhǔn)確的說(shuō)應(yīng)該是5字節(jié)+0~3個(gè)字節(jié)的FSP分秒精度），那么我們這里的5個(gè)字節(jié)數(shù)據(jù)：a7 af 32 c8 ee，是如何存儲(chǔ)的呢？我們看官網(wǎng)的解釋?zhuān)缦拢?/p>
? ? ? 如上圖，按照官網(wǎng)的解釋?zhuān)珼atetime的5個(gè)字節(jié)，總共是40個(gè)bit。將我們的數(shù)據(jù)：a7 af 32 c8 ee，轉(zhuǎn)換為比特，如下：
? ? ? 得到40位bit，為：1010 0111 1010 1111 0011 0010 1100 1000 1110 1110，將其按照上面官網(wǎng)的Datetime解釋?zhuān)M(jìn)行位對(duì)齊后，排列如下：
1 01001111010111100 11001 01100 100011 101110
轉(zhuǎn)換為表格表示，如下：
? ? ? 上面表格中，我們的數(shù)據(jù)經(jīng)過(guò)位拆分，分別計(jì)算后，得到的時(shí)間就是'3125年11月25日 12點(diǎn)35分46秒'，也就是t_one_datetime表中，my_datetime字段所存儲(chǔ)的Datetime類(lèi)型的數(shù)據(jù)'3125-11-25 12：35：46'。
? ? ? 這里我要重點(diǎn)說(shuō)明下，為什么年的存儲(chǔ)，要采用"年*13 + 月"的方式進(jìn)行存儲(chǔ)呢？
? ? ? 假設(shè)我們本次要存儲(chǔ)的時(shí)間為'9999年12月'，如果按照"年月"的格式進(jìn)行存儲(chǔ)，如下：
? ? ? 我們看到，按照"年月"的方式進(jìn)行'9999-12'的存儲(chǔ)，共占用了20個(gè)bit的空間，也就是2.5個(gè)字節(jié)(1個(gè)字節(jié)8bit)。
? ? ? 那么如果按照"年*13+月"的方式進(jìn)行存儲(chǔ)，如下：
? ? ? 如上圖，通過(guò)"年*13+月"的計(jì)算存儲(chǔ)之后，只占用了17個(gè)bit的存儲(chǔ)空間(前面000不計(jì)算在空間內(nèi))，這樣就節(jié)省了3個(gè)bit的空間，使得'9999-12-31 23:59:59'這樣的時(shí)間范圍，可以有效存儲(chǔ)在5個(gè)字節(jié)中，Datetime這樣的存儲(chǔ)方式，只比Timestamp列類(lèi)型的字段多占用了1個(gè)字節(jié)的存儲(chǔ)空間，就能存儲(chǔ)下足夠大的時(shí)間范圍了。
【FSP】
? ? ? 根據(jù)MySQL的官方文檔，我們知道在5.6.4的版本開(kāi)始，Timestamp和Datetime列類(lèi)型的字段，在原有所占字節(jié)空間的基礎(chǔ)上，都加入了1個(gè)叫做FSP(fractional-seconds precision)的分秒精度的擴(kuò)展存儲(chǔ)，如下：
? ? ? 這個(gè)分秒精度FSP的設(shè)計(jì)，主要是為保存毫秒值需求的時(shí)間字段而設(shè)計(jì)的，也就是說(shuō)，自MySQL 5.6.4開(kāi)始，我們開(kāi)始存儲(chǔ)時(shí)間，精確到秒后面的6位小數(shù)了。
? ? ? 看下面這個(gè)例子：
? ? ? 如上圖，自MySQL 5.6.4開(kāi)始，只要在Timestamp和Datetime列類(lèi)型的字段后面，加上表示FSP(分秒精度)的小數(shù)點(diǎn)位數(shù)長(zhǎng)度值，就可以存儲(chǔ)時(shí)間秒后面為0~6位長(zhǎng)度的毫秒時(shí)間了，上例中t_fsp_datetime表中，my_datetime字段的FSP長(zhǎng)度值為6，所以就能插入，表示時(shí)間秒后6位長(zhǎng)度的小數(shù)時(shí)間'2019-01-09 12:03:15.666999'。
? ? ? 那么這個(gè)FSP的位數(shù)的長(zhǎng)短，分別會(huì)占用多少存儲(chǔ)空間呢？官網(wǎng)的解釋如下：
? ? ? 可見(jiàn)，指定不同F(xiàn)SP的位數(shù)長(zhǎng)度，所占用的存儲(chǔ)空間，分別從0-3個(gè)字節(jié)不等。
【總結(jié)】
? ? ? 本篇主要針對(duì)MySQL數(shù)據(jù)庫(kù)設(shè)計(jì)中，Timestamp和Datetime這2個(gè)字段類(lèi)型，進(jìn)行了對(duì)比和分析。
? ? ? 從存儲(chǔ)限制上來(lái)說(shuō)，雖然Datetime字段類(lèi)型多占用了1些存儲(chǔ)空間，但是卻可以存儲(chǔ)足夠大的時(shí)間范圍，適應(yīng)性和可控性都Timestamp要強(qiáng)。
? ? ? 然而Timestamp字段類(lèi)型的存儲(chǔ)，幾乎就等于是Int(有符號(hào))類(lèi)型的存儲(chǔ)。這種情況下，如果有時(shí)間戳的存儲(chǔ)需求，完全可以使用Unsigned Int(無(wú)符號(hào)Int)或者Bigint類(lèi)型來(lái)進(jìn)行時(shí)間戳的存儲(chǔ)處理，這樣它不但可以充分利用Int類(lèi)型的存儲(chǔ)空間來(lái)存儲(chǔ)更長(zhǎng)的時(shí)間，同時(shí)也能夠進(jìn)行插入賦于CURRENT_TIMESTAMP的能力，從而彌補(bǔ)Timestamp字段類(lèi)型的不足。
? ? ? 從存儲(chǔ)行為上來(lái)說(shuō)，Timestamp的自動(dòng)插入能力，Datetime也是支持的，所以Timestamp的額外亮點(diǎn)，就是在于具備自動(dòng)更新的能力。但是這個(gè)能力，還要看設(shè)計(jì)者的評(píng)估，是否適合放在MySQL數(shù)據(jù)庫(kù)這里進(jìn)行更新。
? ? ? 因?yàn)樵诤芏喾植际降膱?chǎng)景中，MySQL數(shù)據(jù)庫(kù)往往與JAVA服務(wù)系統(tǒng)，不在同一個(gè)服務(wù)器節(jié)點(diǎn)上，那么可能存在MySQL的時(shí)間和JAVA服務(wù)系統(tǒng)的時(shí)間，沒(méi)有進(jìn)行NTP時(shí)間同步。而有的設(shè)計(jì)者認(rèn)為，MySQL就應(yīng)該只是負(fù)責(zé)單純存儲(chǔ)的地方，而時(shí)間戳的確認(rèn)和計(jì)算，都應(yīng)該交由服務(wù)系統(tǒng)來(lái)進(jìn)行統(tǒng)一控制管理，這樣就不會(huì)造成MySQL時(shí)間與服務(wù)系統(tǒng)時(shí)間不同步，造成的時(shí)間戳自動(dòng)更新錯(cuò)亂的問(wèn)題了。
? ? ? 在實(shí)際的數(shù)據(jù)庫(kù)設(shè)計(jì)開(kāi)發(fā)中，每位設(shè)計(jì)者的觀點(diǎn)和想法都不盡相同，都有自己的設(shè)計(jì)考量。所謂蘿卜白菜，各有所愛(ài)，關(guān)于Timestamp和Datetime的設(shè)計(jì)選擇，作為開(kāi)發(fā)人員的您，又是如何思考和決定的呢？
? ? ? 歡迎留言與阿K進(jìn)行交流討論，希望本篇文章對(duì)您有所幫助，謝謝！
【示例】
? ? ? 本文中提到的MySQL的示例文件，在阿K的Gitee中都可以找到，如下：
? ? ? https://gitee.com/Kival/mysql-work-demo
【后記】
? ? ? 關(guān)于Datetime在MySQL 5.6.4之前的版本中，占用8個(gè)字節(jié)的存儲(chǔ)空間的計(jì)算方式，官方解釋如下：
? ? ? 簡(jiǎn)單來(lái)說(shuō)，就是用了2個(gè)Int類(lèi)型的4字節(jié)空間，來(lái)存儲(chǔ)時(shí)間數(shù)據(jù)。如：3018-12-20 15:17:55，存儲(chǔ)的實(shí)際數(shù)據(jù)就是'30181220 151755'，這樣的2段4字節(jié)的Int類(lèi)型空間。所以就有了大家普遍認(rèn)知的MySQL中，Datetime比Timestamp的字段類(lèi)型，多占用1倍(4字節(jié))的存儲(chǔ)空間的說(shuō)法了。
【特別鳴謝】
特別感謝以上各位朋友，對(duì)本文中的錯(cuò)誤和用詞不當(dāng)，做出的指點(diǎn)批評(píng)。只有你們的批評(píng)指正，才能激發(fā)阿K的不斷學(xué)習(xí)與進(jìn)步，謝謝！
