MySQLMySQL案例之提姆(Tim)estamp和Datetime

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 CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP属性),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使用实例

MySQL,(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 */

get_date()函数是骨干,那么其源码./mysys/mf_getdate.c如下:

MySQL 1

 

网站地图xml地图