从数据文件看timestamp范围限制

• 创建表：

mysql> desc test_timestamp1;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| time  | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+

• 插入数据，并查询，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 |
+-------------+