,,mysql之TIMESTAMP（时间戳）用法详解

2022-11-11 15:16:58 分类：IT百科 阅读()
,,mysql之TIMESTAMP（时间戳）用法详解

本文主要介绍mysql的时间戳的使用，有需要的朋友可以参考一下。

目录

时间戳数据访问时间戳字段在不同版本的mysql中使用不同的时间戳类型定义时间戳字段异常时间戳类型和时间类型选择时间戳类型使用时间戳和日期时间之间的建议异同来设置时间戳和日期时间戳的自动更新时间MySQL 1的时间戳用法，时间戳II的变体。时间戳III的列类型。在指定日期值时要提防一些缺陷：时间戳是指从1970年1月1日00: 00: 00 GMT(北京时间1970年1月1日08: 00: 00)到现在的总秒数。

在生产环境中部署了各种版本的MySQL，包括MySQL 5.5/5.6/5.7的三个主要版本和n个次要版本。由于MySQL向上兼容性差，同一条SQL在不同版本中表现不同。下面从几个方面详细介绍时间戳数据类型。

时间戳数据存取

在MySQL的上述三大版本中，默认时间戳类型的取值范围为' 1970-01-01 00:00:01' UTC到' 2038-01-19 03:14:07' UTC，数据精确到秒级。这个值范围包含了大约22亿个值，所以在MySQL中使用了4.0%。

1.存储时间戳数据时，先将当地时区时间转换为UTC时区时间，再将UTC时区时间转换为INT格式的毫秒值(使用UNIX_TIMESTAMP函数)，然后存储在数据库中。

2.读取时间戳数据时，先将INT格式的毫秒值转换为UTC时区(使用FROM_UNIXTIME函数)，再转换为本地时区，最后返回给客户端。

在MySQL 5 . 6 . 4及以后版本中，时间戳类型的数据最高精度可以是微秒(百万分之一秒)，数据类型定义为timestamp(N)，N)，取值范围为0-6，默认值为0。如果需要精确到毫秒，则设置为时间戳(3)，如果需要精确到微秒，则设置为时间戳(6)。提高数据精度的代价是其内部存储空间的增加。

时间戳字段定义

时间戳字段定义主要影响两种类型的操作：

插入记录时，时间戳字段包含默认的CURRENT_TIMESTAMP，如果插入记录时没有指定具体的时间数据，时间戳字段值将被设置为当前时间。

当记录更新时，时间戳字段包含ON UPDATE CURRENT_TIMESTAMP。如果在更新记录时没有指定具体的时间数据，时间戳字段值将被设置为当前时间。

PS1: CURRENT_TIMESTAMP意味着使用CURRENT_TIMESTAMP()函数获取当前时间，类似于NOW()函数。

根据以上两种类型的操作，时间戳列可以有四种组合定义，它们的含义是：

当一个字段被定义为时间戳时，意味着该字段在插入和更新时不会自动设置为当前时间。

当该字段被定义为timestamp default current _ timestamp时，意味着该字段只有在被插入且未指定值时才被赋予当前时间，并且在被更新且未指定值时不会被修改。

当该字段被定义为update current _ timestamp上的timestamp时，这意味着在插入时没有指定值，该字段被赋值为“0000-00-00 00:00:00 ”,在更新时没有指定值，该字段被更新为当前时间。

当该字段被定义为timestamp default current _ timestamp on update current _ timestamp时，这意味着在插入或更新该字段时没有指定值，它被分配给当前时间。

PS1:在MySQL中执行的表创建语句和最终的表创建语句是有区别的。建议使用SHOW CREATE TABLE TB_XXX获取所创建表的表创建语句。

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

在MySQL 5.5和更早版本中，只能将一个时间戳字段定义为discount current _ timestamp或ON UPDATE CURRENT_TIMESTAMP，但在MySQL 5.6和MySQL 5.7版本中取消了这一限制。

在MySQL 5.6中，explicit _ defaults _ for _ timestamp的默认值为1；在MySQL 5.7中，explicit _ defaults _ for _ timestamp的默认值为0；

在MySQL 5.5和MySQL 5.7中，时间戳类型默认不为空；在MySQL 5.6中，时间戳类型默认为NULL

当在建表语句中设置c1时间戳时，

相当于MySQL 5.5中更新current _ timestamp时C1时间戳不为null默认current _ timestamp；

相当于MySQL 5.6中的c1时间戳NULL默认NULL；

相当于MySQL 5.7中更新current _ timestamp时C1时间戳不为null默认current _ timestamp；

当C1时间戳在建表语句中默认为0时，

相当于MySQL 5.5中的C1时间戳not null默认' 0000-00-00 00:00:00 '；

相当于MySQL 5.6中C1时间戳null默认' 0000-00-00 00:00:00 '；

