MySQL Binlog(九)——MySQL字段存储格式——日期类型字段类型

本文继续介绍binlog中日期类型字段的存储格式。

日期型字段类型

DATE,DATETIME,TMIESTAMP类型

  • DATE

日期。支持的范围是‘1000-01-01’到‘9999-12-31’。MySQL使用’YYYY-MM-DD’格式显示DATE值。但允许使用字符串或数字给DATE列赋值。

  • DATETIME[(fsp)]

日期和时间的组合。支持的范围是‘1000-01-01 00:00:00.000000’到’9999-12-31 23:59:59.999999’。MySQL使用’YYYY-MM-DD HH:MM:SS[.fraction]’格式显示DATETIME值,但允许使用字符串或数字给DATETIME列赋值。

从MySQL5.6.4开始,一个可选的范围从0到6的fsp值可以指定秒数的精度。值为0表示没有小数部分。如果省略的话,默认精度为0.

从MySQL5.6.5开始,自动化初始和更新到当前日期时间,可以使用DATETIME列的DEFAULT和ON UPDATE定义项。

  • TIMESTAMP[(fsp)]

时间戳。范围从’1970-01-01 00:00:01.000000’UTC到’2038-01-19 03:14:07.999999’UTC。TIMESTAMP存储从纪元(‘1970-01-01 00:00:00’UTC)至今的总秒数。

TIME类型

  • TIME[(fsp)]

时间。范围从’-838:59:59.000000’到’838:59:59.000000’。MySQL使用’HH:MM:SS[.fraction]’格式显示TIME值。但允许使用字符串或数字给TIME列赋值。

从MySQL5.6.4开始,一个可选的范围从0到6的fsp值可以指定秒数的精度。值为0表示没有小数部分。如果省略的话,默认精度为0。

YEAR类型

  • YEAR[(2|4)]

两位或四位格式的年。默认是四位格式,虽然显示上YEAR(2)或YEAR(4)的格式不同,但他们具有相同的范围的值。四位格式显示为1901至2155,和0000。两位格式显示为70至69,表示1970至2069。MySQL以为YYYY或YY格式显示YEAR值,但允许使用字符串或数字赋值。

日期型数据存储格式

解析环境描述

表结构定义

