警告！别再使用TIMESTAMP作为日期字段

　　原文链接：https：mp。weixin。qq。comsTNLJyhxibAB1bC3nHCELXQ
　　来源：JAVA日知录
　　在日常数据库设计中，几乎每张业务表都带有一个日期列，用于记录每条记录产生和变更的时间。比如用户表会有一个日期列表记录用户注册的时间、用户最后登录的时间。又比如，电商行业中的订单表（核心业务表）会有一个订单产生的时间列，当支付时间超过订单产生的时间，这个订单可能会被系统自动取消。
　　日期类型虽然常见，但在表结构设计中也容易犯错，比如很多开发同学都倾向使用整型存储日期类型，同时也会忽略不同日期类型对于性能可能存在的潜在影响。所以你有必要认真看看这篇文章，举一反三，在自己的业务中做好日期类型的设计。日期类型
　　MySQL数据库中常见的日期类型有YEAR、DATE、TIME、DATETIME、TIMESTAMEP。因为业务绝大部分场景都需要将日期精确到秒，所以在表结构设计中，常见使用的日期类型为DATETIME和TIMESTAMP。接下来，我就带你深入了解这两种类型，以及它们在设计中的应用实战。DATETIME
　　类型DATETIME最终展现的形式为：YYYYMMDDHH：MM：SS，固定占用8个字节。
　　从MySQL5。6版本开始，DATETIME类型支持毫秒，DATETIME（N）中的N表示毫秒的精度。
　　例如，DATETIME（6）表示可以存储6位的毫秒值。同时，一些日期函数也支持精确到毫秒，例如常见的函数NOW、SYSDATE：mysqlSELECTNOW（6）；NOW（6）2020091417：50：28。7079711rowinset（0。00sec）
　　用户可以将DATETIME初始化值设置为当前时间，并设置自动更新当前时间的属性。例如用户表User有registerdate、lastmodifydate两个字段的定义：CREATETABLEUser（idBIGINTNOTNULLAUTOINCREMENT，nameVARCHAR（255）NOTNULL，sexCHAR（1）NOTNULL，passwordVARCHAR（1024）NOTNULL，moneyINTNOTNULLDEFAULT0，registerdateDATETIME（6）NOTNULLDEFAULTCURRENTTIMESTAMP（6），lastmodifydateDATETIME（6）NOTNULLDEFAULTCURRENTTIMESTAMP（6）ONUPDATECURRENTTIMESTAMP（6），CHECK（sexMORsexF），PRIMARYKEY（id））；
　　在上面的表User中，列registerdate表示注册时间，DEFAULTCURRENTTIMESTAMP表示记录插入时，若没有指定时间，默认就是当前时间。
　　列lastmodifydate表示当前记录最后的修改时间，DEFAULTCURRENTTIMESTAMP（6）ONUPDATECURRENTTIMESTAMP（6）表示每次修改都会修改为当前时间。
　　这样的设计保证了用户的金钱（money字段）发生了变更，则是lastmodifydate能记录最后一次用户金钱发生变更时的时间。来看下面的例子：mysqlSELECTname，money，lastmodifydateFROMUserWHEREnameDnamemoneylastmodifydateDavid1002020091308：08：33。8985931rowinset（0。00sec）mysqlUPDATEUserSETmoneymoney1WHEREnameDQueryOK，1rowaffected（0。06sec）Rowsmatched：1Changed：1Warnings：0mysqlSELECTname，money，lastmodifydateFROMUserWHEREnameDnamemoneylastmodifydateDavid992020091418：29：17。0563271rowinset（0。00sec）
　　可以看到，当用户金额发生修改时，所对应的字段lastmodifydate也修改成发生变更的时间。TIMESTAMP
　　除了DATETIME，日期类型中还有一种TIMESTAMP的时间戳类型，其实际存储的内容为‘1970010100：00：00’到现在的毫秒数。在MySQL中，由于类型TIMESTAMP占用4个字节，因此其存储的时间上限只能到‘2038011903：14：07’。
　　同类型DATETIME一样，从MySQL5。6版本开始，类型TIMESTAMP也能支持毫秒。与DATETIME不同的是，若带有毫秒时，类型TIMESTAMP占用7个字节，而DATETIME无论是否存储毫秒信息，都占用8个字节。
　　类型TIMESTAMP最大的优点是可以带有时区属性，因为它本质上是从毫秒转化而来的。如果你的业务需要对应不同的国家时区，那么类型TIMESTAMP是一种不错的选择。比如新闻类的业务，通常用户想知道这篇新闻发布时对应的自己国家的时间，那么TIMESTAMP是一种选择。
　　另外，有些国家会执行夏令时。根据不同的季节，人为地调快或调慢1个小时，带有时区属性的TIMESTAMP类型本身就能解决这个问题。
　　参数timezone指定了当前使用的时区，默认为SYSTEM使用操作系统时区，用户可以通过该参数指定所需要的时区。
　　如果想使用TIMESTAMP的时区功能，你可以通过下面的语句将之前的用户表User注册时间字段类型从DATETIME（6）修改为TIMESTAMP（6）：ALTERTABLEUserCHANGEregisterdateregisterdateTIMESTAMP（6）NOTNULLDEFAULTCURRENTTIMESTAMP（6）；
　　这时通过设定不同的timezone，可以观察到不同时区下的注册时间：mysqlSELECTname，registerdateFROMUserWHEREnameDnameregisterdateDavid2018091418：28：33。8985931rowinset（0。00sec）mysqlSETtimezone08：00；QueryOK，0rowsaffected（0。00sec）mysqlSELECTname，registerdateFROMUserWHEREnameDnameregisterdateDavid2018091402：28：33。8985931rowinset（0。00sec）
　　从上述例子中，你可以看到，中国的时区是08：00，美国的时区是08：00，因此改为美国时区后，可以看到用户注册时间比之前延迟了16个小时。当然了，直接加减时区并不直观，需要非常熟悉各国的时区表。
　　在MySQL中可以直接设置时区的名字，如：mysqlSETtimezoneAmericaLosAQueryOK，0rowsaffected（0。00sec）mysqlSELECTNOW（）；NOW（）2020091420：12：491rowinset（0。00sec）mysqlSETtimezoneAsiaSQueryOK，0rowsaffected（0。00sec）mysqlSELECTNOW（）；NOW（）2020091511：12：551rowinset（0。00sec）
　　讲到这儿，想必你已经了解了时间字段类型，接下来我将分享在真实业务设计中如何使用好时间类型。业务表结构设计实战DATETIMEvsTIMESTAMPvsINT，怎么选？
　　在做表结构设计时，对日期字段的存储，开发人员通常会有3种选择：DATETIME、TIMESTAMP、INT。
　　INT类型就是直接存储1970010100：00：00到现在的毫秒数，本质是和TIMESTAMP一样，因此用INT不如直接使用TIMESTAMP。
　　当然，有些同学会认为INT比TIMESTAMP性能更好。但是，由于当前每个CPU每秒可执行上亿次的计算，所以无须为这种转换的性能担心。更重要的是，在后期运维和数据分析时，使用INT存储日期，是会让DBA和数据分析人员发疯的，INT的可运维性太差。
　　也有的同学会热衷用类型TIMESTEMP存储日期，因为类型TIMESTAMP占用4个字节，比DATETIME小一半的存储空间。
　　但若要将时间精确到毫秒，TIMESTAMP要7个字节，和DATETIME8字节差不太多。另一方面，现在距离TIMESTAMP的最大值‘2038011903：14：07’已经很近，这是需要开发同学好好思考的问题。
　　总的来说，我建议你使用类型DATETIME。对于时区问题，可以由前端或者服务这里做一次转化，不一定非要在数据库中解决。不要忽视TIMESTAMP的性能问题
　　前面已经提及，TIMESTAMP的上限值2038年很快就会到来，那时业务又将面临一次类似千年虫的问题。另外，TIMESTAMP还存在潜在的性能问题。
　　虽然从毫秒数转换到类型TIMESTAMP本身需要的CPU指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数tzconvert（），而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以，当大规模并发访问时，由于热点资源竞争，会产生两个问题。性能不如DATETIME：DATETIME不存在时区转化问题。性能抖动：海量并发时，存在性能抖动问题。
　　为了优化TIMESTAMP的使用，强烈建议你使用显式的时区，而不是操作系统时区。比如在配置文件中显示地设置时区，而不要使用系统时区：〔mysqld〕timezone08：00
　　最后，通过命令mysqlslap来测试TIMESTAMP、DATETIME的性能，命令如下：比较timezone为System和AsiaShanghai的性能对比mysqlslapurootnumberofqueries1000000concurrency100querySELECTNOW（）
　　最后的性能对比如下：
　　图片
　　从表中可以发现，显式指定时区的性能要远远好于直接使用操作系统时区。所以，日期字段推荐使用DATETIME，没有时区转化。即便使用TIMESTAMP，也需要在数据库中显式地配置时区，而不是用系统时区。表结构设计规范：每条记录都要有一个时间字段
　　在做表结构设计规范时，强烈建议你每张业务核心表都增加一个DATETIME类型的lastmodifydate字段，并设置修改自动更新机制，即便标识每条记录最后修改的时间。
　　例如，在前面的表User中的字段lastmodifydate，就是用于表示最后一次的修改时间：CREATETABLEUser（idBIGINTNOTNULLAUTOINCREMENT，nameVARCHAR（255）NOTNULL，sexCHAR（1）NOTNULL，passwordVARCHAR（1024）NOTNULL，moneyINTNOTNULLDEFAULT0，registerdateDATETIME（6）NOTNULLDEFAULTCURRENTTIMESTAMP（6），lastmodifydateDATETIME（6）NOTNULLDEFAULTCURRENTTIMESTAMP（6）ONUPDATECURRENTTIMESTAMP（6），CHECK（sexMORsexF），PRIMARYKEY（id））；
　　通过字段lastmodifydate定义的ONUPDATECURRENTTIMESTAMP（6），那么每次这条记录，则都会自动更新lastmodifydate为当前时间。
　　这样设计的好处是：用户可以知道每个用户最近一次记录更新的时间，以便做后续的处理。比如在电商的订单表中，可以方便对支付超时的订单做处理；在金融业务中，可以根据用户资金最后的修改时间做相应的资金核对等。
　　在后面的内容中，我们也会谈到MySQL数据库的主从逻辑数据核对的设计实现，也会利用到lastmodifydate字段。总结
　　日期类型通常就是使用DATETIME和TIMESTAMP两种类型，然而由于类型TIMESTAMP存在性能问题，建议你还是尽可能使用类型DATETIME。我总结一下今天的重点内容：MySQL5。6版本开始DATETIME和TIMESTAMP精度支持到毫秒；DATETIME占用8个字节，TIMESTAMP占用4个字节，DATETIME（6）依然占用8个字节，TIMESTAMP（6）占用7个字节；TIMESTAMP日期存储的上限为2038011903：14：07，业务用TIMESTAMP存在风险；使用TIMESTAMP必须显式地设置时区，不要使用默认系统时区，否则存在性能问题，推荐在配置文件中设置参数timezone08：00；推荐日期类型使用DATETIME，而不是TIMESTAMP和INT类型；表结构设计时，每个核心业务表，推荐设计一个lastmodifydate的字段，用以记录每条记录的最后修改时间。