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

安装 MySQL

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

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

一个埋在未来的坑

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

  1. # 第 1 次更新
  2. $ UPDATE user SET birthday = '2038-01-19 03:14:07'  WHERE id = 1;
  3. Query OK, 0 rows affected (0.01 sec)
  4. Rows matched: 1  Changed: 0  Warnings: 0
  6. # 第 2 次更新
  7. $ UPDATE user SET birthday = '2038-01-19 03:14:08'  WHERE id = 1;
  9. 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 官方 11.2.2 The DATE, DATETIME, and TIMESTAMP Types 也有描述：

  1. 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。
通过以下命令查看。

  1. $ show variables like '%explicit_defaults_for_timestamp%';
  2. +---------------------------------+-------+
  3. | Variable_name                   | Value |
  4. +---------------------------------+-------+
  5. | explicit_defaults_for_timestamp | OFF   |
  6. +---------------------------------+-------+

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

问题复现

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

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

首先创建一个数据库，和一个 user 表，注意下目前对生日字段的定义为 birthday TIMESTAMP NOT NULL。

  1. $ CREATE DATABASE test;
  2. $ CREATE TABLE user(
  3.   id BIGINT NOT NULL AUTO_INCREMENT,
  4.   name VARCHAR(20) NOT NULL,
  5.   birthday TIMESTAMP NOT NULL,
  6.   PRIMARY KEY (id)
  7. );

执行 DESC user; 命令，查看当前的表结构，发现 birthday 字段 Extra 这一列多了一些定义，Why？

  1. DESC user;
  2. +----------+-------------+------+-----+-------------------+-----------------------------------------------+
  3. | Field    | Type        | Null | Key | Default           | Extra                                         |
  4. +----------+-------------+------+-----+-------------------+-----------------------------------------------+
  5. | id       | bigint      | NO   | PRI | NULL              | auto_increment                                |
  6. | name     | varchar(20) | NO   |     | NULL              |                                               |
  7. | birthday | timestamp   | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
  8. +----------+-------------+------+-----+-------------------+-----------------------------------------------+

这一块有个默认的规则，当 explicit_defaults_for_timestamp 这个规则开启时，创建表指定的 TIMESTAMP 类型的第一列，如果没有显示的使用 NULL 或 DEFAULT 或 ON UPDATE 声明，表创建成功之后，会自动带上 DEFAULT_GENERATED on update CURRENT_TIMESTAMP 属性声明。对应示例就是上面定义的 birthday TIMESTAMP NOT NULL。
如果设置为这样子，意思是修改数据，会把该类型对应的字段变为数据库当前的系统日期。
改规则下，并且一张表中仅有一个字段可以拥有该特性，如果设置两个会报错。

  1. $ CREATE TABLE user(
  2.   birthday TIMESTAMP NOT NULL,
  3.   utime TIMESTAMP NOT NULL,
  4. );
  6. // 运行之后会得到一个 show variables like '%explicit_defaults_for_timestamp%'; 错误。

往 user 表中插入一条数据。

  1. $ INSERT INTO user(name, birthday) VALUES('Tom', NOW(6));

假设，目前时间点为 T1（当前 T1 的时间为 2021-01-01 06:10:27），查看当前 user 表中的数据。

  1. $ SELECT * FROM user;
  2. +----+------+---------------------+
  3. | id | name | birthday            |
  4. +----+------+---------------------+
  5. |  1 | Tom  | 2021-06-06 06:10:27 |
  6. +----+------+---------------------+

假设，目前时间点为 T2（当前 T2 的时间为 2021-01-01 06:13:06） 更新 user 表中的 name 为 Tom2，看返回结果 Changed: 1更新是成功的。

  1. $ UPDATE user SET name = 'Tom2' WHERE id = 1;
  2. Query OK, 1 row affected (0.02 sec)
  3. Rows matched: 1  Changed: 1  Warnings: 0

再次查询，发现 birthday 字段的值被改变为了 T2 这个时间点，但是明明上面的 SQL 语句没有写更新 birthday 这个字段啊！Why？

  1. $ SELECT * FROM user;
  2. +----+------+---------------------+
  3. | id | name | birthday            |
  4. +----+------+---------------------+
  5. |  1 | Tom2 | 2021-06-06 06:13:06 |
  6. +----+------+---------------------+

解决方案

