MySQL实战宝典 表结构设计篇 03 日期类型：TIMESTA

几乎每张业务表都带有日期列，用于记录每条记录产生和变更的时间、比如用户表会有一个日期列记录用户注册的时间、用户最后登录的时间。又比如电商行业中的订单表，会有一个订单产生的时间，当支付时间超过订单产生时间一段时间后，这个订单可能会被系统自动取消。

日期类型虽然常见，但在表结构设计中也容易犯错，比如很多程序员同学都倾向使用整型存储日期类型，同时也会忽略不同日期类型对于性能可能存在潜在影响

日期类型

MySQL数据库中常见的日期类型有YEAR、DATE、TIME、DATETIME、TIMESTAMP。因为业务绝大部分场景都需要将日期精确到秒，所以在表结构设计中，常使用的日期类型有DATETIME和TIMESTAMP。

DATETIME

类型DATETIME展现的的形式为：YYYY-MM-DD HH：mm：SS，固定占用8个字节。

从MySQL 5.6版本开始，DATETIME类型支持毫秒，DATETIME(N)中的N表示毫秒的精度。例如DATETIME(6)表示可以存储6位的毫秒值。同时一些日期函数也支持精确到毫秒，例如常见的函数NOW，SYSDATE：

mysql> SELECT NOW(6);
+----------------------------+
| NOW(6)                     |
+----------------------------+
| 2021-06-11 08:29:58.403262 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT SYSDATE(6);
+----------------------------+
| SYSDATE(6)                 |
+----------------------------+
| 2021-06-11 08:30:32.353279 |
+----------------------------+
1 row in set (0.00 sec)

用户可以将DATETIME初始化设置为当前时间，并设置自动更新当前时间的属性。例如之前已设计的用户表users：

CREATE TABLE users (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `sex` CHAR(1) NOT NULL,
    `password` VARCHAR(1024) NOT NULL,
    `money` BIGINT NOT NULL DEFAULT 0,
    `register_date` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    -- 注册时间，默认为当前时间，之后不再修改
    `last_modify_date` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    -- 最后修改时间，默认为当前时间，之后每次记录被更新，都会更改为修改时的当前时间
    CHECK (`sex`='M' OR `sex`='F'),
    PRIMARY KEY(`id`)
);

/***************************************************
MySQL获取当前时间的函数，CURRENT_TIMESTAMP()和NOW()没有任何区别，是SQL执行开始的时间，SYSDATE()则是执行SYSDATE()函数时的时间
mysql> SELECT CURRENT_DATE, CURRENT_TIME(6),CURRENT_TIMESTAMP(6),NOW(6),SYSDATE(6);
+--------------+-----------------+----------------------------+----------------------------+----------------------------+
| CURRENT_DATE | CURRENT_TIME(6) | CURRENT_TIMESTAMP(6)       | NOW(6)                     | SYSDATE(6)                 |
+--------------+-----------------+----------------------------+----------------------------+----------------------------+
| 2021-06-11   | 08:45:16.303309 | 2021-06-11 08:45:16.303309 | 2021-06-11 08:45:16.303309 | 2021-06-11 08:45:16.303423 |
+--------------+-----------------+----------------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)

TIMESTAMP

TIMESTAMP，实际存储的内容为1970-01-01 00:00:00到现在的秒数。在MySQL中，TIMESTAMP占用4个字节，因此其存储的时间上限只能到2038-01-19 03:14:07。

同类型DATETIME一样，从MySQL 5.6版本开始支持毫秒。与DATETIME类型不同的时，若带有毫秒时，类型TIMESTAMP占用7个字节，而DATETIME无论是否存储毫秒信息，都占用8个字节。

类型TIMESTAMP最大的优点是可以带有时区属性，因为它本质上是从毫秒转化而来。如果你的业务需要对应不同的国家时区，那么类型TIMESTAMP是一种不错的选择。用户可以通过参数TIME_ZONE指定所需要的时区，默认为SYSTEM使用操作系统时区。

-- 直接加减时区并不直观，而且需要非常熟悉各国的时间表，MySQL中可以直接设置时区的名字
SET TIME_ZONE = '-08:00';
SET TIME_ZONE = 'Asia/Shanghai';

业务表结构设计实战

DATETIME vs TIMESTAMP vs INT，怎么选

INT类型就是直接存储1970-01-01 00:00:00到现在的毫秒数，本质和TIMESTAMP一样，因此用INT不如直接使用TIMESTAMP。在后期运维和数据分析时，使用INT存储日期，是会让DBA和数据分析人员发疯的，INT的可运维性太差。

TIMESTAMP类型占用4个字节，若要精确到毫秒，占用7个字节。现在距离TIMESTAMP最大值2038-01-19 03:14:07已经太近，这是需要程序员同学好好思考的问题。

所以建议使用DATETIME类型，对于时区问题，可以有前端或者服务这里做一次转化，不一定非要在数据库中解决。

不可忽视TIMESTAMP的性能问题

如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数__tx_convert(),而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以大规模并发访问时，由于热点资源竞争，会产生两个问题。

• 性能不如DATETIME：DATETIME不存在时区转化问题
• 性能抖动：海量并发，存在性能抖动问题

为了优化TIMESTAMP的使用，强烈建议显式的设置时区，而不是使用默认的操作系统时区。比如在配置文件中显式的设置时区

[mysqld]
TIME_ZONE = 'Asia/Shanghai'

最后通过mysqlslap来测试TIMESTAMP使用系统默认时区和显式设置时区的性能差别：

mysqlslap -uroot --number-of-queries=1000000 --concurrency=100 --query='SELECT NOW()'

结果：

时区	耗时	性能提升
System	6.674	/
Asia/Shanghai	4.584	45.59%

从表中可以发现，显式指定时区的性能明显优于使用操作系统时区，所以，日期字段推荐使用DATETIME，没有时区转换；即使使用TIMESTAMP，也需要在数据库中显式地配置时区，而不是用操作系统时区。

表结构设计规范，每条记录都要有一个时间字段

在做表结构设计时，强烈推荐你每张业务核心表都增加一个DATETIME类型的last_modify_date字段，并设置修改自动更新机制，即便标识每条记录最后修改的时间。这样设计的好处是：可以知道每个用户最近一次记录更新时间，以便做后续的处理。比如在电商的订单表中，可以方便对支付超时的订单做处理；在金融行业中，可以根据用户资金最后的修改时间做对应的资金核算等。

总结

• MySQL 5.6 版本开始DATETIME和TIMESTAMP精度支持到毫秒；
• DATETIME占用8个字节，TIMESTAMP占用4个字节，DATETIME(6)依然占用8个字节，TIMASTAMP(6)占用7个字节；
• TIMESTAMP日期存储的上限为2038-01-19 03:14:07，业务用TIMESTAMP存在风险；
• 使用TIMESTAMP必须显式地设置时区，不要使用默认操作系统时区，否则存在性能问题，推荐在配置文件中设置参数TIME_ZONE；
• 推荐日期类型使用DATETIME，而不是TIMESTAMP和INT类型；
• 表结构设计时，每个核心业务表，推荐设计一个last_modify_date的字段，用以记录每条记录的最后修改时间。