MySQL中DATETIME与TIMESTAMP

“温故而知新”

真理永远都不过时,今天由于工作的事情涉及到了这里,印象中只记得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中是没有小数部分的)

好~下面详细介绍一下

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

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的区别。

本文分享自微信公众号 - 可回收BUG(way-of-full-stack)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-07-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券