阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL时间类型Timestamp和Datetime 的深入理解

188次阅读
没有评论

共计 11048 个字符，预计需要花费 28 分钟才能阅读完成。

MySQL 数据库常用的时间类型有 timestamp 和 datetime，两者主要区别是占用存储空间长度不一致、可存储的时间也有限制，但针对不同版本下，timestamp 字段类型的设置需要慎重，因为不注意的可能会被“坑死”。

一、TIMESTAMP 和 DATETIME 字段类型对比

字段类型 存储长度 时间范围 备注
timestamp 4 字节 ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC  
datetime 8 字节（5.7 占 5 字节） ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’  

 

 

 

1.timestamp 注意事项

(1)5.7 版本之前，没有explicit_defaults_for_timestamp 参数(5.7 默认开启，timestamp 不会设置 default 值属性)，timestamp 字段默认存在 default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP 属性，默认值针对的主要是以下函数产生的时间

These are CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().

(2)mysql 的 timestamp 值自动从当前时区转换到 utc 时区存储，并且自动从 utc 时区转换为当前系统时区检索返回

官方参考文档：https://dev.mysql.com/doc/refman/5.7/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. 
(This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.

2.datetime 注意事项：

在 mysql5.7 之后，datetime 字段也可以指定默认值，并且格式和 timestamp 一样

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
或
DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'

二、数据库时区参数

1.system_time_zone

数据库实例启动，从 my.cnf 配置参数 timezone=timezone_name 获取，若 my.cnf 未设置则从操作系统获取环境变量 TZ 获取
2.time_zone

数据库当前实际使用的时区，默认为 system，即系统时区，其设置可以通过 set global time_zone=” 设置，其中参数值有以下三种形式：’SYSTEM’、’+10:00′、’Europe/Helsinki’

Linux 时区知识扩展：Linux 将时钟分为系统时钟 (System Clock) 和硬件 (Real Time Clock，简称 RTC) 时钟两种。系统时间是指当前 Linux Kernel 中的时钟，
而硬件时钟则是主板上由电池供电的那个主板硬件时钟，这个时钟可以在 BIOS 的 "Standard BIOS Feture" 项中进行设置。系统时钟：所有操作系统命令、函数依赖的时钟，独立于硬件时钟运行，可通过 date 设置
    设置系统时间：date -s "2017-08-22 22:58:00" 
    查看时区：date -R

硬件时钟：Linux 启动从系统配置获取
    查看硬件时钟:clock --show、hwclock --show
    设置硬件时钟：hwclock/clock --set --date="月 / 日 / 年  时：分：秒"
    系统时钟同步到硬件：/sbin/clock -w
    硬件时钟同步到系统：/sbin/clock -s

时区设置：修改/etc/sysconfig/clock  
    ZONE=Asia/Shanghai
    rm /etc/localtime
    链接到上海时区文件   
    ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
    执行完上述过程后，重启机器，即可看到时区已经更改。

三、实际使用实例

1. 不同版本下 timestamp 和 datetime 使用实例

（1）测试 timestamp 和 datetime 创建带默认值的表
同时执行创建带默认值的 SQL 语句，发现 5.6（不含 5.6）之前版本 datetime 不支持带 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

CREATE TABLE test (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

5.0.67 版本
ERROR 1067 (42000): Invalid default value for 'dt'

5.5.20 版本
ERROR 1067 (42000): Invalid default value for 'dt'
 
5.6.22 版本
Query OK, 0 rows affected (0.02 sec)

5.7.18 版本
Query OK, 0 rows affected (0.00 sec)

（2）测试 timestamp 和 datetime 创建表结构类型

不指定默认值的情况下创建表结构，发现 5.6（不含 5.6）timestamp 默认值是CURRENT_TIMESTAMP 并且随着记录更新而更新

CREATE TABLE test01 (
  ts TIMESTAMP  ,
  dt DATETIME 
);

5.0.67 版本
 CREATE TABLE `test01` (`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `dt` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

5.5.20 版本
 CREATE TABLE `test01` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
 
5.6.22 版本
CREATE TABLE `test01` (
  `ts` timestamp NULL DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

5.7.18 版本
CREATE TABLE `test01` (
  `ts` timestamp NULL DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

（3）、测试 timestamp 和 datetime 类型 mysqldump 导出导入影响

测试结论为，timestamp 数据类型的记录导出会以 utc 时间格式导出，导入库中自动由 UTC 格式转为系统默认时区，所以看到导出文件 timestamp 内容时间戳和实际存储的不相符。

如果需要看到和导入与实际相符的时间戳，需要加入参数 –tz-utc=false 用于禁止 timestamp 时区转换，默认是开启的，即导出文件中开头设置的 /*!40103 SET TIME_ZONE=’+00:00′ */;

系统默认是 cst 时区，数据库参数设置也是 CST 和 SYSTEM，根据系统时间插入数据：

| system_time_zone | CST    |
| time_zone            | SYSTEM |

insert into test01 values(sysdate(),sysdate());

5.0.67 版本
select * from test01;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-12 14:15:19 | 2018-01-12 14:15:19 | 
+---------------------+---------------------+
 
5.5.20 版本
select * from test01;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-12 14:15:27 | 2018-01-12 14:15:27 |
+---------------------+---------------------+
 
5.6.22 版本
select * from test01;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-07 20:13:22 | 2018-01-07 20:13:22 |
+---------------------+---------------------+
 
5.7.18 版本
select * from test01;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-07 20:13:20 | 2018-01-07 20:13:20 |
+---------------------+---------------------+

然后将数据库表 test01 表利用 mysqldump 导出

mysqldump dbtest --tables test01  >test01.sql
5.0.67 版本
-- MySQL dump 10.11
--
-- Host: localhost    Database: dbtest
-- ------------------------------------------------------
-- Server version       5.0.67-percona-highperf-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `test01`
--
DROP TABLE IF EXISTS `test01`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `test01` (`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `dt` datetime default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
--
-- Dumping data for table `test01`
--
LOCK TABLES `test01` WRITE;
/*!40000 ALTER TABLE `test01` DISABLE KEYS */;
INSERT INTO `test01` VALUES ('2018-01-12 06:15:19','2018-01-12 14:15:19');
/*!40000 ALTER TABLE `test01` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2018-01-12  6:22:25 
5.5.20 版本
-- MySQL dump 10.13  Distrib 5.5.20, for Linux (x86_64)
--
-- Host: localhost    Database: dbtest
-- ------------------------------------------------------
-- Server version       5.5.20-rel24.1-log

/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `test01`
--
DROP TABLE IF EXISTS `test01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test01` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test01`
--
LOCK TABLES `test01` WRITE;
/*!40000 ALTER TABLE `test01` DISABLE KEYS */;
INSERT INTO `test01` VALUES ('2018-01-12 06:15:27','2018-01-12 14:15:27');
/*!40000 ALTER TABLE `test01` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2018-01-12 14:22:32
5.6.22 版本
-- MySQL dump 10.13  Distrib 5.6.22-71.0, for Linux (x86_64)
--
-- Host: localhost    Database: dbtest
-- ------------------------------------------------------
-- Server version       5.6.22-71.0-log
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `test01`
--
DROP TABLE IF EXISTS `test01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test01` (
  `ts` timestamp NULL DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test01`
--
LOCK TABLES `test01` WRITE;
/*!40000 ALTER TABLE `test01` DISABLE KEYS */;
INSERT INTO `test01` VALUES ('2018-01-07 12:13:22','2018-01-07 20:13:22');
/*!40000 ALTER TABLE `test01` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2018-01-07 20:20:29
5.7.18 版本
-- MySQL dump 10.13  Distrib 5.7.18-15, for Linux (x86_64)
--
-- Host: localhost    Database: dbtest
-- ------------------------------------------------------
-- Server version       5.7.18-15-log

/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--
-- Table structure for table `test01`
--
DROP TABLE IF EXISTS `test01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test01` (
  `ts` timestamp NULL DEFAULT NULL,
  `dt` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test01`
--
LOCK TABLES `test01` WRITE;
/*!40000 ALTER TABLE `test01` DISABLE KEYS */;
INSERT INTO `test01` VALUES ('2018-01-07 12:13:20','2018-01-07 20:13:20');
/*!40000 ALTER TABLE `test01` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2018-01-07 20:20:29

删除 test01 表，并用 mysqldump 导出文件还原

mysql -D dbtest -e "drop tables test01"
mysql -D dbtest  <test01.sql 
select * from test01;

5.0.67 版本
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-12 14:15:19 | 2018-01-12 14:15:19 | 
+---------------------+---------------------+
5.5.20 版本
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-12 14:15:27 | 2018-01-12 14:15:27 |
+---------------------+---------------------+
5.6.22 版本
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-07 20:13:22 | 2018-01-07 20:13:22 |
+---------------------+---------------------+
5.7.18 版本
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2018-01-07 20:13:20 | 2018-01-07 20:13:20 |
+---------------------+---------------------+

（4）ON UPDATE CURRENT_TIMESTAMP 属性影响

如果包含 ON UPDATE CURRENT_TIMESTAMP 属性，则如果对表记录更新，此记录对应的 timestamp 类型记录也会更新

ts 字段是 timestamp 类型且含有 on update current_timestamp 属性，dt 字段是 datetime 类型且不含 on update current_timestamp 属性
更新前：mysql -D dbtest -e "select * from test01;"
+---------------------+---------------------+------+
| ts                  | dt                  | id   |
+---------------------+---------------------+------+
| 2018-01-12 14:15:19 | 2018-01-12 14:15:19 | NULL | 
+---------------------+---------------------+------+
更新：mysql -D dbtest -e "update test01 set id=1 where id is null"

更新后：mysql -D dbtest -e "select * from test01;" Logging to file '/home/mysql/query.log'
+---------------------+---------------------+------+
| ts                  | dt                  | id   |
+---------------------+---------------------+------+
| 2018-01-12 15:42:15 | 2018-01-12 14:15:19 |    1 | 
+---------------------+---------------------+------+

2. 不同版本下 mysqldump 结束时间分析

使用 mysqldump 备份的同仁都可能会注意到，正常备份结束的话，在文件结尾会有一条成功结束的表示，即：– Dump completed on 2018-01-12  6:22:25。

这个在 mysql5.0 后可以通过一些参数控制，例如可以加参数 –comments=false 来禁止添加 comments 信息，即所有 – 开头的 comment 会不记录在备份文件中，也可以增加 –dump-date=false 来禁止时间戳添加，即只包含 – Dump completed。

但特别需要注意的是，此时间戳的由来在 mysqldump 10.11 版本（mysql5.0.x 版本对应 MySQL dump 10.11，含 10.11 版本）记录的是 UTC 时区时间戳，而在 mysqldump 10.12 版本之后（mysql 5.5 对应的版本是 MySQL dump 10.13，含 10.13）记录的是系统当前时区时间戳。

所以大家看到的是 mysqldump 10.11 备份结束的时间总是比系统当前时间提前 8 小时，例如 mysql5.0 系统当前是 CST 时区：2018-01-12  14:22:25  而备份文件结束时间戳是 – Dump completed on 2018-01-12  6:22:25。这个只是记录的时间戳不同，不影响最终的数据记录时间。

源码片段如下：

./client/mysqldump.c

#define DUMP_VERSION "10.13"

static void write_footer(FILE *sql_file)
{if (opt_xml)
  {fputs("</mysqldump>\n", sql_file);
    check_io(sql_file);
  }
  else if (!opt_compact)
  {if (opt_tz_utc)
      fprintf(sql_file,"/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;\n");

    fprintf(sql_file,"\n/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\n");
    if (!path)
    {fprintf(md_result_file,"\
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\n\
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\n");
    }
    if (opt_set_charset)
      fprintf(sql_file,
"/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;\n"
"/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;\n"
"/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;\n");
    fprintf(sql_file,
            "/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\n");
    fputs("\n", sql_file);

    if (opt_dump_date)
    {char time_str[20];
      get_date(time_str, GETDATE_DATE_TIME, 0);
      print_comment(sql_file, 0, "-- Dump completed on %s\n", time_str);
    }
    else
      print_comment(sql_file, 0, "-- Dump completed\n");

    check_io(sql_file);
  }
} /* write_footer */

本文永久更新链接地址：http://www.linuxidc.com/Linux/2018-01/150266.htm

正文完
星哥玩云-微信公众号
post-qrcode
MongoDB MySQL MySQL Datetime MySQL Timestamp MySQL时间类型Timestamp和Datetime 的深入理解 NoSQL Oracle Oracle教程 Oracle数据库 PostgreSQL 数据库
发表至： 数据库技术
2022-01-22
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-22发表，共计11048字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
如何在Linux中安装Apache CouchDB 2.3.0
ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./test/’, errno: 17)
Oracle 10gR2在RHEL 5下开启异步IO
MySQL5.7.18字符集配置详解
MySQL5.7 设置远程访问
Oracle 物化视图快速刷新对性能的影响
利用Oracle RUEI+EM12c进行应用的“端到端”性能诊断
Oracle的条件in中包含NULL时的处理
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3594584
评论数
4
文章搜索
热门文章
选择PHP与Python，可以考虑这三个问题

选择PHP与Python，可以考虑这三个问题

导读 尽管 RESTful API 和跨平台库已经使网页开发容易起来，但依然没有解决阻碍并隔分开发者、管理者和...
Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

Centos 7平滑无缝升级PHP7.1.0到PHP 7.1.5

实验环境：CentOS Linux release 7.3.1611 (Core) 内核版本：Linux ve...
介绍ansible的Ad-hoc与commands模块

介绍ansible的Ad-hoc与commands模块

Ad-Hoc 是指 ansible 下临时执行的一条命令，并且不需要保存的命令，对于复杂的命令后面会说 pla...
Linux安装使用pidstat命令以对进程数据进行监控

Linux安装使用pidstat命令以对进程数据进行监控

导读 Linux 系统监控的命令其实有很多，pidstat 命令就是其中的一种，使用 pidstat 命令可以...
linux下使用tree命令以树形结构显示文件目录结构

linux下使用tree命令以树形结构显示文件目录结构

导读 今天小编来给分享 Linux 系统下一个非常有用的命令的使用：tree 命令可以以树形结构显示文件目录结...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
细述使用 Python 获取 Linux 系统信息

细述使用 Python 获取 Linux 系统信息

在本文中，我们将会探索使用 Python 编程语言工具来检索 Linux 系统各种信息。 哪个 Python ...
详解：Linux Chrony 设置服务器集群同步时间

详解：Linux Chrony 设置服务器集群同步时间

导读 Chrony 是一个开源的自由软件，像 CentOS 7 或基于 RHEL 7 操作系统，已经是默认服务...
介绍14款专业黑客都喜欢用的操作系统

介绍14款专业黑客都喜欢用的操作系统

导读 专业的黑客（Hacker）都在什么操作环境下工作？这是一个很多人都感兴趣的问题。今天，就来梳理一下，那些...
在Linux系统下制作U盘启动盘

在Linux系统下制作U盘启动盘

导读 我的当前的系统是 Linux(版本 Xubuntu)，之前是通过软通牒 (UltraISO) 在 Win...
选择PHP与Python，可以考虑这三个问题

选择PHP与Python，可以考虑这三个问题

导读 尽管 RESTful API 和跨平台库已经使网页开发容易起来，但依然没有解决阻碍并隔分开发者、管理者和...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中