MySQL中DATETIME与TIMESTAMP
MySQL中DATETIME与TIMESTAMP
“温故而知新”
真理永远都不过时，今天由于工作的事情涉及到了这里，印象中只记得DATETIME类型占用8字节，TIMESTAMP类型占用4字节，心想这么久没有更新的知识万一过时了咋办，毕竟MySQL8都有了，于是翻开了MySQL的官网，决定查一查这两个字段的区别。
打开官网，扑面而来的英文字母刺的眼睛生疼，揉了揉眼睛，定心下来查一查。
官网这么大，怎么搜，想起来目前在用的是MySQL 5.7版本，那就先查查5.7版本的文档吧。
废话不多说，先把结果拿出来。
- v5.6.4版本之前DATETIME占用8字节。
- v5.6.4版本之前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 Types | Before 5.6.4 | As of 5.6.4 |
|---|---|---|
DATETIME | 8 bytes | 5 bytes + fractional seconds storage |
TIMESTAMP | 4 bytes | 4 bytes + fractional seconds storage |
举个例子：比如同样的 DATETIME类型的时间 “2019-07-29 17:30:33” 在v5.6.4之前就是占用8字节，从v5.6.4开始，仅占用 5字节。
下面的表格是小数部分不同精度所占用的字节数
Fractional Seconds Precision | Storage 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类型发生了关键性变化，下面贴上官网原文：
---------------------------
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的区别。