时间戳数据存取

在MySQL上述三个大版本中，默认时间戳(Timestamp)类型的取值范围为'1970-01-01 00:00:01' UTC 至'2038-01-19 03:14:07' UTC，数据精确到秒级别，该取值范围包含约22亿个数值，因此在MySQL内部使用4个字节INT类型来存放时间戳数据：

1、在存储时间戳数据时，先将本地时区时间转换为UTC时区时间，再将UTC时区时间转换为INT格式的毫秒值(使用UNIX_TIMESTAMP函数)，然后存放到数据库中。
2、在读取时间戳数据时，先将INT格式的毫秒值转换为UTC时区时间(使用FROM_UNIXTIME函数)，然后再转换为本地时区时间，最后返回给客户端。

在MySQL 5.6.4及之后版本，可以将时间戳类型数据最高精确微秒(百万分之一秒)，数据类型定义为timestamp(N),N取值范围为0-6，默认为0，如需要精确到毫秒则设置为Timestamp(3)，如需要精确到微秒则设置为timestamp(6)，数据精度提高的代价是其内部存储空间的变大，但仍未改变时间戳类型的最小和最大取值范围。

时间戳字段定义

时间戳字段定义主要影响两类操作：

插入记录时，时间戳字段包含DEFAULT CURRENT_TIMESTAMP，如插入记录时未指定具体时间数据则将该时间戳字段值设置为当前时间

更新记录时，时间戳字段包含ON UPDATE CURRENT_TIMESTAMP，如更新记录时未指定具体时间数据则将该时间戳字段值设置为当前时间

PS1：CURRENT_TIMESTAMP表示使用CURRENT_TIMESTAMP()函数来获取当前时间，类似于NOW()函数

根据上面两类操作，时间戳列可以有四张组合定义，其含义分别为：

当字段定义为timestamp，表示该字段在插入和更新时都不会自动设置为当前时间。

当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP，表示该字段仅在插入且未指定值时被赋予当前时间，再更新时且未指定值时不做修改。

当字段定义为timestamp ON UPDATE CURRENT_TIMESTAMP，表示该字段在插入且未指定值时被赋值为"0000-00-00 00:00:00",在更新且未指定值时更新为当前时间。

当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，表示该字段在插入或更新时未指定值，则被赋值为当前时间。

PS1：在MySQL中执行的建表语句和最终表创建语句会存在差异，建议使用SHOW CREATE TABLE TB_XXX获取已创建表的建表语句。

时间戳字段在MySQL各版本的使用差异

在MySQL 5.5及之前版本中，仅能对一个时间戳字段定义DEFUALT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP，但在MySQL 5.6和MySQL 5.7版本中取消了该限制;

在MySQL 5.6版本中参数explicit_defaults_for_timestamp默认值为1，在MySQL 5.7版本中参数explicit_defaults_for_timestamp默认值为0;

在MySQL 5.5和MySQL 5.7版本中timestamp类型默认为NOT NULL,在在MySQL 5.6版本中timestamp类型默认为NULL;

当建表语句中定于c1 timestamp 时，

在MySQL 5.5中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

在MySQL 5.6中等价于c1 timestamp NULL DEFAULT NULL;

在MySQL 5.7中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

当建表语句中c1 timestamp default 0时，

在MySQL 5.5中等价于c1 timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00';
在MySQL 5.6中等价于c1 timestamp NULL DEFAULT ‘0000-00-00 00:00:00';
在MySQL 5.7中等价于c1 timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00';
PS1: MySQL 5.6版本和MySQL 5.7版本中主要差异受参数explicit_defaults_for_timestamp的默认值影响。

PS2：当时间戳列的默认值为'0000-00-00 00:00:00'时，使用“不在时间戳取值范围内”的该默认值并不会产生警告。

时间戳类型引发的异常

当MySQL参数time_zone=system时，查询timestamp字段会调用系统时区做时区转换，而由于系统时区存在全局锁问题，在多并发大数据量访问时会导致线程上下文频繁切换，CPU使用率暴涨，系统响应变慢甚至假死。

如果表中包含


    TIMESTAMP

的列，那么其建表语句有可能被系统篡改，取决于MySql的版本和参数设置。

如果存入超过范围的时间，在非严格状态下，MySql不会报错，反而会插入


    '0000-00-00 00:00:00'

问题一：高并发下的问题

这一点 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使用率暴涨，系统响应变慢甚至假死。

问题二：如果表中包含 `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 |
+------+------+---------------------+

问题三：时间范围并不是强校验的

如果我们尝试往 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类型代替datetime字段，其理由是timestamp类型使用4字节，而datetime字段使用8字节，但随着磁盘性能提升和内存成本降低，在实际生产环境中，使用timestamp类型并不会带来太多性能提升，反而可能因timestamp类型的定义和取值范围限制和影响业务使用。

在MySQL 5.6.4及之后版本，可以将时间戳类型(timestamp)数据最高精确微秒，也同样可以将时间类型(datetime)数据最高精确微秒，时间类型(datetime)同样可以获得timestamp类型相同的效果，如将字段定义为 dt1 DATETIME(3) NOT NULL DEFAULT NOW(3) ON UPDATE NOW(3); 时间类型(datetime)的存取范围'1000-01-01 00:00:00.000000' 至 ‘9999-12-31 23:59:59.999999'，能更好地存放各时间段的数据。

时间戳类型使用建议

在只关心数据最后更新时间的情况下，建议将时间戳列定义为TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP；

在关心创建时间和更新时间的情况下，建议将更新时间设置为时间戳字段，将创建时间定义为DAETIME 或 TIMESTAMP DEFAULT ‘0000-00-00 00:00:00'，并在插入记录时显式指定创建时间；

建议在表中只定义单个时间戳列，并显式定义DEFAULT 和 ON UPDATE属性；

虽然在MySQL中可以对时间戳字段赋值或更新，但建议仅在必要的情况下对时间戳列进行显式插入和更新；

建议将time_zone参数设置为system外的值，如中国地区服务器设置为'+8:00'；

建议将MySQL线下测试版本和线上生产版本保持一致。

Timestamp和datetime的异同

1.可自动更新和初始化，默认显示格式相同YYYY-MM-dd HH:mm:ss
不同点：
2. timestamp的时间范围是：‘1970-01-01 00:00:01' UTC to ‘2038-01-19 03:14:07' UTC ，自动时区转化，实际存储毫秒数，4字节存储
3. datetime的时间范围：‘1000-01-01 00:00:00' to ‘9999-12-31 23:59:59' ，不支持时区，8字节存储

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