搜索

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

Java后端技术

共 11817字，需浏览 24分钟

 ·

2021-06-05 14:12

往期热门文章：

1、翻车！在项目中用了Arrays.asList、ArrayList的subList，被公开批评
2、想接私活时薪再翻一倍，建议根据这几个开源的Spring Boot项目改改~
3、细数ThreadLocal三大坑，内存泄露仅是小儿科
4、Redis与MySQL双写一致性如何保证？
5、程序员版孔乙己

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的默认值。
如果我们将explicit_defaults_for_timestamp的值改为ON，则事情会变得好很多
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)
这一次，建表语句中那些奇怪的默认值都没有了，清爽了好多，而且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> 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的改动需要格外注意，尽量要在充分的测试后再上线。

参考资料


[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
往期热门文章：
1、《历史文章分类导读列表！精选优秀博文都在这里了！》
2、为什么不建议你用a.equals(b)判断对象相等
3、为什么 Java 后端开发没有大规模采用 Kotlin？
4、为什么不推荐使用BeanUtils属性转换工具
5、Top 16 的 Java 工具类，你用过几个？
6、分享几个酷炫的 IDEA 主题
7、Intellij IDEA 这样配置注释模板，让你瞬间高出一个逼格！
8、【建议收藏】面试官会问的位运算奇淫技巧
9、到底可不可以用 kill -9 关闭程序？
10、IDEA 2021首个大版本发布，新增了这几个超实用功能！
浏览 22
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
不用到2038年，MySql的TIMESTAMP就能把我们系统搞崩
后端技术漫谈
0
不用到2038年，MySql的TIMESTAMP就能把我们系统搞崩
Java技术迷
0
MySQL 8.0 timestamp引发的狗血剧情
MySQL技术
0
MySQL 尽量避免使用 TIMESTAMP！！
Java技术栈
0
当 go-sql-driver 遇到 mysql timestamp 的离奇 bug
Go语言精选
0
卧槽，sql注入竟然把我们的系统搞挂了
程序IT圈
0
卧槽，sql注入竟然把我们的系统搞挂了
moon聊技术
0
卧槽，sql注入竟然把我们的系统搞挂了
码农沉思录
0
卧槽，sql注入竟然把我们的系统搞挂了
苏三说技术
0
“直播乞讨”乱象丛生，“不给钱就搞废你的号”
蓝鲸浑水
0
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报