Mysql的timestamp时间戳2038问题怎么解决

发布时间:2023-04-27 14:43:47 作者:iii
来源:亿速云 阅读:191

本篇内容主要讲解“Mysql的timestamp时间戳2038问题怎么解决”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“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),数据精度提高的代价是其内部存储空间的变大,但仍未改变时间戳类型的最小和最大取值范围。

时间戳字段定义

时间戳字段定义主要影响两类操作:

PS1:CURRENT_TIMESTAMP表示使用CURRENT_TIMESTAMP()函数来获取当前时间,类似于NOW()函数

根据上面两类操作,时间戳列可以有四张组合定义,其含义分别为:

PS1:在MySQL中执行的建表语句和最终表创建语句会存在差异,建议使用SHOW CREATE TABLE TB_XXX获取已创建表的建表语句。

时间戳字段在MySQL各版本的使用差异

当建表语句中定于c1 timestamp 时,

当建表语句中c1 timestamp default 0时,

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’,能更好地存放各时间段的数据。

时间戳类型使用建议

在只关心数据最后更新时间的情况下,建议将时间戳列定义为TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

在关心创建时间和更新时间的情况下,建议将更新时间设置为时间戳字段,将创建时间定义为DAETIME 或 TIMESTAMP DEFAULT ‘0000-00-00 00:00:00’,并在插入记录时显式指定创建时间;

建议在表中只定义单个时间戳列,并显式定义DEFAULT 和 ON UPDATE属性;

虽然在MySQL中可以对时间戳字段赋值或更新,但建议仅在必要的情况下对时间戳列进行显式插入和更新;

建议将time_zone参数设置为system外的值,如中国地区服务器设置为’+8:00’;

建议将MySQL线下测试版本和线上生产版本保持一致。

Timestamp和datetime的异同

相同点:

不同点:

设置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_TIMESTAMP(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是有上限的,超过了,自然会报错。 部分截图如下:

Mysql的timestamp时间戳2038问题怎么解决

解决方案

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';

新建一个 datatime类型的字段(新建一列,用来替换原来的);

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`;

完整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`;

到此,相信大家对“Mysql的timestamp时间戳2038问题怎么解决”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

推荐阅读:
  1. 怎么搭建MySQL主从复制经典架构
  2. Mysql主从复制和读写分离的概念

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql timestamp

上一篇:react hooks useWhyDidYouUpdate源码分析

下一篇:uniapp怎么使用第三方UI库uview-plus

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》