MySQL中DATETIME与TIMESTAMP

微信关注 [可回收BUG]

“温故而知新”

真理永远都不过时，今天由于工作的事情涉及到了这里，印象中只记得DATETIME类型占用8字节，TIMESTAMP类型占用4字节，心想这么久没有更新的知识万一过时了咋办，毕竟MySQL8都有了，于是翻开了MySQL的官网，决定查一查这两个字段的区别。

打开官网，扑面而来的英文字母刺的眼睛生疼，揉了揉眼睛，定心下来查一查。

官网这么大，怎么搜，想起来目前在用的是MySQL 5.7版本，那就先查查5.7版本的文档吧。

废话不多说，先把结果拿出来。

1. v5.6.4版本之前DATETIME占用8字节。
2. v5.6.4版本之前TIMESTAMP占用4字节。
3. v5.6.4版本开始DATETIME非小数时间部分仅占用5字节，如果有秒的小数部分会占用0-3个字节。
4. v5.6.4版本开始TIMESTAMP非小数部分占用4个字节，小数部分占用0-3个字节。
5. v5.6.4版本之前DATETIME是分为两部分，分别是4字节的整数存储的；TIMESTAMP是以时间戳整数的形式存储的4字节。
6. v5.6.4版本开始，DATETIME的数据结构变化较大，后面详细介绍，TIMESTAMP基本相同只是又小端序改为大端序。

（在v5.5.x中是没有小数部分的）

好～下面详细介绍一下

按官网给出的表格大概是这样的

举个例子：比如同样的 DATETIME类型的时间 “2019-07-29 17:30:33” 在v5.6.4之前就是占用8字节，从v5.6.4开始，仅占用 5字节。

下面的表格是小数部分不同精度所占用的字节数

举例：DATETIME(4)，会保存精度为4的时间，会占用5 + 2 = 7bytes，DATETIME(3)与DATETIME(4)，DATETIME(0)与DATETIME一样，只占用5字节。

由于好奇，我点开了让我眼前一亮的一个章节：

“Important Algorithms and Structures” — “重要的算法和结构” （链接在底部）

v5.6.4版本之前：

TIMESTAMP内部是以一个正整数来存储的，所以占用4字节，最小是0，转化为时间就是1970-01-01 00:00:00（UTC），而最大是2^31 – 1 转化为 UTC时间就是2038-01-19 03:14:07，如果是中国时区就是UTC+8 2038-01-19 11:14:07（TIMESTAMP会以UTC时区存储）

DATETIME内部占用8字节，以两个四字节整数组合而成的数据，假设有一个YYYY-MM-DD hh:mm:ss格式的日期，日期部分占用4字节等于YYYY*10000 + MM * 100 + DD，时间部分等于hh*10000 + mm*100 + ss。

从v5.6.4开始：

DATETIME类型发生了关键性变化，下面贴上官网原文：

 ---------------------------
 1 bit  sign           (1 = non-negative, 0= negative)
17 bits year*13+month  (year 0-9999, month 0-12)
 5 bits day            (0-31)
 5 bits hour           (0-23)
 6 bits minute         (0-59)
 6 bits second         (0-59)
 ---------------------------
 40 bits = 5 bytes

第一位是符号位，后面的day、hour、minute、second都可以理解，2^5=32 可以表示31以下的全部数字，2^6=64 可以表示59以下的数字。

最关键的就是年和月的存储方式，有的小伙伴说直接分别存储年和月不行吗？

行是行，但是不是最优，这里我们计算一下，要想最大表示9999需要多少bits，答案是14，2^13=8192，2^14=16384，所以最大9999的年需要14bits，而月份最大是12，需要4bits，2^4=16 > 12。

这样一来，年和月就要占用18bits，多占用了1bits，就是这么较真儿，1bits也不多给。

那么问题来了，这1bits是怎么省出来的？为什么官方给出的算法是year*13+month

这个可以说非常巧妙了，因为月份的范围很小，在1bits的指数增长范围内渺小的很，可以利用这个将月份和年放到一起存储，可是有个问题，放一起好放，怎么准确的拆分还原呢？

关键时刻数学起作用，月份范围是1-12，这个数据总能在一定的范围内移动，这不就是取余所具有的性质的嘛！余数总是小于除数，月份最大12，所以选择13作为除数，这就是为什么是用13乘以年加上月份，得到存储值。

最大9999 * 13 + 12 = 129999，恰好小于2^17 = 131072，其实这样完全可以最大表示到10004年，但9999以完完全全足够使用了。

这次经过查阅官网，收获了很多，而且还有很多其他内容，篇幅有限，有了新的理解再进行分享。

最后一点个人想法，5.6.4开始的版本再定义时间建议使用DATETIME，业务无要求的情况仅使用非小数部分就可以了，仅比TIMESTAMP多一个字节，但是范围要大很多，而且DATETIME相比之前存储、计算速度有了一定的提升；TIMESTAMP使用到2038年1月19号就到终点站了……

希望大家的项目到那时还存在，我的也是～～

贴出官网文档：

底层算法和结构：https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

日期时间类型概览：https://dev.mysql.com/doc/refman/5.7/en/date-and-time-type-overview.html

不仅仅是DATETIME和TIMESTAMP，还有DATE，TIME，YEAR的介绍。

PS：右上角可以选择文档版本，查看不同版本的MySQL的区别。

再见～