CREATE TABLE `time_table` (
  `col1` date DEFAULT NULL,
  `col2` datetime DEFAULT NULL,
  `col3` datetime(3) DEFAULT NULL,
  `col4` timestamp NULL DEFAULT NULL,
  `col5` timestamp(4) NULL DEFAULT NULL,
  `col6` time DEFAULT NULL,
  `col7` time(5) DEFAULT NULL,
  `col8` year(4) DEFAULT NULL,
  `col9` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

表数据展示

root@localhost : gangshen 07:04:33> insert into time_table values('2017-12-14','2017-12-14 09:54:00','2017-12-14 09:54:00.112','2017-12-14 09:54:00','2017-12-14 09:54:00.1113','09:54:00','09:54:00.00000','2017','2017');
Query OK, 1 row affected (0.03 sec)

root@localhost : gangshen 07:04:47> select * from time_table;                                                                                                          +------------+---------------------+-------------------------+---------------------+--------------------------+----------+----------------+------+------+
| col1      | col2                | col3                    | col4                | col5                    | col6    | col7          | col8 | col9 |
+------------+---------------------+-------------------------+---------------------+--------------------------+----------+----------------+------+------+
| 2017-12-14 | 2017-12-14 09:54:00 | 2017-12-14 09:54:00.112 | 2017-12-14 09:54:00 | 2017-12-14 09:54:00.1113 | 09:54:00 | 09:54:00.00000 | 2017 | 2017 |
+------------+---------------------+-------------------------+---------------------+--------------------------+----------+----------------+------+------+
1 row in set (0.00 sec)

然后从binlog文件中拿到,对应的Table_map_event和Writes_rows_event对应的字节内容,开始解析

元数据解析

Table_map_event字节数据解析

公共头部部分省略:
4e 00 00 00 00 00 //table_id :小端存储,16进制转换为10进制为78
01 00 //flag :
08 67 61 6e 67 73 68 65 6e 00 //database_name :1个字节是字符串长度,后接一个null-terminated string 第一个字节表示字符串长度为8,后面内容将16进制转换为ascii字符为gangshen
0a 74 69 6d 65 5f 74 61 62 6c 65 00 //table_name: 1个字节是字符串长度,后接一个null-terminated string第一个字节表示字符串长度为5,后面内容将16进制转换为ascii字符为time_table
09 //columns count :packet integer类型,转换之后,数值为9 表示表中有9个字段
0a 12 12 11 11 13 13 0d 0d //column type
06 //metadata_length: packet integer类型,转换之后,数值为9,表示记录表中的metadata内容占用9个字节
00 //col2
03 //col3
00 //col4
04 //col5
00 //col6
05 //col7
ff 01 //null_bits :int((column_count + 7) / 8)个字节 一个bit表示一个字段是否可以为null
38 9b 0a 26 //checksum

从Table_map_event中可以按照上面的讲述,解析出表中所有的字段类型以及对应的元数据,按照顺序分别是:

第一个字段:0x0a=MYSQL_TYPE_DATE 无元数据

第二个字段:0x12=MYSQL_TYPE_DATETIME2 元数据0x00表示该字段的时间精度为0

第三个字段:0x12=MYSQL_TYPE_DATETIME2 元数据0x03表示该字段的时间精度为3

第四个字段:0x11=MYSQL_TYPE_TIMESTAMP2 元数据0x00表示该字段的时间精度为0

第五个字段:0x11=MYSQL_TYPE_TIMESTAMP2 元数据0x04表示该字段的时间精度为4

第六个字段:0x13=MYSQL_TYPE_TIME2 元数据0x00表示该字段的时间精度为0

第七个字段:0x13=MYSQL_TYPE_TIME2 元数据0x05表示该字段的时间精度为5

第八个字段:0x0d=MYSQL_TYPE_YEAR 无元数据

第九个字段:0x0d=MYSQL_TYPE_YEAR 无元数据

日期型数据“值”解析

Write_rows_log_event字节数据解析

公共头部部分省略
4e 00 00 00 00 00 //table_id: 小端存储,16进制转换为10进制为78
01 00 //flag:
02 00 //var header length :小端存储,16进制转换为10进制为2
09 //m_width :packet integer,表示表中字段数量
ff ff //before image: (m_width + 7) / 8字节
00 fe //bitmap_bits :表中两个字段,插入的值都不为NULL
8e c3 0f //col1
99 9e 5c 9d 80 //col2
99 9e 5c 9d 80 04 60 //col3
5a 31 d9 b8 //col4
5a 31 d9 b8 04 59 //col5
80 9d 80 //col6
80 9d 80 00 00 00 //col7
75 //col8
75 //col9
7e da 55 ce //checksum

MYSQL_TYPE_DATE字段解析

MYSQL_TYPE_DATE类型使用3个字节存储,小端存储。3个字节一共24个比特位,其中从低到高(从右到左)前5位,表示日期,接着的从低到高4位表示月份,剩余的位数表示年份。

在上面的例子中,col1的类型就是MYSQL_TYPE_DATE,所以在Write_rows_log_event中占用3个字节的内容,为0x8ec30f,因为是小端存储,所以实际顺序为0x0fc38e,3个字节一共24个比特位,从右往左,5位表示日期,4位表示月份,15位表示年份。将0x0fc38e转换成二进制是b’0000 1111 1100 0011 1000 1110’,所以day=b’01110’=14,month=b‘1100’=12,year=’0000 1111 1100 001’=2017,所以对应的字段的值是‘2017-12-14’

MYSQL_TYPE_DATETIME2字段解析

MYSQL_TYPE_DATETIME2类型使用5个字节存储,并且根据时间精度的不同,额外需要不同的字节存储数据,时间精度部分为大端存储。

年月日时分秒的内容存储在基础的5个字节中,5个字节一共40位,从左往右前18位表示年月(年份 * 13 + 月份),其中最高位表示符号,最高位为1表示是正的,为0表示是负的,接着的5位表示日期,剩余的17位中,按照顺序,5位表示时,6位表示分,6位表示秒。因为时间精度最多为6位,一个字节表示两位,所以时间精度为1或者2的时候,使用一个额外的字节表示时间精度,时间精度为3或者4的时候,使用两个额外的字节表示时间精度,时间精度为5或者6的时候,使用三个额外的字节表示时间精度。

时间精度 所需字节数
0 0
1 1
2 1
3 2
4 2
5 3
6 3

在上面的例子中,col2和col3的类型就是MYSQL_TYPE_DATETIME2,因为col2的时间精度为0,所以col2在Write_rows_log_event中占用5个字节的内容,为0x999e5c9d80,因为col3的时间精度为3,所以col3在Write_rows_log_event中占用(5+2)个字节的内容,为0x999e5c9d800460。

我们先解析col2字段的值,从上面的描述中,5个字节(40位)内容,前18位表示年月(年份 * 13+月份),5位表示日期,5位表示时,6位表示分,6位表示秒。0x999e5c9d80转换为二进制是b‘1001 1001 1001 1110 0101 1100 1001 1101 1000 0000’,年月=b’1001 1001 1001 1110 01’ ,日期=b’01110’,时=b’01001’,

分=b’110110’,秒=b’000000’。年月中最高位符号位(正数最高位为1,负数最高位为0),所以在计算数值的时候,要排除最高位的影响(将基础的5个字节减去0x8000000000),即年月值的实际二进制为b’0001 1001 1001 1110 01’,转换为10进制为26233。年=26233/13,月=26233%13,日=b’01110’=14,时=b’01001’=9,分=b’110110’=54,秒=b’000000’=0。所以col2字段的值为2017-12-14 09:54:00

接着解析col3字段值,因为col2和col3字段的基础5个字节的内容一致,所以直接跳过,解析额外的时间精度部分,时间精度部分为0x0460,因为是大端存储,所以转换为10进制结果为1120,即时间精度部分为1120。所以col3字段的值为2017-12-14 09:54:00.1120

MYSQL_TYPE_TIMESTAMP2字段解析

MYSQL_TYPE_TIMESTAMP2类型使用4个字节存储,大端存储,并且根据时间精度的不同,额外需要不同的字存储数据,时间精度部分也使用大端存储;如果时间精度为1或者2,则4个字节之外还需要1个字节存储数据;如果时间精度为3或者4,则4个字节之外还需要2个字节存储数据;如果时间精度为5或者6,则4个字节之外还需要3个字节存储数据。

时间精度 所需字节数
0 0
1 1
2 1
3 2
4 2
5 3
6 3

在上面的例子中,col4和col5的类型就是MYSQL_TYPE_TIMESTAMP2,因为col4的时间精度为0,所以col4在Write_rows_log_event中占用4个字节的内容,为0x5a31d9b8,因为col5的时间精度为4,所以col5在Write_rows_log_event中占用(4+2)=6个字节,为0x5a31d9b80459。

我们先解析col4字段的值,4个字节,大端存储,所以0x5a31d9b8转换为10进制数为1513216440,即col4的时间戳为1513216440(时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数),转换为时间为2017-12-14 09:54:00

接着解析col5字段的值,因为col4和col5字段前4个字节内容相同,故不重新解析,直接解析时间精度部分,时间精度为0x0459,因为是大端存储,所以转换为10进制为1113,即时间精度部分为1113。所以col5字段的值为2017-12-14 09:54:00.1113

MYSQL_TYPE_TIME2字段解析

MYSQL_TYPE_TIME2类型使用3个字节存储,大端存储,并且根据时间精度的不同,额外需要不同的字节存储数据,时间精度部分使用大端存储。

时分秒的内容存储在基础的3个字节中,3个字节一共24位,从左往右,前12位表示时,中间6位表示分,最后6位表示秒,额外的字节存储秒的时间精度部分。因为时间精度最多为6位,一个字节表示两位,所以时间精度为1或者2的时候,使用一个额外的字节表示时间精度,时间精度为3或者4的时候,使用两个额外的字节表示时间精度,时间精度为5或者6的时候,使用三个额外的字节表示时间精度。

时间精度 所需字节数
0 0
1 1
2 1
3 2
4 2
5 3
6 3

MYSQL_TYPE_TIME2类型字段值可以为负数。那么MYSQL_TYPE_TIME2类型是如何存储正负值的呢。

  • 没有时间精度

在没有时间精度的情况下,MYSQL_TYPE_TIME2类型使用3个字节存储时分秒内容(大端存储)。MySQL使用0x800000作为时间原点(00:00:00),比0x800000大的为正的时间值,比0x800000小的为负的时间值。即时分秒的内容是与0x800000做减法之后的绝对值。

  • 时间精度为1或者2

在时间精度为1或者2的情况下,MYSQL_TYPE_TIME2类型使用3个基础字节存储时分秒内容(大端存储),使用一个额外的字节存储时间精度。MySQL使用0x80000000作为时间原点(00:00:00.00),比0x80000000大的为正的时间值,比0x80000000小的为负的时间值,即时分秒的即时间精度的内筒是与0x80000000做减法之后的绝对值。

  • 时间精度为3或者4

在时间精度为1或者2的情况下,MYSQL_TYPE_TIME2类型使用3个基础字节存储时分秒内容(大端存储),使用两个额外的字节存储时间精度。MySQL使用0x8000000000作为时间原点(00:00:00.0000),比0x8000000000大的为正的时间值,比0x8000000000小的为负的时间值,即时分秒的即时间精度的内筒是与0x8000000000做减法之后的绝对值。

  • 时间精度为5或者6

在时间精度为1或者2的情况下,MYSQL_TYPE_TIME2类型使用3个基础字节存储时分秒内容(大端存储),使用三个额外的字节存储时间精度。MySQL使用0x800000000000作为时间原点(00:00:00.000000),比0x800000000000大的为正的时间值,比0x800000000000小的为负的时间值,即时分秒的时间精度是与0x800000000000做减法之后的绝对值。

在上面的例子中,col6和col7的类型就是MYSQL_TYPE_TIME2,因为col6的时间精度为0,所以col6在Write_rows_log_event中占用3个字节的内容,为0x809d80,因为col7的时间精度为5,所以col7在Write_rows_log_event中占用(3+3)=6个字节的内容,为0x809d80000000。

我们先解析col6的值,按照上面的描述,col6字段类型为MYSQL_TYPE_TIME2,时间精度为0,所以使用3个字节存储内容,在Write_rows_log_event中占用3个字节的内容,为0x809d80。因为时间精度为0的情况下,MySQL是以0x800000为时间原点,具体的时分秒为0x809d80与0x800000差值的绝对值=0x009d80。且0x809d80比0x800000大,所以字段值符号为正。时分秒=0x009d80=b‘0000 0000 1001 1101 1000 0000’,时=前12位=b’0000 0000 1001’=9,分=中间6位=b’1101 10’=54,秒=最后6位=b’00 0000’=0,即col6字段的值为09:54:00

接着解析col7的值,按照上面的描述,col7字段类型为MYSQL_TYPE_TIME2,时间精度为5,所以使用3+3=6个字节存储内容,在Write_rows_log_event中占用6个字节的内容,为0x809d80000000,因为时间精度为5的情况下,MySQL是以0x800000000000为时间原点,具体的时分秒以及时间精度存储为0x809d80000000与0x800000000000差值的绝对值=0x009d80000000。且0x809d80000000比0x800000000000大,所以字段值符号为正。时分秒=0x009d80=b‘0000 0000 1001 1101 1000 0000’,时=前12位=b’0000 0000 1001’=9,分=中间6位=b’1101 10’=54,秒=最后6位=b’00 0000’=0,时间精度=0x000000=0,即col7字段的值为09:54:00.00000

MYSQL_TYPE_YEAR字段解析

MYSQL_TYPE_DATA类型使用1个字节存储。表示的值是(要表示的年份-1900)

在上面的例子中,col8和col9的类型就是MYSQL_TYPE_YEAR,col8和col9都在Write_rows_log_event中占用1个字节的内容,分别为0x75和0x75,转换为10进制为117,将结果加上1900,则最终col8和col9字段的值为2017。

我来评几句
登录后评论

已发表评论数()

相关站点

+订阅
热门文章