不用到 2038 年，MySQL 的 TIMESTAMP 就能把我们系统搞崩|mysql|timestamp|query|innodb

分享至

　　说起MySQL中的TIMESTAMP，大多数人知道它只能表达2038年之前的时间，但它还有很多隐藏的坑，比如说涉及到时区转换的时候有可能拿全局锁影响并发性能，最糟糕的是在5.6版本以后，系统默认自动篡改TIMESTAMP的建表语句，产生许多让开发者疑惑的行为，本文详细总结了开发中可能会遇到的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> 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 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)

　　这一次，建表语句中那些奇怪的默认值都没有了，清爽了好多，而且 TIMESTAMP 的的列也可以插入 NULL 了，如果我们显式指定了 NOT NULL，STRICT_TRANS_TABLES 被指定的情况下直接报错，如果 STRICT_TRANS_TABLES 没有被指定，那么会向该列中插入0000-00-00 00:00:00并且产生一个 warning

　　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> 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> 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 的改动需要格外注意，尽量要在充分的测试后再上线。

　　参考资料

　　[1] https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

　　[2] https://blog.csdn.net/weter_drop/article/details/89924451

　　[3] https://kernelnewbies.org/y2038

　　[4] https://segmentfault.com/a/1190000018818020

　　[5] https://www.ithome.com.tw/news/103902

特别声明：以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布，本平台仅提供信息存储服务。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.