mysql 中datetime_MySQL中Datetime与Timestamp

“温故而知新”

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

打开官网，扑面而来的英文字母刺的眼睛生疼，揉了揉眼睛，定心下来查一查。官网这么大，怎么搜，想起来目前在用的是MySQL 5.7版本，那就先查查5.7版本的文档吧。

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

v5.6.4版本之前DateTime占用8字节，TimeStamp占用4字节。

v5.6.4版本开始DateTime非小数时间部分仅占用5字节，如果有秒的小数部分会占用0-3个字节，v5.6.4版本开始TimeStamp非小数部分占用4个字节，小数部分占用0-3个字节。

v5.6.4版本之前DateTime是分为两部分，分别是4字节的整数存储的；TimeStamp是以时间戳整数的形式存储的4字节。

v5.6.4版本开始，DateTime的数据结构变化较大，后面详细介绍，TimeStamp基本相同只是又小端序改为大端序。

(在v5.5.x中是没有小数部分的)

好～下面详细介绍一下

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

Data TypesBefore 5.6.4As of 5.6.4

8 bytes

5 bytes + fractional seconds storage

4 bytes

4 bytes + fractional seconds storage

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

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

Fractional Seconds PrecisionStorage Required

0

0 bytes

1, 2

1 byte

3, 4

2 bytes

5, 6

3 bytes

举例：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类型发生了关键性变化，下面贴上官网原文：

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

分析：

第一位是符号位，后面的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号就到终点站了……

贴出官网文档：

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

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