MySQL 时间戳类型

日期类型是我们在数据库操作中一个较为常见的数据类型，TIMESTAMP 类型相信使用的朋友也不少，但是你真的了解它吗？

本文介绍在 MySQL 中使用 TIMESTAMP 类型遇到的一些潜在问题，最大时间限制相当于埋在未来的坑、因为系统的一些默认规则触发日期自动更新、默认系统时区的性能问题，发现问题的同时，后面也推荐了一些在日期上个人认为不错的方案，供参考。

安装 MySQL

推荐 Docker 的方式本机安装一个 MySQL，步骤也很简单，如下所示，对于学习还是很方便的，已安装的可忽略。

$ docker pull mysql
$ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
$ docker exec -it mysql-test /bin/sh
$ mysql -h localhost -u root -p

一个埋在未来的坑

假设，未来 2038 年某天的你，执行了一条 SQL 更新了一个时间，第一次值为 '2038-01-19 03:14:07' 成功了，第二次值为 '2038-01-19 03:14:08' 报错了说传的值是无效的，中间仅差了一秒，看着挺正常的一个 SQL 啊！Why？

# 第 1 次更新
$ UPDATE user SET birthday = '2038-01-19 03:14:07'  WHERE id = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# 第 2 次更新
$ UPDATE user SET birthday = '2038-01-19 03:14:08'  WHERE id = 1;

ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'birthday' at row 1

在 MySQL 中，由于 TIMESTAMP 类型占用的空间为 4 个字节，理论上其能够存储最大的日期为 “2038-01-19 03:14:07”，而在 MySQL 5.6 之后占用的内存空间为 7 个字节，可以精确到毫秒、微秒，但是这个最大日期并没有被改变。

所以我们上面多设置了一秒，就报错了，对于系统而言，哪怕多一点也是不行的，超了就是超了。

这个限制在 MySQL 官方  也有描述：

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

小心 TIMESTAMP 的自动更新

假设一张表有 name、birthday 这些字段，这里的自动更新是指当你修改了表中 name 这个字段，但是最后发现 birthday 这个字段被更新为了系统的当前时间。

并且这种情况并不总是会出现，它和 MySQL 系统里的一个规则 **explicit_defaults_for_timestamp** 有关,默认情况下该参数的值为 OFF。

通过以下命令查看。

$ show variables like '%explicit_defaults_for_timestamp%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+

但是，这里容易潜在的埋一些坑，有些 MySQL 镜像直接将这个值改为了 ON 就是禁用了功能。例如，通过上面 Docker 方式安装的就已经禁用了该功能。

问题复现

为了复现和讲解这个问题，现在我需要将这个功能给放开，使用如下命令。

$ SET @@SESSION.explicit_defaults_for_timestamp = 'ON';