写点什么

时间戳,这样用就对了

用户头像
Simon
关注
发布于: 2020 年 08 月 21 日
时间戳,这样用就对了

前言: 



时间戳字段在MySQL中经常使用到,比如需要记录一行数据创建的时间或修改的时间时,我们通常会使用时间戳即timestamp字段。本篇文章主要介绍timestamp字段的使用方法及相关参数,希望大家读完能对timestamp有更深的认识。



1.TIMESTAMP字段类型简介



timestamp字段类型可存储时间类型数据,timestamp所能存储的时间范围为:'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999',timestamp字段占用4个字节。在MySQL5.7版本,timestamp可以指定精度,即TIMESTAMP(fsp)中fsp可以指定一个介于0到6之间的可选值,以代表小数秒精度。值为0表示没有小数部分,如果省略,则默认精度为0。



下面我们来简单测试下:



# timestamp可以指定精度
mysql> CREATE TABLE `stu_tb` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
-> `stu_id` int(11) NOT NULL COMMENT '学号',
-> `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
-> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
-> `update_time` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2) COMMENT '修改时间',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql> show create table stu_tb\G
*************************** 1. row ***************************
Table: stu_tb
Create Table: CREATE TABLE `stu_tb` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`stu_id` int(11) NOT NULL COMMENT '学号',
`stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2) COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into stu_tb (stu_id,stu_name) values (1001,'dfdfa');
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu_tb;
+----+--------+----------+---------------------+------------------------+
| id | stu_id | stu_name | create_time | update_time |
+----+--------+----------+---------------------+------------------------+
| 1 | 1001 | dfdfa | 2020-01-07 17:33:51 | 2020-01-07 17:33:51.63 |
+----+--------+----------+---------------------+------------------------+
1 row in set (0.00 sec)



2.影响时间戳的参数



影响时间戳显示的参数主要有两个,分别是explicit_defaults_for_timestamp,time_zone。下面简单介绍下这两个参数对时间戳的影响。



explicit_defaults_for_timestamp参数决定MySQL服务端对timestamp列中的默认值和NULL值的不同处理方法。此变量自MySQL 5.6.6 版本引入,分为全局级别和会话级别,可动态更新,默认值为OFF。



当explicit_defaults_for_timestamp参数默认设置为OFF时,其行为如下:



  • 在默认情况下,如果timestamp列没有显式的指明null属性,那么该列会被自动加上not null属性(而其他类型的列如果没有被显式的指定not null,那么是允许null值的),如果往这个列中插入null值,会自动的设置该列的值为current timestamp值。

  • 表中的第一个timestamp列,如果没有指定null属性或者没有指定默认值,也没有指定ON UPDATE语句。那么该列会自动被加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性。

  • 对于其它TIMESTAMP列,如果没有显示指定NULL和DEFAULT属性的话,会自动设置为NOT NULL DEFAULT '0000-00-00 00:00:00'。(当然,这个与SQLMODE有关,如果SQL_MODE中包含'NO_ZERO_DATE',实际上是不允许将其默认值设置为'0000-00-00 00:00:00'的。)



当explicit_defaults_for_timestamp参数设置为ON时,其行为如下:



  • 如果timestamp列没有显式的指定not null属性,那么默认的该列可以为null,此时向该列中插入null值时,会直接记录null,而不是current timestamp。

  • 不会自动的为表中的第一个timestamp列加上DEFAULT CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP属性。

  • 如果timestamp列被加上了not null属性,并且没有指定默认值。这时如果向表中插入记录,但是没有给该TIMESTAMP列指定值的时候,如果strict sql_mode被指定了,那么会直接报错。如果strict sqlmode没有被指定,那么会向该列中插入'0000-00-00 00:00:00'并且产生一个warning。



time_zone参数指定了数据库时区,time_zone分为全局级别和会话级别,可动态更新,默认为SYSTEM,表示数据库服务时区与系统时区相同。MySQL存储timestamp时间戳时,存入数据库的实际是UTC的时间,查询显示时会根据具体的时区来显示不同的时间。可以在配置文件中写入default_time_zone指定时区,比如指定中国时间东八区可以这样写:default_time_zone='+8:00'。下面简单演示下不同时区下时间的显示:



# 初始默认值
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
mysql> select * from testdb.stu_tb;
+----+--------+----------+---------------------+------------------------+
| id | stu_id | stu_name | create_time | update_time |
+----+--------+----------+---------------------+------------------------+
| 1 | 1001 | dfdfa | 2020-01-07 17:33:51 | 2020-01-07 17:33:51.63 |
+----+--------+----------+---------------------+------------------------+
1 row in set (0.00 sec)
# 改为UTC时区 并重新连接 发现timestamp存储的时间有变化
mysql> set global time_zone='+0:00';
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | +00:00 |
+------------------+--------+
2 rows in set (0.01 sec)
mysql> select * from testdb.stu_tb;
+----+--------+----------+---------------------+------------------------+
| id | stu_id | stu_name | create_time | update_time |
+----+--------+----------+---------------------+------------------------+
| 1 | 1001 | dfdfa | 2020-01-07 09:33:51 | 2020-01-07 09:33:51.63 |
+----+--------+----------+---------------------+------------------------+
1 row in set (0.00 sec)



3.TIMESTAMP使用最佳实践



TIMESTAMP在使用是有很多变体,列举常用的几个如下:



  • TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

在创建新记录和修改现有记录的时候都对这个数据列刷新

  • TIMESTAMP DEFAULT CURRENT_TIMESTAMP

在创建新记录的时候把这个字段设置为当前时间,但以后修改时,不再刷新它

  • TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

在创建新记录的时候把这个字段设置为0,以后修改时刷新它

  • TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss’ ON UPDATE CURRENT_TIMESTAMP

在创建新记录的时候把这个字段设置为给定值,以后修改时刷新它



实际情况下,这里建议这样创建表:



CREATE TABLE `table_name` (
`increment_id` INT UNSIGNED NOT NULL auto_increment COMMENT '自增主键',
...
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



即每一行数据都包含创建时间和修改时间,程序写入数据并不需要显示指定创建时间和修改时间,由默认值自动填充。



关于时间戳,下面总结下几点经验建议,希望对你有所帮助:



  1. 数据表具有create_timeupdate_time 时间戳字段,并设置好默认值。

  2. 对于时间戳字段,插入数据时建议不指定或插入now()函数。

  3. explicit_defaults_for_timestamp 参数建议默认为OFF。

  4. 若采用中国时间,建议time_zone设置为'+8:00'。

  5. 不同环境数据库时区建议保持一致,不要随意更改数据库时区。

  6. 不同实例间迁移数据要注意时区是否相同。

  7. 程序jdbc连接串建议指定时区并与数据库保持一致。



总结: 



本篇文章详细介绍了timestamp的用法,对于影响时间戳的几个参数,我们也简单做了介绍。timestamp时间戳使用实践及相关建议希望对你有所帮助,其实这些内容在官方文档中都有记录,大家有事没事可以多翻阅官方文档哦。



发布于: 2020 年 08 月 21 日阅读数: 58
用户头像

Simon

关注

MySQL技术学习者 2018.06.08 加入

公众号『MySQL技术』作者

评论

发布
暂无评论
时间戳,这样用就对了