当 explicit_defaults_for_timestamp 这个规则开启时（其值为 OFF），如果没有对 TIMESTAMP 类型的字段显性赋值，更新时系统会默认设置为系统当前时间。
如果不清楚这个问题，查找起来简直让人崩溃，明明 SQL 语句没有，还是被更新了。
大多数情况下，这并非想要的情况，怎么禁用？

方法一：修改系统参数

将 explicit_defaults_for_timestamp 的值修改为 ‘ON’ 禁用掉该属性。
正在运行的，可以使用 SET @@SESSION.explicit_defaults_for_timestamp = 'ON'; 修改。这里又一个坑，经测试验证一旦表已创建，在设置是无效的。如果是在禁用该规则后创建的表，是可以的。

方法二：修改表结构

对于那些线上正在运行的无法修改的，总不能直接把表删了再改吧。
当 explicit_defaults_for_timestamp 属性为 OFF 的情况下也有两种方法可以禁用，需要修改表结构。

  1. // 指定该列为 NULL，例如
  2. $ ALTER TABLE user MODIFY birthday TIMESTAMP NULL。
  4. // 使用 DEFAULT 为该列指定一个默认值，例如
  5. $ ALTER TABLE user MODIFY birthday TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

最后，根据 MYSQL 官网文档 [sysvar_explicit_defaults_for_timestamp](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp) 的描述，这个非标准行为已被弃用，希望它们在未来的 MYSQL 版本中被删除。确实挺坑的一个行为，如果不熟读文档，很容易踩坑。

TIMESTAMP 性能问题

TIMESTAMP 类型支持时区转换，这个有利也有弊，当默认为操作系统时区时（time_zone=SYSTEM），查询系统 TIMESTAMP 类型的字段会调用系统时区做时区转换。而这个系统时区需要加锁来保证此时的操作系统时区没有被修改。
当出现并发访问时，势必会出现资源竞争，多线程的上下文切换消耗，性能也会出现下降，下面做个性能测试。
查看当前的时区信息，time_zone=SYSTEM 表示此时是操作系统的时区。

  1. $ show variables like "%time_zone%";
  2. +------------------+--------+
  3. | Variable_name    | Value  |
  4. +------------------+--------+
  5. | system_time_zone | UTC    |
  6. | time_zone        | SYSTEM |
  7. +------------------+--------+

时区修改

MySQL 默认使用系统时区，修改方法大致分为两种：使用 SQL 命令临时修改，修改配置文件这种是永久修改。

  1. # SQL 命令修改
  2. $ SET time_zone = 'Asia/Shanghai';
  4. # 配置文件
  5. $ vim /etc/mysql/my.cnf
  6. default-time_zone = 'Asia/Shanghai'

如果使用 Docker 的可以在 docker run 时修改。通过 -e TZ='Asia/Shanghai' 指定时区，但是这样发现 虽然 SELECT NOW() 没问题，但是执行 show variables like "%time_zone%" 命令 time_zone 还是显示的 SYSTEM。

  1. $ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -e TZ='Asia/Shanghai' mysql

推荐修改文件，首先进入容器内执行 cat /etc/mysql/conf.d/mysql.cnf 命令查看默认配置，拷贝一份到自己的本机电脑，在执行 docker run 时挂载到容器内，这种方式好处是当有多个配置需要修改时，都可以在配置文件里改。
配置文件也许是这样的：

  1. [mysqld]
  2. pid-file        = /var/run/mysqld/mysqld.pid
  3. socket          = /var/run/mysqld/mysqld.sock
  4. datadir         = /var/lib/mysql
  5. secure-file-priv= NULL
  6. default-time_zone = 'Asia/Shanghai'
  8. # Custom config should go here
  9. !includedir /etc/mysql/conf.d/

最终 docker run 命令如下：

  1. # 注意 /${root}/mysql.cnf 这个是你本机的配置地址
  2. $ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /${root}/mysql.cnf:/etc/mysql/my.cnf mysql

性能测试

MySQL 自带了一个压力测试工具 mysqlslap，可以模拟多个并发客户端来对 MySQL 做压力测试，还是挺不错的，写一些功能，想测试下基本的性能时还是可以用用的。
以下这个语句的意思是模拟 100 个客户端并发，共执行 100,0000 万次查询。

  1. # --number-of-queries 总的测试查询次数
  2. # -c 并发量，模拟多个客户端执行，下例模拟多个客户端执行 “SELECT NOW()”
  3. # --create-schema 代表自定义的测试库名称，就是 MySQL 中的数据库名称
  4. $ mysqlslap -u root -p --number-of-queries=1000000 --create-schema=test -c 100 --query='SELECT NOW()'