相当于MySQL 5.7中的C1时间戳not null默认' 0000-00-00 00:00:00 '；

MySQL 5.6和MySQL 5.7的主要区别受参数explicit _ defaults _ for _ timestamp的默认值影响。

PS2:当时间戳列的默认值为“0000-00-00 00:00:00”时，使用此默认值“超出时间戳值的范围”不会生成警告。

时间戳类型引发的异常

当MySQL参数time_zone=system时，查询时间戳字段会调用系统时区进行时区转换。但由于系统时区的全局锁问题，在发生多个并发大数据访问时，会设置频繁的线程上下文切换、CPU利用率飙升、系统响应缓慢、假死等问题。

时间戳类型和时间类型选择

在一些“数据库指南”文档中，建议使用时间戳类型而不是日期时间字段。原因是时间戳类型使用4个字节，而datetime字段使用8个字节。但是，随着磁盘性能的提升和内存成本的降低，在实际生产环境中，时间戳类型的使用并不会带来太大的性能提升，反而可能由于时间戳类型的定义和取值范围而限制和影响业务使用。

在MySQL 5 . 6 . 4及以后版本中，对于时间戳数据可以获得最高精度微秒，对于datetime数据也可以获得最高精度微秒，对于datetime数据也可以获得同样的效果，比如将字段定义为dt1 datetime(3)not null default now(3)on update now(3)；datetime的访问范围是' 1000-01-01 00: 00: 00.00000 '到' 9999-12-31 23: 59: 59.99999 '，可以更好的存储各个时间段的数据。

时间戳类型使用建议

当只关注数据的最后一次更新时间时，建议将时间戳列定义为timestamp not null默认current _ timestamp on update current _ timestamp；

考虑到创建时间和更新时间，建议将更新时间设置为时间戳字段，将创建时间定义为DAETIME或时间戳默认' 0000-00-00 00: 00: 00 '，并在插入记录时显式指定创建时间；

建议在表中只定义一个时间戳列，并显式定义DEFAULT和ON UPDATE属性；

虽然在MySQL中可以分配或更新时间戳字段，但建议只在必要时显式插入和更新时间戳列；

建议将time_zone参数设置为系统外的值，比如中国的服务器为‘8:00’；

建议MySQL的离线测试版本与在线生产版本保持一致。

Timestamp和datetime的异同

相同点：

1.可以自动更新初始化，默认显示格式相同YYYY-MM-dd HH:mm:ss。

差异：

2.时间戳的时间范围是：' 1970-01-01 00: 00: 01' UTC到' 2038-01-19 03: 14: 07' UTC，自动时区转换，实际存储毫秒，4字节存储。

3.datetime的时间范围：“1000-01-01 00: 00: 00”到“9999-12-31 23: 59: 59”，不支持时区，存储8个字节。

设置timestamp和date的自动更新时间

当更新一条数据或插入一条新数据而没有给date和mydate赋值时，date和mydate字段将自动默认为当前时间。

2038个问题

当时间戳存储的时间长于' 2038-01-19 03:14:07' UTC时，mysql会报错，因为这是mysql自身的问题，也就是说时间戳有上限。如果超过了，自然会报错。具体原因见官方文件：https://dev.mysql.com/doc/refman/8.0/en/datetime.html。部分截图如下：

解决方案

虽然时间戳有上限，但是保存了时间戳，不用考虑时区。如果需要处理时区相关的需求，解决2038的限制，建议将时间戳改为整数类型保存时间戳，然后在程序中进行转换。(这个方案还没有实施，只是个建议。慎用！)

如果不需要考虑时区，只需要将时间戳替换为datatime类型即可，因为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默认值' 0000-00-00 00:00:00 '；

将原始字段列的数据复制到新字段列；

更新“学生”设置“入学日期”=“临时入学日期”;

删除原列；

ALTER TABLE ` student ` DROP ` temp _ entry _ date `;

Sql完成如下：(注意原始时间戳的默认值，这个也需要添加)

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默认值' 0000-00-00 00:00:00 '；

更新“学生”设置“入学日期”=“临时入学日期”;

ALTER TABLE ` student ` DROP ` temp _ entry _ date `;

mysql之TIMESTAMP（时间戳）用法

一、TIMESTAMP的变体

时间戳时间戳在创建时可以有多种不同的特征，例如：

1.创建新记录和修改现有记录时刷新此数据列：

时间戳更新当前时间戳时的默认当前时间戳

2.创建新记录时将此字段设置为当前时间，但以后修改时不要刷新它：

时间戳默认当前时间戳

3.创建新记录时将此字段设置为0，并在以后修改时刷新它：

更新时的时间戳当前时间戳

4.创建新记录时将此字段设置为给定值，并在以后修改时刷新它：

