不用到 2038 年，MySQL 的 TIMESTAMP 就能把我们系统搞崩

MySQL?中常见的时间类型有三种 DATE, DATETIME 和 TIMESTAMP，其中 DATE 类型用于表示日期，但是不会包含时间，格式为 YYYY-MM-DD，而 DATETIME 和 TIMESTAMP 用于表示日期和时间，常见的格式为 YYYY-MM-DD HH:MM:SS，也可以带 6 位小数来表示微秒。不同于 DATETIME，TIMESTAMP 支持的时间范围从 1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999，使用了 TIMESTAMP 的应用很有可能在 2038-01-19 03:14:07.999999 之后宕机，同样面临这个问题的还有所有的类 Unix 系统，因为他们使用了 time_t 这一 32 位数字来表示时间，这就是著名的 2038 年问题。

因为时间问题搞坏系统的例子可不少，在 2016 年曾经爆出过一个 iPhone 的 bug，如果将 iPhone 的时间调整到 1970-01-01 00:00:00，则会导致手机”变砖“，原因是 IOS 基于 BSD 这种 Unix 系统构建，在将时间调整到 1970-01-01 00:00:00 后，如果手机需要展示之前的时间，例如之前收到过短信，则会导致整数溢出。对于 2038 问题，Linux 的解法是提供新的用户接口：https://kernelnewbies.org/y2038.但是 MySql 至今还没有相应的公告。

TIMESTAMP 的设计之初是为了支持自动时区转换：

mysql>?CREATE?TABLE?`employee`?(
????->??`entry_time`?timestamp?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP
????->?)?ENGINE=InnoDB
????->?;
Query?OK,?0?rows?affected?(0.01?sec)

mysql>?INSERT?INTO?`employee`?(`entry_time`)?VALUES?(CURRENT_TIMESTAMP);
Query?OK,?1?row?affected?(0.01?sec)

mysql>?SELECT?*?FROM?`employee`;
+---------------------+
|?entry_time??????????|
+---------------------+
|?2021-05-09?08:14:08?|
+---------------------+
1?row?in?set?(0.00?sec)

mysql>?SET?@@session.time_zone?=?'-05:00';?SELECT?*?FROM?`employee`;
Query?OK,?0?rows?affected?(0.00?sec)

+---------------------+
|?entry_time??????????|
+---------------------+
|?2021-05-09?03:14:08?|
+---------------------+
1?row?in?set?(0.00?sec)

但是 TIMESTAMP 的一些设计却非常鬼畜，比如：

• 如果表中包含 TIMESTAMP 的列，那么其建表语句有可能被系统篡改，取决于 MySql 的版本和参数设置。
• 当 MySQL 参数 time_zone=system 时，高并发可能会引起 CPU 使用率暴涨，系统响应变慢甚至假死
• 如果存入超过范围的时间，在非严格状态下，MySql 不会报错，反而会插入'0000-00-00 00:00:00'

新建一个包含TIMESTAMP的表可真难

MySql 5.6.6 版本引入了 explicit_defaults_for_timestamp 这个参数，随即被标记为废弃，这个参数主要影响表中类型为 TIMESTAMP 的那些列在新建表时的表现

mysql>?show?variables?like?'explicit_defaults_for_timestamp';
+---------------------------------+-------+
|?Variable_name???????????????????|?Value?|
+---------------------------------+-------+
|?explicit_defaults_for_timestamp?|?OFF???|
+---------------------------------+-------+

mysql>?create?table?t1?
????->?(
????->?ts1?timestamp,
????->?ts2?timestamp,
????->?ts3?timestamp?default?'2010-01-01?00:00:00'
????->?);
Query?OK,?0?rows?affected?(0.03?sec)

mysql>?show?create?table?t1\G
***************************?1.?row?***************************
???????Table:?t1
Create?Table:?CREATE?TABLE?`t1`?(
??`ts1`?timestamp?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP,
??`ts2`?timestamp?NOT?NULL?DEFAULT?'0000-00-00?00:00:00',
??`ts3`?timestamp?NOT?NULL?DEFAULT?'2010-01-01?00:00:00'
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8
1?row?in?set?(0.00?sec)

虽然我们输入的建表语句很简单，但是 MySql 却对于我们输入的建表语句做了诸多的篡改：

• 对于表中的第一个TIMESTAMP列，系统自动加了NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，这些操作对于新建表的开发者完全是不感知的。
• 对于表中的第二个 TIMESTAMP 列，系统自动加了一个默认值 0000-00-00 00:00:00，这个操作同样对于新建表的开发者完全不感知。

在系统对我们的建表语句做了自动修改之后，对表的插入操作可能就不会如开发者预期的那样:

mysql>?insert?into?t1?values?(null,null,null);
Query?OK,?1?row?affected?(0.00?sec)

mysql>?select?*?from?t1;
+---------------------+---------------------+---------------------+
|?ts1?????????????????|?ts2?????????????????|?ts3?????????????????|
+---------------------+---------------------+---------------------+
|?2021-05-09?07:47:50?|?2021-05-09?07:47:50?|?2021-05-09?07:47:50?|
+---------------------+---------------------+---------------------+
1?row?in?set?(0.00?sec)

可以看到，MySql 的表现非常的鬼畜

• 对于第一个 TIMESTAMP 列，建表语句中指定可以为null，但是插入null的时候存到表里的却是当前时间
• 对于第二个 TIMESTAMP 列，虽然通过语句 show create table t1\G查出来的建表语句指定的默认值是'0000-00-00 00:00:00'但是存到表里的却是当前时间
• 最奇怪的是第三个 TIMESTAMP 列，尽管我们显式指定默认值为'2010-01-01 00:00:00'，但是落表的时间仍然是当前时间

这一切都是在参数 explicit_defaults_for_timestamp 被设置为 OFF 的时候发生的，但是遗憾的是 OFF 恰恰就是参数 explicit_defaults_for_timestamp 的默认值。

mysql>?show?variables?like?'explicit_defaults_for_timestamp';
+---------------------------------+-------+
|?Variable_name???????????????????|?Value?|
+---------------------------------+-------+
|?explicit_defaults_for_timestamp?|?ON????|
+---------------------------------+-------+
mysql>?create?table?t2?
????->?(
????->?ts1?timestamp,
????->?ts2?timestamp,
????->?ts3?timestamp?default?'2010-01-01?00:00:00'
????->?);
Query?OK,?0?rows?affected?(0.02?sec)

mysql>?show?create?table?t2\G
***************************?1.?row?***************************
???????Table:?t2
Create?Table:?CREATE?TABLE?`t2`?(
??`ts1`?timestamp?NULL?DEFAULT?NULL,
??`ts2`?timestamp?NULL?DEFAULT?NULL,
??`ts3`?timestamp?NULL?DEFAULT?'2010-01-01?00:00:00'
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8
1?row?in?set?(0.01?sec)

mysql>?insert?into?t2?values?(null,null,null);
Query?OK,?1?row?affected?(0.01?sec)

mysql>?select?*?from?t2;
+------+------+------+
|?ts1??|?ts2??|?ts3??|
+------+------+------+
|?NULL?|?NULL?|?NULL?|
+------+------+------+
1?row?in?set?(0.00?sec)

mysql>?create?table?t3?
????->?(
????->?ts1?timestamp,
????->?ts2?timestamp,
????->?ts3?timestamp?not?null
????->?);
Query?OK,?0?rows?affected?(0.01?sec)

mysql>?show?create?table?t3\G
***************************?1.?row?***************************
???????Table:?t3
Create?Table:?CREATE?TABLE?`t3`?(
??`ts1`?timestamp?NULL?DEFAULT?NULL,
??`ts2`?timestamp?NULL?DEFAULT?NULL,
??`ts3`?timestamp?NOT?NULL
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8
1?row?in?set?(0.01?sec)

mysql>?insert?into?t3?values?(null,null,null);
ERROR?1048?(23000):?Column?'ts3'?cannot?be?null

mysql>?insert?into?t3?(ts1,ts2)?values?(null,null);
Query?OK,?1?row?affected,?1?warning?(0.01?sec)

mysql>?show?warnings;
+---------+------+------------------------------------------+
|?Level???|?Code?|?Message??????????????????????????????????|
+---------+------+------------------------------------------+
|?Warning?|?1364?|?Field?'ts3'?doesn't?have?a?default?value?|
+---------+------+------------------------------------------+

mysql>?select?*?from?t3;
+------+------+---------------------+
|?ts1??|?ts2??|?ts3?????????????????|
+------+------+---------------------+
|?NULL?|?NULL?|?0000-00-00?00:00:00?|
+------+------+---------------------+

高并发环境下并不适合使用TIMESTAMP

这一点 MySql 的文档中有明确的说明：

Note

If set to SYSTEM, every MySQL function call that requires a time zone calculation makes a system library call to determine the current system time zone. This call may be protected by a global mutex, resulting in contention.

虽然通过 TIMESTAMP 可以自动转换时区，代价是当 MySQL 参数time_zone=system 时每次都会尝试获取一个全局锁，这在高并发的环境下无疑是致命的，可能会导致线程上下文频繁切换，CPU 使用率暴涨，系统响应变慢甚至假死。

时间范围并不是强校验的

如果我们尝试往 MySql 中插入超过 TIMESTAMP 可表示的时间范围的值，MySql 在非严格模式下并不会报错，仅会产生一个 warning

mysql> insert into t1 values ('2039-01-01 00:00:00',null,null);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1264 | Out of range value for column 'ts1' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+---------------------+---------------------+---------------------+
| ts1                 | ts2                 | ts3                 |
+---------------------+---------------------+---------------------+
| 2021-05-09 07:47:50 | 2021-05-09 07:47:50 | 2021-05-09 07:47:50 |
| 0000-00-00 00:00:00 | 2021-05-09 08:09:06 | 2021-05-09 08:09:06 |
+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

总结

现在用 TIMESTAMP 比较少了，的确也应该尽量避免使用 TIMESTAMP，MySql 在 TIMESTAMP 的设计上实在是蹩脚，如果你正在维护一个老的系统，涉及到 TIMESTAMP 的改动需要格外注意，尽量要在充分的测试后再上线。

参考资料

?突破 1nm！台积电祭出“半金属”取代硅材料；搜狗发布手语 AI 合成主播；iOS 微信 8.0.6 版本更新|极客头条
?Babel 陷财务困境，负责人13万年薪遭质疑，Vue.js作者尤雨溪发文力挺
?"我辞退了一位学位学历造假的程序员"