下面是基于 mysqlslap 做的性能测试结果，在不同的时区下，分别所耗时间，单位（秒），很明显系统时区耗时更长些，两者直接的相差为 25%。这只是耗时上的差异，CPU 信息没有去看。还有不同的电脑，测试出来的性能差距也会有差异。

——— System Asia/Shanghai difference
Average number of seconds to run all queries 35.55s 28.42s 25%

日期该怎么选择？

MySQL 中日期类型存储通常有 3 中方案，使用 INT、TIMESTAMP、DATETIME 下面分别简单总结下。

INT 类型

INT 类型来存储日期类型，存储的就是时间戳类型，例如 2021-01-01 06:10:27 的时间戳为 1609452627000。
数据库实际存储的是一串数字，这种好处是没有时间上下范围限制，性能也比 TIMESTAMP 好，但是这种性能是收效甚微的，一个不友好的问题是，当想查看数据做一些问题排查或数据分析时，通常不是很直观的。

TIMESTAMP 类型

TIMESTAMP 类型在存储时会先将本地时区时间转换为 UTC 的时区时间，再讲 UTC 时区时间转为 4 字节 INT 类型存储，本质是和 INT 一样的，都是存储为毫秒数。读取时再次反向的转换为时间戳 TIMESTAMP 类型，会做一些时间的格式化，看起来更直观些。
TIMESTAMP 类型比较大的一个问题是有最大的时间限制，能够有效存储的时间范围为 “’1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’”，2038 年这个时间说远也是很快的，这个是需要考虑的，别为将来埋坑。
TIMESTAMP 类型尽管 5.6 版本之后支持精确到微笑，毫秒后面 6 为，但是 2038 最大时间限制这个问题并没有解决。
它还有一个隐藏的问题是，当把一个字段的定义为 birthday DATETIME NOT NULL 且触发了它的自动更新规则时，很容易掉坑里。可怕的是开发和生产环境配置不一致，这种问题前期就发现不了，除非踩过这个坑。

DATETIME 类型

DATETIME 这个类型是比较推荐的，它占用 8 个字节，能存储的精确度为微妙，声明类型时通过 DATETIME(6) 指定。
它的时间范围为 ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. 这个时间目前是够用的了。当然，如果要存储 “三国时期张飞” 什么时候出生，那这 160 年生日也是存储不了的。
DATETIME 类型它不会存储时区信息，当然这个问题，也不一定义非要在数据层解决不可，也不是什么大不了的问题，想做这种国际化的跨时区的，由中间层服务（Node.js 就很适合）统一解决也可这个日期类型它能解决上面使用 TIMESTAMP 遇到的那些问题。
修改上面 user 表结构，将日期类型统一声明为 DATETIME 类型。

  • birthday 字段由用户自定义传入，指定为非空，DATETIME 这样声明精确为秒。
  • ctime 字段默认当前时间，仅在创建时指定，时间精确到微秒。
  • utime 字段记录每一次的更新时间，这个不受 explicit_defaults_for_timestamp 参数影响并且也是在显示的定义了 ON UPDATE… 之后才触发自动更新。
    1. CREATE TABLE user(
    2. id BIGINT NOT NULL AUTO_INCREMENT,
    3. name VARCHAR(20) NOT NULL,
    4. birthday DATETIME NOT NULL,
    5. ctime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    6. utime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    7. PRIMARY KEY (id)
    8. );
    假设，插入一条数据，birthday 传入 2039 年使用 DATETIME 是没问题的，同时也可以看下 ctime、utime 时间，这个精确度也是自己定义的。
    1. +----+------+---------------------+----------------------------+----------------------------+
    2. | id | name | birthday            | ctime                      | utime                      |
    3. +----+------+---------------------+----------------------------+----------------------------+
    4. |  1 | Tom  | 2039-01-01 22:00:28 | 2021-01-01 22:00:28.112048 | 2021-01-01 22:00:28.112048 |
    5. +----+------+---------------------+----------------------------+----------------------------+

    参考资料

    11.2.2 The DATE, DATETIME, and TIMESTAMP Types: https://dev.mysql.com/doc/refman/8.0/en/datetime.html
    sysvar_explicit_defaults_for_timestamp: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp