mysql当中时间类型是如何存储的

来源:互联网 时间:1970-01-01


mysql当中时间类型是如何存储的,有需要的朋友可以参考下。


一直很好奇时间类型在mysql中的存储形式,终于在mysql官网中找到了答案:

下面的表格描述了不同时间类型在不同数据库版本中是如何存储的。

Type

Storage before MySQL 5.6.4

Storage as of MySQL 5.6.4

YEAR

1 byte, little endian

Unchanged

DATE

3 bytes, little endian

Unchanged

TIME

3 bytes, little endian

3 bytes + fractional-seconds storage, big endian

TIMESTAMP

4 bytes, little endian

4 bytes + fractional-seconds storage, big endian

DATETIME

8 bytes, little endian

5 bytes + fractional-seconds storage, big endian

在MYSQL 5.6.4之前,日期跟时间类型是按照以下编码进行存储的:

  • YEAR:一个字节的整型

  • DATE:三个字节的整型,按照这种方式进行压缩: YYYY×16×32 + MM×32 + DD

  • TIME: 三个字节的整型,按照这种方式进行压缩:DD×24×3600 + HH×3600 + MM×60 + SS

  • TIMESTAMP:四个字节的整型,计算UTC时间,计算从('1970-01-01 00:00:00' UTC)到指定时间的秒数

  • DATETIME:八个字节,四个字节代表日志的整型,按照这种方式进行编码: YYYY×10000 + MM×100 + DD ,四个字节代表时间,按照这种方式进行编码: HH×10000 + MM×100 + SS

从5.6.4版本开始,TIME,TIMESTAMP,DATTIME这三种类型增加了对小数秒的支持,对这三种类型的存储采用了大端(big endian)的字节排序方式(为了兼容memcmp()),小数秒部分跟在非小数秒后面。(YEAR和DATE类型的存储方式不变)

  • TIME类型的非小数秒部分编码如下:
 1 bit sign (1= non-negative, 0= negative) 1 bit unused (reserved for future extensions)10 bits hour (0-838) 6 bits minute (0-59) 6 bits second (0-59) ---------------------24 bits = 3 bytes
  • TIMESTAMP 非小数秒部分的编码与 5.6.4之前的版本一样,只是字节排序方式由小端排序改为了大端排序(little endian--->big endian)

  • 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

sign标志位为1,代表整数;0禁止使用

  • 小数秒部分的编码根据小数秒的精度进行调整,如下:

FSP

Storage

0

0 bytes

1,2

1 byte

3,4

2 bytes

4,5

3 bytes

根据以上的描述,我们是不是可以推测出,在对时间字段建立索引以后,在进行索引比较时,是不是会将我们输入的时间转换为以上类型再进行比较?从而可以确定对时间类型的字段建立索引,其效率与在整型字段上建立索引的效率是一样的?还请数据库高手给与解释。



相关阅读:
Top