从数据文件看timestamp范围限制

痛定思痛。 2022-08-20 14:29 162阅读 0赞

*  创建表：

*  插入数据，并查询，mysql官方文档解释：timestamp的范围：1970-01-01 00:00:01UTC～2038/01/19 3:14:07UTC，

*  我们是东八区，所以我们的timestamp的范围：1970-01-01 8:00:01UTC～2038/01/19 11:14:07UTC,测试很简单：

> mysql> insert into test\_timestamp1 values('2038/01/19 11:14:07');  
> Query OK, 1 row affected (0.00 sec)  
> mysql> insert into test\_timestamp1 values('2038/01/19 11:14:08');  
> ERROR 1292 (22007): Incorrect datetime value: '2038/01/19 11:14:08' for column 'time' at row 1  
>   
> mysql> insert into test\_timestamp1 values('1970-01-01 8:00:01');  
> Query OK, 1 row affected (0.00 sec)  
> mysql> insert into test\_timestamp1 values('1970-01-01 8:00:00');  
> ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 8:00:00' for column 'time' at row 1  
>

*  为什么有这样的限制？可以从数据文件中找到答案：

> mysql> select \* from test\_timestamp1 limit 2;  
> \+---------------------+  
> | time                |  
> \+---------------------+  
> | 2038-01-19 11:14:07 |  
> | 2038-01-19 11:14:06 |  
> \+---------------------+  
> 2038-01-19 11:14:07  
> 查看表空间文件：  
> hexdump -C -v test\_timestamp1.ibd|more  
> 0000c1a0  00 00 00 07 75 0b 00 00  01 43 f1 16 e1 00 00 01  
> 0000c1b0  7d 01 10 7f ff ff ff 00  00 00 68 00 1d 00 00 00  
> 0000c1c0  07 75 0c 00 00 01 43 f1  17 e2 00 00 02 4e 01 10  
> 0000c1d0  7f ff ff fe 00 00 00 70  00 1d 00 00 00 07 75 0d  
> 0000c1e0  00 00 01 43 f1 7c b4 00  00 02 2a 01 10 7f ff ff  
>

*  猜测7f ff ff fe（01111 1111 .... 1110）就是2038-01-19 11:14:06，7f ff ff ff（01111 1111 .... 1111）（最高位是符号位？）就是2038-01-19 11:14:07，为什么这么说？

> 做个简单的查询：  
> mysql> select unix\_timestamp('2038-01-19 11:14:07');  
> \+---------------------------------------+  
> | unix\_timestamp('2038-01-19 11:14:07') |  
> \+---------------------------------------+  
> |                            2147483647 |  
> \+---------------------------------------+  
> 2147483647就是"2038/01/19 3:14:07UTC"减去"1970-01-01 8:00:01 UTC"的秒数，也就是说实际上mysql在存储时间戳的时候，会求取32位的整数，存储在磁盘上。  
> mysql> select pow(2,31)-1;  
> \+-------------+  
> | pow(2,31)-1 |  
> \+-------------+  
> |  2147483647 |  
> \+-------------+  
>   
>