前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL中DATETIME与TIMESTAMP

MySQL中DATETIME与TIMESTAMP

作者头像
暮雨
发布2019-10-11 15:06:06
2.2K0
发布2019-10-11 15:06:06
举报
文章被收录于专栏:云端漫步

微信关注 [可回收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中是没有小数部分的)

好~下面详细介绍一下

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

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

代码语言:javascript
复制
 ---------------------------
 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的区别。

再见~

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-10-10,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 云端漫记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档