MYSQL中TIMESTAMP的各种坑

MySql 中常见的时间类型有三种 DATEDATETIMETIMESTAMP ,其中 DATE 类型用于表示日期,但是不会包含时间,格式为 YYYY-MM-DD ,而 DATETIMETIMESTAMP 用于表示日期和时间,常见的格式为 YYYY-MM-DD HH:MM:SS ,也可以带 6 位小数来表示微秒。

不同于 DATETIMETIMESTAMP 支持的时间范围从 1970-01-01 00:00:01.0000002038-01-19 03:14:07.999999 ,使用了 TIMESTAMP 的应用很有可能在 2038-01-19 03:14:07.999999 之后宕机,同样面临这个问题的还有所有的类 Unix 系统,因为他们使用了 time_t 这一 32 位数字来表示时间,这就是著名的 2038 年问题

因为时间问题搞坏系统的例子可不少,在 2016 年曾经爆出过一个 iPhonebug ,如果将 iPhone 的时间调整到 1970-01-01 00:00:00 ,则会导致手机”变砖“,原因是 IOS 基于 BSD 这种 Unix 系统构建,在将时间调整到 1970-01-01 00:00:00 后,如果手机需要展示之前的时间,例如之前收到过短信,则会导致整数溢出。对于 2038 问题,Linux 的解法是提供新的用户接口: y2038 - Linux Kernel Newbies 。但是 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 NULLSTRICT_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 比较少了,的确也应该尽量避免使用 TIMESTAMPMySqlTIMESTAMP 的设计上实在是蹩脚,如果你正在维护一个老的系统,涉及到 TIMESTAMP 的改动需要格外注意,尽量要在充分的测试后再上线。

参考资料

[1] https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
[2] Mysql的timestamp(时间戳)详解以及2038问题的解决方案_mysql timestamp范围-CSDN博客
[3] y2038 - Linux Kernel Newbies
[4] 滑动验证页面
[5] 網路惡作劇別上當! 將iPhone日期調回1970年可能造成手機死當  | iThome


原文:https://mp.weixin.qq.com/s/z0aZwEmJvsl1TSUD5W2c7g
作者:菜鸟教程