MySQL:datetime的存储方式和解析

问题

最近处理一个死锁问题的时候，看到datetime的在show engine中看到的格式大概如下，

• 0: len 5; hex 99b0940000; asc

那么如何将其变为日期格式的可读数据呢？下面就来看看如何解析。

相关解析

关于datetime 官方文档有如下描述，

image.png

很显然这里用的5字节来存储一个没有小数秒的datetime类型，我们看看hex 99b0940000刚好也就是5个字节。我们可以简单做一个表插入数据如下：

mysql> select * from dt1;
+------+---------------------+
| id   | dt                  |
+------+---------------------+
|    1 | 2023-07-19 14:05:05 |
+------+---------------------+

然后做一个所堵塞看到的信息大概如下：

------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3821 page no 4 n bits 72 index GEN_CLUST_INDEX of table `testdb`.`dt1` trx id 2920114 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 6; hex 000000cb8a08; asc       ;;
 1: len 6; hex 0000002c8eac; asc    ,  ;;
 2: len 7; hex 02000003040381; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 5; hex 99b0a6e145; asc     E;; --这里就是datetime实际存储的数据

那么这里的99b0a6e145就是我们的数据2023-07-19 14:05:05，它的转换规则实际上为如下：

| Bits  | Field         | Value |
| ----: | :----         | :---- |
|    1  | sign          |(used when on disk) |
|   17  | year*13+month |(year 0-9999, month 0-12) |
|    5  | day           |(0-31)|
|    5  | hour          |(0-23)|
|    6  | minute        |(0-59)|
|    6  | second        |(0-59)|
|   24  | microseconds  |(0-999999)|

也比较简单，对于微妙而言是用3字节直接表示的，因此如果带了微妙datetime依旧为8字节存储，那么我们先将99b0a6e145扩展为一个8字节的long long类型，二进制大概如下：

1001 1001 1011 0000 1010 0110 1110 0001 0100 0101 0000 0000 0000 0000 0000 0000

因为我们这里没有微妙因此后面3个字节补0即可，然后根据公式转换过来，而最高位1我们可以直接忽略掉，那么得到的有效数据解析如下：

• 年和月：bin 001 1001 1011 0000 10 = dec 26306
  年：26306/13 = 2023
  月：mod(26306,13) = 7
  很显然这里月不可能大于12,因此年乘以一个大于12的最小数字13,按照公式取余就很容易得到月份
• 日：bin 10011 = dec 19
• 时：bin 01110 = dec 14
• 分：bin 000101 = dec 5
• 秒：bin 000101 = dec 5

那么组合起来实际上就是2023-07-19 14:05:05 这也就是实际的数据。当然如果包含了微妙部分这存储依旧为8字节，比如99b0a7585503b6aa，代表后面3个字节就是bin 03b6aa，dec 就是243370，而我们的数据为

mysql> select * from dt2;
+------+----------------------------+
| id   | dt                         |
+------+----------------------------+
|    1 | 2023-07-19 21:33:21.243370 |
+------+----------------------------+

这样就完成了解析。

简单接口记录

接口部分就给出函数就可以了，有兴趣可以自行查看一下

• 转换为存储格式：TIME_to_longlong_datetime_packed

• 转换为日期格式：TIME_from_longlong_datetime_packed

以上简单记录。。。