本文转载自微信公众号“马越君”，作者吴越君。转载本文请联系MayJun公众号。日期类型是我们数据库操作中比较常见的一种数据类型。相信使用TIMESTAMP类型的朋友很多，但是你真的了解吗？本文介绍在MySQL中使用TIMESTAMP类型遇到的一些潜在问题。最大时间限制相当于以后埋了个坑，因为系统的一些默认规则会触发日期的自动更新和系统默认时区的表现。发现问题的时候，也推荐一些我个人认为在date上还不错的解决方案，供参考。推荐的安装MySQL的方式是Docker。在本地安装MySQL。步骤也很简单。如下图，学习起来很方便，安装的可以忽略。$dockerpullmysql$dockerrun-itd--namemysql-test-p3306:3306-eMYSQL_ROOT_PASSWORD=123456mysql$dockerexec-itmysql-test/bin/sh$mysql-hlocalhost-uroot-p埋在未来的坑假设未来某一天2038对你来说，你执行了一条SQL来更新时间，第一次的值为'2038-01-1903:14:07'并且成功了，第二次的值为'2038-01-1903:14:08'报错据说传值无效，中间只有一秒的差别，看起来是正常的SQL！为什么？#1st更新$UPDATEuserSETbirthday='2038-01-1903:14:07'WHEREid=1;QueryOK,0rowsaffected(0.01sec)Rowsmatched:1Changed:0Warnings:0#Secondupdate$UPDATEuserSETbirthday='2038-01-1903:14:08'WHEREid=1;ERROR1292(22007):Incorrectdatetimevalue:'2038-01-1903:14:08'forcolumn'birthday'atrow1在MySQL中，由于TIMESTAMP类型占用的空间是4个字节，理论上它能存储的最大日期是“2038-01-1903:14:07”，而在占用的内存空间MySQL5.6及以后版本是7个字节，可以精确到毫秒和微秒，但是最大日期没有变。所以如果我们多设置一秒，就会报错。就系统而言，再多一点也不行。超过了就超过了。这个限制在MySQL官方11.2.2TheDATE,DATETIME,andTIMESTAMPTypes[1]中也有描述：TheTIMESTAMPdatatypeisusedforvaluesthatcontainbothdateandtimeparts.TIMESTAMPhasarangeof'1970-01-0100:00:01'UTCto'2038-01-1903:14:07'UTC.注意TIMESTAMP的自动更新。假设一个表有姓名和生日等字段。这里的自动更新是指当你修改表中的姓名字段，最后发现生日字段更新为系统当前时间。而这种情况并不是每次都会出现，它与MySQL系统中的一条规则**explicit_defaults_for_timestamp**有关，该参数的值默认为OFF。使用以下命令检查它。$showvariableslike'%explicit_defaults_for_timestamp%';+------------------------------------+-------+|Variable_name|Value|+--------------------------------+-------+|explicit_defaults_for_timestamp|OFF|+--------------------------------+--------+但是，这里很容易潜在地埋一些坑。有的MySQL镜像直接将这个值改成ON来关闭该功能。例如，通过上述Docker方法安装已禁用此功能。问题复现为了重现和解释这个问题，现在我需要释放这个功能，使用下面的命令。$SET@@SESSION.explicit_defaults_for_timestamp='ON';首先，让我们创建一个数据库和一个用户表。请注意，生日字段当前定义为生日TIMESTAMPNOTNULL。$CREATEDATABASEtest;$CREATETABLEuser(idBIGINTNOTNULLAUTO_INCREMENT,nameVARCHAR(20)NOTNULL,birthdayTIMESTAMPNOTNULL,PRIMARYKEY(id));执行DESC用户；命令查看当前表结构，发现birthday字段Extracolumn多了定义，为什么？DESCuser;+----------+------------+-----+-----+-------------------+--------------------------------------------+|Field|Type|Null|Key|Default|Extra|+----------+------------+------+-----+--------------------+-------------------------------------------+|id|bigint|NO|PRI|NULL|auto_increment||name|varchar(20)|否||NULL|||生日|时间戳|NO||CURRENT_TIMESTAMP|DEFAULT_GENERATEDonupdateCURRENT_TIMESTAMP|+----------+------------+------+-----+--------------------+-------------------------------------------+这个块中有一个默认规则。当启用explicit_defaults_for_timestamp规则时，将创建表中指定的TIMESTAMP类型的第一列。如果没有显示的语句使用NULL或DEFAULT或ONUPDATE。建表成功后，会自动给我们带来**DEFAULT_GENERATEDonupdateCURRENT_TIMESTAMP**属性语句。对应我们的例子就是上面定义的生日TIMESTAMPNOTNULL。如果这样设置，则表示修改数据，该类型对应的字段将更改为数据库当前系统日期。规则下，一张表中只有一个字段可以有这个特性，如果设置了两个，会报错。$CREATETABLEuser(birthdayTIMESTAMPNOTNULL,utimeTIMESTAMPNOTNULL,);//运行后会得到一个showvariableslike'%explicit_defaults_for_timestamp%';错误。在user表中插入一条数据。$INSERTINTOuser(name,birthday)VALUES('Tom',NOW(6));假设当前时间点为T1（T1当前时间为2021-01-0106:10:27），查看当前用户表数据。$SELECT*FROMuser;+----+------+----------------+|id|name|birthday|+----+------+--------------------+|1|汤姆|2021-06-0606:10:27|+----+------+--------------------+假设当前时间点为T2（T2当前时间为2021-01-0106:13:06)更新user表中的name为Tom2，查看返回结果Changed:1更新成功。$UPDATEuserSETname='Tom2'WHEREid=1;QueryOK,1rowaffected(0.02sec)Rowsmatched:1Changed:1Warnings:0再次查询发现birthday字段的值已经改为T2了，但是显然上面的SQL语句没有更新生日这个字段！为什么？$SELECT*FROMuser;+----+------+--------------------+|id|name|birthday|+----+------+--------------------+|1|Tom2|2021-06-0606:13:06|+----+-------+--------------------+解决方案当explicit_defaults_for_timestamp规则启用时（它的值为OFF），如果我们这样做没有给TIMESTAMP类型的字段显式赋值，更新时系统会默认为我们设置为当前系统时间。如果你不知道这个问题，找到它会很令人沮丧。很明显，SQL语句不在了，但还是更新了。在大多数情况下，这不是我们想要的。如何禁用它？方法一：修改系统参数，将explicit_defaults_for_timestamp的值修改为'ON'，关闭该属性。它正在运行，可以使用SET@@SESSION.explicit_defaults_for_timestamp='ON'进行修改；这里还有一个坑。经测试验证，一旦创建表，设置无效。如果表是在禁用规则后创建的，则有可能。方法二：修改表结构对于那些不能修改的在线运行，不能直接删除表再修改。当explicit_defaults_for_timestamp属性为OFF时，也有两种方法可以禁用，需要修改表结构。//指定列为NULL，比如$ALTERTABLEuserMODIFYbirthdayTIMESTAMPNULL。//使用DEFAULT为列指定一个默认值，比如$ALTERTABLEuserMODIFYbirthdayTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP最后，根据MYSQL官网文档sysvar_explicit_defaults_for_timestamp[2]的描述，这种非标准的行为已经被弃用，希望他们会在未来的MYSQL版本中被删除。这确实是一个棘手的行为。如果不熟悉文档，很容易踩到陷阱。TIMESTAMP性能问题TIMESTAMP类型支持时区转换，有优点也有缺点。当默认为操作系统时区（time_zone=SYSTEM）时，查询系统TIMESTAMP类型字段会调用系统时区进行时区转换。并且需要锁定这个系统时区，保证此时操作系统时区没有被修改。当并发访问时，必然会出现资源竞争、多线程上下文切换消耗、性能下降等问题。下面我们来做一个性能测试。查看当前时区信息，time_zone=SYSTEM表示这是操作系统的时区。$showvariableslike"%time_zone%";+----------------+--------+|Variable_name|Value|+-------------------+--------+|system_time_zone|UTC||time_zone|SYSTEM|+------------------+--------+时区修改MySQL默认使用系统时区，修改方式大致有两种：使用SQL命令临时修改，通过修改配置文件永久修改。#修改SQL命令$SETtime_zone='Asia/Shanghai';#配置文件$vim/etc/mysql/my.cnfdefault-time_zone='Asia/Shanghai'如果使用Docker，可以在dockerrun时修改。时区是通过-eTZ='Asia/Shanghai'指定的，但是发现虽然SELECTNOW()没问题，但是执行showvariableslike"%time_zone%"命令时time_zone还是显示为SYSTEM.$dockerrun-itd--namemysql-test-p3306:3306-eMYSQL_ROOT_PASSWORD=123456-eTZ='Asia/Shanghai'mysql建议修改文件，先进入容器执行cat/etc/mysql/conf.d/mysql。cnf命令查看默认配置，复制一份到本地电脑，执行dockerrun时挂载到容器中。这种方式的好处是，当你有多个配置需要修改时，可以在配置文件中修改。配置文件可能如下所示：[mysqld]pid-file=/var/run/mysqld/mysqld.pidsocket=/var/run/mysqld/mysqld.sockdatadir=/var/lib/mysqlsecure-file-priv=NULLdefault-time_zone='Asia/Shanghai'#Customconfigshouldgohere!includedir/etc/mysql/conf.d/最后的dockerrun命令如下：#Note/${root}/mysql.cnf这是你本机的配置地址$dockerrun-itd--namemysql-test-p3306:3306-eMYSQL_ROOT_PASSWORD=123456-v/${root}/mysql.cnf:/etc/mysql/my.cnfmysql性能测试mysql自带压力测试工具mysqlslap，可以模拟多个并发客户端对MySQL做一个压力测试还是蛮不错的。写一些功能，想测试基本性能的时候还是可以用的。下面语句的意思是模拟100个并发客户端，总共执行1,000,000个查询。#--number-of-queries测试查询总数#-c并发，模拟多客户端执行，下面的例子模拟多客户端执行"SELECTNOW()"#--create-schema代表自定义测试库名是MySQL中的数据库名$mysqlslap-uroot-p--number-of-queries=1000000--create-schema=test-c100--query='SELECTNOW()'下面是基于mysqlslap的性能测试结果，在不同的时区，分别花费的时间单位（秒），很明显系统时区耗时更长，两者直接相差25%。这只是耗时的区别，我没看CPU信息。也有不同的电脑，测试出来的性能差距也会不同。------SystemAsia/Shanghai差异运行所有查询的平均秒数35.55s28.42s25%如何选择日期？MySQL中日期类型的存储通常有3种方案，分别使用INT、TIMESTAMP和DATETIME。下面简单总结一下。INT类型INT类型用来存储日期类型，是时间戳类型，比如2021-01-0106:10:27的时间戳是1609452627000。数据库实际存储的是一串数字。这个优点是没有时间上下限，性能比TIMESTAMP好，但是这个性能影响不大。一个不友好的问题是，当我们想要查看数据，做一些问题排查或者数据分析的时候，通常不是很直观。TIMESTAMP类型TIMESTAMP类型在存储时会先将本地时区时间转换成UTC时区时间，再将UTC时区时间转换成4字节的INT类型存储，本质上和INT一样，都是以毫秒为单位存储的。读取时，再次反向转换为时间戳TIMESTAMP类型，还会做一些时间格式化，看起来更直观。相对较大的TIMESTAMP类型的一个问题是它有最大时间限制。可以有效存储的时间范围是“'1970-01-0100:00:01.000000'到'2038-01-1903:14:07.999999'”，2038年的时间也很快，这个需要要考虑的，不要给以后埋坑。虽然TIMESTAMP类型在5.6版本之后支持精度到微笑，毫秒之后支持6，但是最大时间限制2038的问题还没有解决。它还有一个笔者个人认为非常隐蔽的问题。当你定义一个字段为birthdayDATETIMENOTNULL并触发其自动更新规则时，很容易掉坑。可怕的是开发和生产环境配置不一致。除非踩到这个坑，否则前期是发现不了这种问题的。DATETIME类型DATETIME这种类型是作者推荐的，占用8个字节，可以存储的精度为微秒，在声明类型的时候由DATETIME(6)指定。它的时间范围是'1000-01-0100:00:00'到'9999-12-3123:59:59'。这段时间对我们来说已经足够了。当然，如果要说我要存储张飞在“三国时期”出生的时间，那么160岁生日就存储不了了。DATETIME类型不存储时区信息。当然这个问题不一定要在数据层解决，也不是什么大问题。如果要做这种跨时区的国际，由中间层来服务（Node.js很适合）也可以统一解决。我觉得这个日期类型可以解决我们上面用TIMESTAMP遇到的问题。修改上面的用户表结构，将日期类型统一声明为DATETIME类型。生日字段由用户传入，指定为非空。DATETIME声明它以秒为单位是准确的。ctime字段默认为当前时间，只在创建时指定，时间精确到微秒。utime字段记录了每次更新的时间，不受explicit_defaults_for_timestamp参数的影响，在我们定义了ONUPDATE...之后也会触发自动更新，如图所示。CreateTableUser（iDbigintNullauto_Increment，nameVarchar（20）notnull，birthdydatetimenotnull，ctimeDateTime（6）notnulldefcurrent_timestamp（6）2039用DATETIME是没问题的，同时也可以看看ctime和utime时间。这个精度也是我们定义的。+---+------+--------------------+-------------------------+----------------------------+|id|姓名|生日|ctime|utime|+----+------+--------------------+--------------------------+----------------------------+|1|汤姆|2039-01-0122:00:28|2021-01-0122:00:28.112048|2021-01-0122:00:28.112048|+----+-----+-------------------+----------------------------+--------------------------+参考文献[1]11.2.2DATE、DATETIME和TIMESTAMP类型：https://dev.mysql。com/doc/refman/8.0/en/datetime.html[2]sysvar_explicit_defaults_for_timestamp：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