更新CURRENT_TIMESTAMP时的时间戳默认值“yyyy-mm-dd hh:mm:ss”

MySQL目前不支持列的Default 为函数的形式，如达到你某列的默认值为当前更新日期与时间的功能，你可以使用TIMESTAMP列类型，以下详细说明了时间戳列类型。

二、TIMESTAMP列类型

时间戳值可以是从1970年到2037年的某个时间，精度为一秒，其值显示为数字。

时间戳值显示大小的格式如下表所示：

：

- -

|列类型|显示格式|

|时间戳(14) | YYYYMMDDHHMMSS |

|时间戳(12) | YYMMDDHHMMSS |

|时间戳(10) | YYMMDDHHMM |

|时间戳(8) | YYYYMMDD |

|时间戳(6) | YYMMDD |

|时间戳(4) | YYMM |

|时间戳(2) | YY |

- -

“完整”时间戳格式是14位，但是时间戳列也可以使用较短的显示大小。最常见的显示尺寸是6、8、12和14。

创建表时，可以指定任意的显示大小，但是如果将列长度定义为0或大于14，则列长度将被强制定义为14。

列范围从1到13的奇数值被强制到下一个更大的偶数。

例如：

定义字段长度强制字段长度。

时间戳(0) -时间戳(14)

时间戳(15)-时间戳(14)

时间戳(1) -时间戳(2)

时间戳(5) -时间戳(6)

的所有时间戳列具有相同的存储大小，并且指定周期时间值的全精度(14位)用于存储合法值，而与显示大小无关。非法日期将被强制存储为0。

有几个含义：

1.虽然在构建表时定义了TIMESTAMP(8)列，但是在插入和更新数据时，TIMESTAMP列实际上保存了14位数据(包括年、月、日、分、秒)，但是在查询时，MySQL会返回8位年、月、日数据给你。如果使用ALTER TABLE来加宽窄的时间戳列，将会显示以前“隐藏”的信息。

2.类似地，收缩时间戳列也不会导致信息丢失，只是在显示前一个值时显示的信息较少。

3.虽然时间戳值存储为完全精度，但是唯一直接操作存储值的函数是UNIX _ TIMESTAMP()；由于MySQL返回的时间戳列的列值是格式化的检索值，这意味着您可能无法使用某些函数来操作时间戳列(如HOUR()或SECOND())，除非时间戳值的相关部分包含在格式化的值中。

例如，只有当时间戳列被定义为TIMESTAMP(10)或更大时，才会显示时间戳列的HH部分，因此对较短的时间戳值使用HOUR()会产生不可预知的结果。

4.非法时间戳值被转换为适当类型的“零”值(0000000000000)。(日期时间，日期相同)

例如，您可以使用以下语句来验证：

