Mysql的timestamp（时间戳）详解以及2038问题的解决⽅案
时间戳是指格林威治时间1970年01⽉01⽇00时00分00秒(北京时间1970年01⽉01⽇08时00分00秒)起⾄现在的总秒数。
⽣产环境中部署着各种版本的MySQL，包括MySQL 5.5/5.6/5.7三个⼤版本和N个⼩版本，由于MySQL在向上兼容性较差，导致相同SQL在不同版本上表现各异，下⾯从⼏个⽅⾯来详细介绍时间戳数据类型。
时间戳数据存取
在MySQL上述三个⼤版本中，默认时间戳(Timestamp)类型的取值范围为’1970-01-01 00:00:01’ UTC ⾄’2038-01-19
03:14:07’ UTC，数据精确到秒级别，该取值范围包含约22亿个数值，因此在MySQL内部使⽤4个字节INT类型来存放时间戳数据：
1、在存储时间戳数据时，先将本地时区时间转换为UTC时区时间，再将UTC时区时间转换为INT格式的毫秒值(使⽤UNIX_TIMESTAMP函数)，然后存放到数据库中。
2、在读取时间戳数据时，先将INT格式的毫秒值转换为UTC时区时间(使⽤FROM_UNIXTIME函数)，然后再转换为本地时区时间，最后返回给客户端。
在MySQL 5.6.4及之后版本，可以将时间戳类型数据最⾼精确微秒(百万分之⼀秒)，数据类型定义为timestamp(N),N取值范围为0-6，默认为0，如需要精确到毫秒则设置为Timestamp(3)，如需要精确到微秒则设置为timestamp(6)，数据精度提⾼的代价是其内部存储空间的变⼤，但仍未改变时间戳类型的最⼩和最⼤取值范围。
时间戳字段定义
时间戳字段定义主要影响两类操作：
1. 插⼊记录时，时间戳字段包含DEFAULT CURRENT_TIMESTAMP，如插⼊记录时未指定具体时间数据则将该时间戳字段值设置为当
前时间
2. 更新记录时，时间戳字段包含ON UPDATE CURRENT_TIMESTAMP，如更新记录时未指定具体时间数据则将该时间戳字段值设置
为当前时间
PS1：CURRENT_TIMESTAMP表⽰使⽤CURRENT_TIMESTAMP()函数来获取当前时间，类似于NOW()函数
根据上⾯两类操作，时间戳列可以有四张组合定义，其含义分别为：
1. 当字段定义为timestamp，表⽰该字段在插⼊和更新时都不会⾃动设置为当前时间。
2. 当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP，表⽰该字段仅在插⼊且未指定值时被赋予当前时间，再更新时且未
指定值时不做修改。
3. 当字段定义为timestamp ON UPDATE CURRENT_TIMESTAMP，表⽰该字段在插⼊且未指定值时被赋值为"0000-00-00
00:00:00"，在更新且未指定值时更新为当前时间。
4. 当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，表⽰该字段在插⼊或更新
时未指定值，则被赋值为当前时间。
PS1：在MySQL中执⾏的建表语句和最终表创建语句会存在差异，建议使⽤SHOW CREATE TABLE TB_XXX获取已创建表的建表语句。
时间戳字段在MySQL各版本的使⽤差异
1. 在MySQL 5.5及之前版本中，仅能对⼀个时间戳字段定义DEFUALT CURRENT_TIMESTAMP或ON UPDATE
CURRENT_TIMESTAMP，但在MySQL 5.6和MySQL 5.7版本中取消了该限制;
2. 在MySQL 5.6版本中参数explicit_defaults_for_timestamp默认值为1，在MySQL 5.7版本中参数
explicit_defaults_for_timestamp默认值为0;
3. 在MySQL 5.5和MySQL 5.7版本中timestamp类型默认为NOT NULL，在在MySQL 5.6版本中timestamp类型默认为NULL;
4. 当建表语句中定于c1 timestamp 时，mysql操作官方文档
在MySQL 5.5中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
在MySQL 5.6中等价于c1 timestamp NULL DEFAULT NULL;
在MySQL 5.7中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
5. 当建表语句中c1 timestamp default 0时，
在MySQL 5.5中等价于c1 timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’;
在MySQL 5.6中等价于c1 timestamp NULL DEFAULT ‘0000-00-00 00:00:00’;
在MySQL 5.7中等价于c1 timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’;
PS1: MySQL 5.6版本和MySQL 5.7版本中主要差异受参数explicit_defaults_for_timestamp的默认值影响。
PS2：当时间戳列的默认值为’0000-00-00 00:00:00’时，使⽤“不在时间戳取值范围内”的该默认值并不会产⽣警告。
时间戳类型引发的异常
当MySQL参数time_zone=system时，查询timestamp字段会调⽤系统时区做时区转换，⽽由于系统时区存在全局锁问题，在多并发⼤数据量访问时会导致线程上下⽂频繁切换，CPU使⽤率暴涨，系统响应变慢设置假死。
时间戳类型和时间类型选择
在部分"数据库指导"⽂档中，会推荐使⽤timestamp类型代替datetime字段，其理由是timestamp类型使⽤4字节，⽽datetime字段使⽤8字节，但随着磁盘性能提升和内存成本降低，在实际⽣产环境中，使⽤timestamp类型并不会带来太多性能提升，反⽽可能因timestamp 类型的定义和取值范围限制和影响业务使⽤。
在MySQL 5.6.4及之后版本，可以将时间戳类型(timestamp)数据最⾼精确微秒，也同样可以将时间类型(datetime)数据最⾼精确微秒，时间类型(datetime)同样可以获得timestamp类型相同的效果，如将字段定义为 dt1 DATETIME(3) NOT NULL DEFAULT NOW(3) ON UPDATE NOW(3); 时间类型(datetime)的存取范围’1000-01-01 00:00:00.000000’ ⾄ ‘9999-12-31 23:59:59.999999’，能更好地存放各时间段的数据。
时间戳类型使⽤建议
1. 在只关⼼数据最后更新时间的情况下，建议将时间戳列定义为TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP；
2. 在关⼼创建时间和更新时间的情况下，建议将更新时间设置为时间戳字段，将创建时间定义为DAETIME 或 TIMESTAMP DEFAULT
‘0000-00-00 00:00:00’，并在插⼊记录时显式指定创建时间；
3. 建议在表中只定义单个时间戳列，并显式定义DEFAULT 和 ON UPDATE属性；
4. 虽然在MySQL中可以对时间戳字段赋值或更新，但建议仅在必要的情况下对时间戳列进⾏显式插⼊和更新；
5. 建议将time_zone参数设置为system外的值，如中国地区服务器设置为’+8:00’；
6. 建议将MySQL线下测试版本和线上⽣产版本保持⼀致。
Timestamp和datetime的异同
相同点：
1. 可⾃动更新和初始化，默认显⽰格式相同YYYY-MM-dd HH:mm:ss
不同点：
2. timestamp的时间范围是：‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC ，⾃动时区转化，实际存储毫秒数，4字节存储
3. datetime的时间范围：‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ ，不⽀持时区，8字节存储
设置timestamp和date的⾃动更新时间
当对某条数据进⾏更新操作时或者插⼊⼀条新的数据⽽没有对date和mydate进⾏赋值，date和mydate这两个字段会⾃动默认为当前时间
CREATE TABLE `mytime` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMEST
AMP(6),
`mydate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
2038问题
当 timestamp 存储的时间⼤于 '2038-01-19 03:14:07' UTC，mysql就会报错， 因为这是 mysql⾃⾝的问题，也就是说 timestamp是有上限的，超过了，⾃然会报错， 具体原因查看官⽅⽂档 ： sql/doc/refman/8.0/en/datetime.html， 部分截图如下：
解决⽅案
timestamp 虽然有上限限制，但是它保存的是时间戳，可以不⽤去考虑时区的问题，如果是需要处理与时区相关的需求， 解决 2038 限制的时候，建议将 timestamp改为整数类型，⽤来保存时间戳，在程序中再进⾏转换（这个⽅案没有实施过，仅仅是建议，慎⽤！！）
如果不需要考虑时区问题，直接⽤ datatime类型替换 timestamp即可，因为datatime的取值范围⼤很多，可看上图；
替换的思路：
1. 修改原来字段的名字；
ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00';
2. 新建⼀个 datatime类型的字段（新建⼀列，⽤来替换原来的）；
ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';
3. 将原来字段列的数据拷贝到新的字段列中；
UPDATE `student` SET `entry_date` = `temp_entry_date`;
4. 删除原来的列；
ALTER TABLE `student` DROP `temp_entry_date`;
完整sql 如下：（需要注意，原来的 timestamp的默认值，这个也需要加上）
ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL default '0000-00-00 00:00:00'; ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';
UPDATE `student` SET `entry_date` = `temp_entry_date`;
ALTER TABLE `student` DROP `temp_entry_date`;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。