Mysql的timestamp(时间戳)详解以及2038问题的解决方案_mysql timestamp范围-程序员宅基地

技术标签: mysql  timstamp  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 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;

  1. 当建表语句中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的异同

参考链接:https://blog.csdn.net/z3278221/article/details/81000876

相同点:

  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_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是有上限的,超过了,自然会报错, 具体原因查看官方文档 : https://dev.mysql.com/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';
  1. 新建一个 datatime类型的字段(新建一列,用来替换原来的);
ALTER TABLE `student` ADD `entry_date` DATETIME NOT NULL default '0000-00-00 00:00:00';
  1. 将原来字段列的数据拷贝到新的字段列中;
UPDATE `student` SET `entry_date` = `temp_entry_date`;
  1. 删除原来的列;
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`;
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/weter_drop/article/details/89924451

智能推荐

npm install 错误问题 gyp ERR! configure error gyp ERR! stack Error: Command failed: C:\Users\Zhan_npm err! gyp err! configure error npm err! gyp err-程序员宅基地

文章浏览阅读2.1w次,点赞3次,收藏8次。刚开始使用vscode打开一个已写好的vue。1.根据网上教程,下载并配置好nodejs2.利用vscode打开已有vue文件3.使用命令行,输入npm install4.然后就报出如下错误localhost:react-first changwei$ npm install --save react-routernpm WARN deprecated [email protected]:..._npm err! gyp err! configure error npm err! gyp err! stack error: command fai

ContentProvider 与Content Resolver_怎么知道contentresolver update数据库失败-程序员宅基地

文章浏览阅读512次。ContentProvider 与Content Resolver_怎么知道contentresolver update数据库失败

linux磁盘及文件系统之三文件系统的管理工具_linux管理文件系统的工具-程序员宅基地

文章浏览阅读198次。`centos7`中,默认的文件系统类型已经变为了`xfs`,`centos6`默认不支持`xfs`类型的文件系统,可以通过`yum install xfsprogs`安装支持程序来实现。_linux管理文件系统的工具

短网址(short URL)系统的原理及其实现-程序员宅基地

文章浏览阅读158次。背景提供一个短址服务你有没有发现,我们的任务中出现长 URL 就会比较麻烦?如果有一个短址生成器就好了。虽然市面上有很多,但是我们可以重复发明一个轮子,利用这个机会尝试一下简单的 Web 全栈开发。任务做一个短链接生成器,可以将一个长链接缩短成一个短链接。要发车了 _只允许一个要被缩短网址对应一个短网址,若允许对应多个短网址写false

披着函数外衣的关键字sizeof-程序员宅基地

文章浏览阅读857次。通过这篇文章可以让大家更好的理解sizeof而不至于闹出sizeof是函数的笑话。

学习笔记6(opencv+python阈值分割(最大熵))_python 最大熵阈值法-程序员宅基地

文章浏览阅读2.9k次,点赞4次,收藏37次。@ 图像阈值分割(最大熵方法)老规矩,看相关函数(哈哈,没有啥函数)步骤1.进行归一化直方图2.累加概率直方图3.求出各个灰度级的熵4.计算最大熵时的阈值计算公式1.normHist为归一化的直方图,这里不做介绍2.累加概率直方图3.求出各个灰度级的熵4.计算最大熵时的阈值计算:f(t)=f1(t)+f2(t)最大化的t值,该值即为得到的阈值,即thresh=argmax(f(t))上代码#相关包import numpy as npimport cv2import im_python 最大熵阈值法

随便推点

CC2530入门篇————实现四盏灯全亮_cc2530控制四盏灯的亮灭-程序员宅基地

文章浏览阅读3.8k次。#include<iocc2530.h>//四个引脚分别对应板子上四个小灯#define LED1 P1_0#define LED2 P1_5#define LED3 P1_3#define LED4 P1_4void Init_LED(void){P1SEL&=~0x39;//功能寄存器 :0为普通IO口,1为第二功能–外设P1DIR|=0x39;//方向寄存器 :0为输入 , 1为输出P1&=~0x39;;//将四个灯熄灭}main(){Init__cc2530控制四盏灯的亮灭

android 中右上角的数字BadgeView_android badge 数字-程序员宅基地

文章浏览阅读5.2k次。转载自[https://github.com/jgilfelt/android-viewbadger]最近项目中有用到右上角有数字的效果,上网查了一下记录下来以供以后需要 BadgeView主要是继承了TextView,底层放了一个label,可以自定义背景图,自定义背景颜色,是否显示,显示进入的动画效果以及显示的位置等等;两张效果图 构造方法: View target = findVie_android badge 数字

区分聚集索引、非聚集索引、聚簇索引、稀疏索引、稠密索引_聚集索引 聚簇索引-程序员宅基地

文章浏览阅读1.4w次,点赞9次,收藏29次。转自:http://blog.csdn.net/douunderstand/article/details/702150611、聚集索引聚集索引:指索引项的排序方式和表中数据记录排序方式一致的索引 也就是说聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表中的数据,即对表的数据按索引键的顺序进行排序,然后重新存储到磁盘上。因为数据在物理存_聚集索引 聚簇索引

ssh数据源(c3p0+was)-程序员宅基地

文章浏览阅读114次。ssh web 应用使用c3p0数据库连接池ibm 给出的实例说明 将ssh web应用程序从tomcat迁移到websphere(was)WAS 中数据源的配置使用及其常见问题开源DBCP、C3P0、Proxool 、 BoneCP连接池的比较 ..._was数据源 ssh

实现点击通知栏发送广播控制音乐的播放、暂停、上一曲、下一曲_adb 发送上下一曲按键-程序员宅基地

文章浏览阅读3.4k次。Intent intent = new Intent(this,Main2Activity.class); PendingIntent pending = PendingIntent.getActivity(this, 0, intent, 0); builder.setContentIntent(pending); builder.setOngoi_adb 发送上下一曲按键

C# .NetCore知识点-程序员宅基地

文章浏览阅读6.4k次,点赞11次,收藏78次。1、.netCore的执行过程2、如何在controller中注入service?在Config Services方法中配置这个service在Controller的构造函数中 添加这个依赖注入3、.netCore比.net更具优势的地方是什么?跨平台,可以运行在 Windows 、Linux 和 MAC 系统上对框架本身安装没有依赖,所有依赖都和程序本身在一起.netCore处理请求的效率更高,进而可以处理更多的请求具有更多的安装配置方法4、.netCore主要的特性有哪些?依赖注入_.netcore