在日常的数据库设计中，几乎每个业务表都有一个日期列来记录每条记录产生和改变的时间。比如user表会有一个date列记录用户注册的时间和上次登录的时间。再比如电商行业的订单表（核心业务表）会有一个订单生成时间列。当付款时间超过订单生成时间时，订单可能会被系统自动取消。日期类型虽然常见，但在表结构设计中也很容易出错。例如，很多开发者倾向于使用整型存储日期类型，同时忽略了不同日期类型对性能的潜在影响。所以大家有必要认真阅读本文，举一反三，做好自己业务中的日期类型设计。日期类型MySQL数据库中常见的日期类型有YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因为大部分业务场景都要求日期精确到秒，所以在表结构设计中，常用的日期类型有DATETIME和TIMESTAMP。接下来，我将带大家深入了解这两种类型以及它们在设计中的实际应用。DATETIME类型DATETIME的最终形式为：YYYY-MM-DDHH:MM:SS，占用8个字节。从MySQL5.6开始，DATETIME类型支持毫秒，DATETIME(N)中的N代表毫秒精度。例如，DATETIME(6)表示可以存储6位毫秒。同时一些日期函数也支持精确到毫秒，比如常用函数NOW和SYSDATE：mysql>SELECTNOW(6);+-----------------------------+|现在(6)|+----------------------------+|2020-09-1417:50:28.707971|+------------------------+1rowinset(0.00sec)用户可以设置DATETIME的初始值为当前时间，并设置属性自动更新当前时间。例如，用户表User定义了两个字段register_date和last_modify_date：CREATETABLEUser(idBIGINTNOTNULLAUTO_INCREMENT,nameVARCHAR(255)NOTNULL,sexCHAR(1)NOTNULL,passwordVARCHAR(1024)NOTNULL,moneyINTNOTNULLDEFAULT0,register_dateDATETIME(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6),last_modify_dateDATETIME(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6)ONUPDATECURRENT_TIMESTAMP(6),CHECK(sex='M'ORsex='F'),主键(id));在上表User中，列register_date表示注册时间，DEFAULTCURRENT_TIMESTAMP表示记录插入的时间。如果未指定时间，则默认为当前时间。last_modify_date列表示当前记录的最后修改时间，DEFAULTCURRENT_TIMESTAMP(6)ONUPDATECURRENT_TIMESTAMP(6)表示每次修改都会修改为当前时间。这样的设计保证了当用户的money（money字段）发生变化时，last_modify_date可以记录用户的money最后一次变化的时间。看下面的例子：mysql>SELECTname,money,last_modify_dateFROMUserWHEREname='David';+--------+--------+----------------------------+|姓名|钱|last_modify_date|+--------+--------+--------------------------+|大卫|100|2020-09-1308:08:33.898593|+-------+-------+--------------------------+1rowinset(0.00sec)mysql>UPDATEUserSETmoney=money-1WHEREname='David';QueryOK,1rowaffected(0.06sec)Rowsmatched:1Changed:1警告:0mysql>SELECTname,money,last_modify_dateFROMUserWHEREname='David';+-------+--------+--------------------------+|姓名|钱|last_modify_date|+------+--------+----------------------------+|大卫|99|2020-09-1418:29:17.056327|+------+--------+--------------------------+1rowinset(0.00sec)可以看出，当修改用户的金额时，对应的字段last_modify_date也修改为修改的时间。TIMESTAMP除了DATETIME之外，date类型中还有一个TIMESTAMP时间戳类型，它实际存储的是从'1970-01-0100:00:00'到现在的毫秒数。在MySQL中，由于TIMESTAMP类型占用4个字节，其存储时间上限只能达到'2038-01-1903:14:07'。与DATETIME类型一样，TIMESTAMP类型从MySQL5.6开始支持毫秒。与DATETIME不同，TIMESTAMP类型以毫秒为单位占用7个字节，而DATETIME无论是否存储毫秒信息均占用8个字节。TIMESTAMP类型最大的好处就是可以有时区属性，因为它本质上是由毫秒转换而来的。如果你的业务需要对应不同的国家时区，那么TIMESTAMP类型是一个不错的选择。比如在新闻业务中，用户通常想知道新闻发布时自己所在国家的时间，那么TIMESTAMP就是一个选项。此外，一些国家强制执行夏令时。根据不同的季节，人为向前或向后调整1小时，带有时区属性的TIMESTAMP类型可以自行解决这个问题。参数time_zone指定当前使用的时区。默认情况下，SYSTEM使用操作系统时区。用户可以通过该参数指定需要的时区。如果要使用TIMESTAMP的时区功能，可以通过如下语句将之前用户表User的注册时间字段类型由DATETIME(6)改为TIMESTAMP(6)：ALTERTABLEUserCHANGEregister_dateregister_dateTIMESTAMP(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6);此时通过设置不同的time_zone，可以观察到不同时区的注册时间：mysql>SELECTname,register_dateFROMUserWHEREname='David';+--------+----------------------------+|姓名|注册日期|+--------+--------------------------+|大卫|2018-09-1418:28:33.898593|+-------+--------------------------+1集合中的行（0.00秒）mysql>SETtime_zone='-08:00';查询正常，受影响的0行（0.00秒）mysql>SELECTname,register_dateFROMUserWHEREname='David';+-------+-----------------------------+|姓名|注册日期|+--------+----------------------------+|大卫|2018-09-1402:28:33.898593|+------+----------------------------+1rowinset(0.00sec)从上面的例子可以看出，中国时区是+08:00，美国时区是-08:00，所以换成美国，可以看到用户注册时间比之前晚了16个小时。当然，直接加减时区并不直观，需要对各国时区表非常熟悉。在MySQL中，可以直接设置时区名称，如：mysql>SETtime_zone='America/Los_Angeles';QueryOK,0rowsaffected(0.00sec)mysql>SELECTNOW();+-------------------+|现在（）|+--------------------+|2020-09-1420:12:49|+--------------------+1rowinset(0.00sec)mysql>SETtime_zone='Asia/Shanghai';QueryOK,0rowsaffected(0.00sec)mysql>SELECTNOW();+--------------------+|现在（）|+------------------------+|2020-09-1511:12:55|+--------------------+1rowinset(0.00sec)至此，你一定明白了时间字段类型。接下来，我将分享如何在实际业务设计中使用时间字段。业务表结构设计实战DATETIMEvsTIMESTAMPvsINT，如何选择？在进行表结构设计时，开发者通常有三种存储日期字段的选择：DATETIME、TIMESTAMP、INT。INT类型是直接存储'1970-01-0100:00:00'到现在的毫秒数，本质上和TIMESTAMP是一样的，所以用INT不如直接用TIMESTAMP。当然也有同学会认为INT比TIMESTAMP有更好的性能。不过，由于目前每个CPU每秒可以执行数亿次计算，因此无需担心这种转换的性能。更重要的是，在后期的运维和数据分析中使用INT存储日期会让DBA和数据分析师抓狂，INT的可操作性太差了。也有同学热衷于使用TIMESTEMP类型来存储日期，因为TIMESTAMP类型占用4个字节，是DATETIME存储空间的一半。但是如果时间精确到毫秒，TIMESTAMP需要7个字节，和DATETIME8个字节差别不大。另一方面，它非常接近TIMESTAMP'2038-01-1903:14:07'的最大值。这是开发者需要认真思考的问题。一般来说，我建议您使用DATETIME类型。对于时区问题，可以由前端或者这里的服务做一个转换，不一定要在数据库中解决。不要忽视TIMESTAMP的性能问题前面提到，TIMESTAMP的上限即将在2038年到来，届时业务将面临另一个类似于千年虫的问题。此外，TIMESTAMP有潜在的性能问题。虽然从毫秒到类型TIMESTAMP的转换本身不需要很多CPU指令，但这不会立即造成性能问题。但是，如果使用默认的操作系统时区，则每次使用时区计算时间都必须调用操作系统的底层系统函数__tz_convert()，并且该函数需要额外的加锁操作来保证操作此时尚未修改系统时区。因此，当发生大规模并发访问时，由于资源竞争激烈，会出现两个问题。性能不如DATETIME：DATETIME没有时区转换问题。性能抖动：当出现海量并发时，会出现性能抖动问题。为了优化TIMESTAMP的使用，强烈建议您使用显式时区而不是操作系统时区。例如，在配置文件中明确设置时区，而不是使用系统时区：[mysqld]time_zone="+08:00"最后，使用命令mysqlslap测试TIMESTAMP和DATETIME的性能。命令如下：#comparetime_zoneasSystem和Asia/Shanghaimysqlslap-uroot--number-of-queries=1000000--concurrency=100--query='SELECTNOW()'最终的性能比较是如下：从表中可以发现，显式指定时区的性能比直接使用OS时区要好很多。因此，不进行时区转换的日期字段推荐使用DATETIME。即使使用TIMESTAMP，您也需要在数据库中显式配置时区，而不是使用系统时区。表结构设计规范：每条记录必须有时间字段。在做表结构设计规范的时候，强烈建议大家在每一个业务核心表中都增加一个DATETIME类型的last_modify_date字段，并设置修改自动更新机制，即使标识每一个记录最后一次修改的时间。例如上表User中的字段last_modify_date用于表示最后修改时间：CREATETABLEUser(idBIGINTNOTNULLAUTO_INCREMENT,nameVARCHAR(255)NOTNULL,sexCHAR(1)NOTNULL,passwordVARCHAR(1024)NOTNULL,moneyINTNOTNULLDEFAULT0,register_dateDATETIME(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6),last_modify_dateDATETIME(6)NOTNULLDEFAULTCURRENT_TIMESTAMP(6)ONUPDATECURRENT_TIMESTAMP(6),CHECK(sex='M'或者sex='F'),PRIMARYKEY(id));通过字段last_modify_date定义的ONUPDATECURRENT_TIMESTAMP(6)，那么每次这条记录都会自动更新last_modify_date为当前时间。这样设计的好处是用户可以知道每个用户的最后一条记录被更新的时间，以便后续处理。比如在电商订单表中，方便处理已经逾期付款的订单；在金融服务中，可以根据用户资金的最后修改时间进行相应的资金检查。在后面的内容中，我们还会讲到MySQL数据库的主从逻辑数据校验的设计与实现，也会用到last_modify_date字段。综上所述，日期类型通常使用DATETIME和TIMESTAMP。但是由于TIMESTAMP类型的性能问题，建议大家尽量使用DATETIME类型。总结一下今天的重点：MySQL5.6开始支持DATETIME和TIMESTAMP精度到毫秒；DATETIME占8个字节，TIMESTAMP占4个字节，DATETIME(6)仍占8个字节，TIMESTAMP(6)占7个字节；TIMESTAMP日期存储上限为2038-01-1903:14:07，业务使用TIMESTAMP存在风险；使用TIMESTAMP时，必须明确设置时区，不要使用默认的系统时区，否则会出现性能问题，建议使用TIMESTAMP，在配置文件中设置参数time_zone='+08:00'；建议使用DATETIME而不是TIMESTAMP和INT作为日期类型；在设计表结构时，建议为每个核心业务表设计一个last_modify_date字段，记录每条记录最后一次被修改的时间。