创建表测试(' id ' INT(3)UNSIGNED AUTO _ INCREMENT，' date1 '

时间戳(8)主键(' id ')；

插入id=1的测试集；

SELECT * FROM test

- -

| id |日期1 |

- -

| 1 | 20021114 |

- -

更改表测试更改“日期1”“日期1”时间戳(14)；

SELECT * FROM test

- -

| id |日期1 |

- -

| 1 | 20021114093723 |

- -

您可以使用TIMESTAMP列类型用当前日期和时间自动标记插入或更新操作。

如果您有多个时间戳列，那么只有第一个列会被自动更新。在下列任何情况下，都会自动更新第一个时间戳列：

1.在INSERT或LOAD DATA INFILE语句中没有显式指定列值。

2.列值没有在UPDATE语句中显式指定，其他列会更改值。(注意，UPDATE将一列设置为其现有值，这不会导致TIMESTAMP列被更新，因为如果您将一列设置为其当前值，MySQL会忽略这一更改以提高效率。)

3.您显式地将时间戳列设置为NULL。

4.第一列以外的时间戳列也可以设置为当前日期和时间，只要该列设置为NULL或NOW()。

创建表测试(

id' INT (3)无符号AUTO_INCREMENT，

日期1 '时间戳(14)，

日期2 '时间戳(14)，

主键(“id”)

);

插入到测试(id，date1，date2)值(1，NULL，NULL)；

插入id=2的测试集；

- - -

| id |日期1 |日期2 |

- - -

| 1 | 20021114093723 | 20021114093723 |

| 2 | 20021114093724 | 00000000000000 |

- - -

在第一条指令中，因为date1和date2设置为NULL，所以date1和date2的值是当前时间。在第二条指令中，第一个时间戳列date1更新为当前时间，而两个时间戳列date2由于日期不合法而更改为“00000000000000”。

更新测试集id=3，其中id=1；

- - -

| id |日期1 |日期2 |

- - -

| 3 | 20021114094009 | 20021114093723 |

| 2 | 20021114093724 | 00000000000000 |

- - -

该指令没有显式设置列值date2，因此第一个时间戳列date1将被更新为当前时间。

更新测试集id=1，date1=date1，date2=NOW()，其中id=3。

- - -

| id |日期1 |日期2 |

- - -

| 1 | 20021114094009 | 20021114094320 |

| 2 | 20021114093724 | 00000000000000 |

- - -

在此指令中，因为设置了date1=date1，所以在更新数据时，date1的列值不会更改，因为设置了date2=NOW()，所以在更新数据时，date2的列值将更新为当前时间。该指令相当于：

更新测试集id=1，date1=date1，date2=NULL，其中id=3。

由于MySQL返回的时间戳列显示为数字，所以可以使用DATE_FROMAT()函数格式化时间戳列，如下所示：

选择id，DATE_FORMAT(日期1，' %Y-%m-%d %H:%i:%s ')作为日期1，

DATE_FORMAT(date2，' %Y-%m-%d %H:%i:%s ')作为测试中的DATE 2；

- - -

| id |日期1 |日期2 |

- - -

| 1 | 2002-11-14 09:40:09 | 2002-11-14 09:43:20 |

| 2 | 2002-11-14 09:37:24 | 0000-00-00 00:00:00 |

- - -

SELECT id，DATE_FORMAT(日期1，' %Y-%m-%d ')作为日期1，

DATE_FORMAT(date2，' %Y-%m-%d ')作为测试中的DATE 2；

- - -

| id |日期1 |日期2 |

- - -

| 1 | 2002-11-14 | 2002-11-14 |

| 2 | 2002-11-14 | 0000-00-00 |

- - -

在某种程度上，您可以将一种日期类型的值分配给另一种日期类型的对象。

但是，特别需要注意的是，可能会有一些值的变化或信息的丢失：

1.如果将日期值分配给DATETIME或TIMESTAMP对象，结果值的时间部分将设置为“00:00:00 ”,因为日期值不包含时间信息。

2.如果将日期时间或时间戳值赋给DATE对象，结果值的时间部分将被删除，因为日期类型不存储时间信息。

3.尽管DATETIME、DATE和TIMESTAMP值都可以由相同的格式集指定，但所有类型并不具有相同的值范围。

比如TIMESTAMP值不能比1970早，也不能比2037晚，这意味着，一个日期例如'1968-01-01'，当作为一个DATETIME或DATE值时它是合法的，但它不是一个正确TIMESTAMP值！并且如果将这样的一个对象赋值给TIMESTAMP列，它将被变换为0。

三、当指定日期值时，当心某些缺陷：

1.允许作为字符串指定值的宽松格式能被欺骗。例如，由于使用了“:”分隔符，值“10:11:12”可能看起来像一个时间值，但如果在日期中使用，则上下文会将“2010-11-12”解释为年份。值“10:45:15”将被转换为“0000-00-00”，因为“45”不是合法的月份。

2.以2位数字指定的年值是模糊的，因为世纪是未知的。MySQL使用以下规则来解释两位数的年值：

00-69范围内的年值被转换为2000-2069。70-99范围内的年度值被更改为1970-1999。

PS：这里再为大家推荐一个本站Unix时间戳转换工具，附带了各种语言下时间戳的操作方法，包括PHP、MySQL、SQL Server、java等的时间戳获取与转换操作技巧：

Unix时间戳(timestamp)转换工具：http://tools.jb51.net/code/unixtime

郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。

标签：

相关文章阅读

  • 使用php连接mysql数据库,php连接数据库的方法
  • 使用php连接mysql数据库,php连接数据库的方法,一文详解PHP连接MySQL数据库的三种方式
  • pymysql菜鸟教程,pymysql 使用
  • pymysql菜鸟教程,pymysql 使用,pymysql模块使用简介与示例
  • mysql锁实现,mysql锁算法
  • mysql锁实现,mysql锁算法,MySQL锁机制与用法分析
  • mysql连接报错10061,mysql连接错误10060
  • mysql连接报错10061,mysql连接错误10060,MYSQL无法连接 提示10055错误的解决方法
  • mysql连接报10060错误,mysql连接报错10055
  • mysql连接报10060错误,mysql连接报错10055,MySQL连接异常报10061错误问题解决
  • mysql辅助索引和主键索引,mysql 主键 外键 索引
  • mysql辅助索引和主键索引,mysql 主键 外键 索引,MySQL索引之主键索引
  • MySQL语句大全,mysql常见语句总结
  • MySQL语句大全,mysql常见语句总结,MySQL语句整理及汇总介绍
  • mysql触发器的使用方法实验报告,mysql中触发器的使用
    • 留言与评论（共有 条评论）
       
    验证码：