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

nill0705

发布于 2022-9-27 11:46

浏览

0收藏

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

但是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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

虽然我们输入的建表语句很简单，但是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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

可以看到，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的默认值。

不用到2038年，MySql的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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.

这一次，建表语句中那些奇怪的默认值都没有了，清爽了好多，而且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 |
+------+------+---------------------+1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

高并发环境下并不适合使用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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

总结

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

分类

数据库

标签

Java

已于2022-9-27 13:52:06修改

51CTO

51CTO博客

51CTO学堂

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

订阅鸿蒙技术特刊，精选内容抢先看