时间戳，这样用就对了

待我称王封你为后i 2023-06-29 13:59 21阅读 0赞

**前言：**

时间戳字段在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'。(当然，这个与SQL*MODE有关，如果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 sql*mode没有被指定，那么会向该列中插入'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*time、update*time时间戳字段，并设置好默认值。
2.  对于时间戳字段，插入数据时建议不指定或插入now()函数。
3.  explicit*defaults*for\_timestamp参数建议默认为OFF。
4.  若采用中国时间，建议time\_zone设置为' 8:00'。
5.  不同环境数据库时区建议保持一致，不要随意更改数据库时区。
6.  不同实例间迁移数据要注意时区是否相同。
7.  程序jdbc连接串建议指定时区并与数据库保持一致。

**总结：**

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

![公众号.jpg][.jpg]

[.jpg]: https://img-blog.csdnimg.cn/20200113101313178.jpeg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdrdW5fag==,size_16,color_FFFFFF,t_70