timestamp字段类型的装置必要郑重,timestamp字段类型的设置须要郑重

mysql数据库常用的时光项目有timestamp和datetime,两者根本不同是占用存储空间尺寸不等同、可存储的小运也有限量,但针对不同版本下,timestamp字段类型的设置必要慎重,因为不小心的恐怕会被“坑死”。

mysql数据库常用的时光档次有timestamp和datetime,两者根本差异是占有存储空间尺寸不平等、可存储的大运也有限定,但针对分裂版本下,timestamp字段类型的安装需求郑重,因为不小心的只怕会被“坑死”。

一、TIMESTAMP和DATETIME字段类型对比

一、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’  
字段类型 存储长度 时间范围 备注
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.timestamp注意事项

(1)5.7版本以前,没有explicit_defaults_for_timestamp
参数(5.7暗中认同开启,timestamp不会设置default CU凯雷德RENT_TIMESTAMP on update
CURRENT_TIMESTAMP属性),timestamp字段私自认同存在default CU帕杰罗RENT_TIMESTAMP
on update CURRENT_TIMESTAMP属性,暗中认同值针对的显倘若以下函数爆发的命宫

(1)5.7本子之前,没有explicit_defaults_for_timestamp
参数(5.7暗中认可开启,timestamp不会安装default CU揽胜极光RENT_TIMESTAMP on update
CURRENT_TIMESTAMP属性),timestamp字段私行认同存在default CU凯雷德RENT_TIMESTAMP
on update CURRENT_TIMESTAMP属性,暗中认可值针对的重假诺以下函数发生的日子

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

(2)mysql的timestamp值自动从日前时区转换来utc时区存储,并且自动从utc时区转换为当前系统时区检索重回

(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. 
官方参考文档: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注意事项:

2.datetime注意事项:

在mysql5.7随后,datetime字段也可以指定专擅认同值,并且格式和timestamp一样

在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'
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

1.system_time_zone

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

  数据库实例运营,从my.cnf配置参数timezone=timezone_name获取,若my.cnf未安装则从操作系统获取环境变量TZ获取
2.time_zone

  数据库当前实际应用的时区,暗许为system,即系统时区,其安装可以通过set
global time_zone=”设置,其中参数值有以下二种格局:’SYSTEM’ 、’+10:00′
、’Europe/Helsinki’

  数据库当前事实上选择的时区,私自认同为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
    执行完上述过程后,重启机器,即可看到时区已经更改。                    
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使用实例

(1)测试timestamp和datetime创立带专断认同值的表
并且推行创制带专断认同值的SQL语句,发现5.6(不含5.6)从前版本datetime不辅助带DEFAULT
CUEnclaveRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

(1)测试timestamp和datetime创设带暗中同意值的表
同时履行创制带暗中认可值的SQL语句,发现5.6(不含5.6)此前版本datetime不扶助带DEFAULT
CU君越RENT_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)
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成立表结构类型

(2)测试timestamp和datetime创制表结构类型

不指定暗中认同值的图景下创立表结构,发现5.6(不含5.6)timestamp私行认同值是CULX570RENT_TIMESTAMP并且随着记录更新而创新

不指定暗许值的事态下创制表结构,发现5.6(不含5.6)timestamp暗许值是CU卡宴RENT_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
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导出导入影响

(3)、测试timestamp和datetime类型mysqldump导出导入影响

测试结论为,timestamp数据类型的笔录导出会以utc时间格式导出,导入库中自动由UTC格式转为系统默许时区,所以看到导出文件timestamp内容时间戳和骨子里存储的不符合。

测试结论为,timestamp数据类型的记录导出会以utc时间格式导出,导入库中自行由UTC格式转为系统默许时区,所以见到导出文件timestamp内容时间戳和实际存储的不吻合。

即使需要看到和导入与事实上相符的年华戳,要求进入参数–tz-utc=false用于禁止timestamp时区转换,暗中认同是开启的,即导出文件中开首设置的/*!40103
SET TIME_ZONE=’+00:00′ */;

只要急需看到和导入与实际相符的时日戳,要求插足参数–tz-utc=false用于禁止timestamp时区转换,默许是敞开的,即导出文件中初露设置的/*!40103
SET TIME_ZONE=’+00:00′ */;

系统默许是cst时区,数据库参数设置也是CST和SYSTEM,依照系统时间插入数据:

系统暗中认同是cst时区,数据库参数设置也是CST和SYSTEM,依据系统时间插入数据:

| system_time_zone | CST    |
| time_zone              | 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 |
+---------------------+---------------------+
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导出

接下来将数据库表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
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导出文件还原

剔除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 |
+---------------------+---------------------+
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属性影响

(4)ON UPDATE CURRENT_TIMESTAMP属性影响

即使带有ON UPDATE
CU奥迪Q5RENT_TIMESTAMP属性,则只要对表记录更新,此记录对应的timestamp类型记录也会更新

如果含有ON UPDATE
CU途锐RENT_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 | 
+---------------------+---------------------+------+
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停止时间分析

2.两样版本下mysqldump截止时间分析

动用mysqldump备份的同事都大概会注意到,平常备份截止以来,在文件结尾会有一条成功截止的表示,即
:– Dump completed on 2018-01-12  6:22:25。

行使mysqldump备份的同人都可能会小心到,常常备份截止以来,在文书结尾会有一条成功甘休的意味,即
:– Dump completed on 2018-01-12  6:22:25。

其一在mysql5.0后方可经过某个参数控制,例如可以加参数–comments=false来禁止添加comments音信,即怀有–初始的comment会不记录在备份文件中,也足以增加–dump-date=false来禁止时间戳添加,即只含有–
Dump completed。

以此在mysql5.0后方可透过有个别参数控制,例如能够加参数–comments=false来禁止添加comments消息,即所有–早先的comment会不记录在备份文件中,也足以追加–dump-date=false来禁止时间戳添加,即只蕴涵–
Dump completed。

皇冠现金app,但专门需求专注的是,此时间戳的由来在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版本(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。这些只是记录的小时戳不一致,不影响最终的数量记录时间。

从而我们看看的是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

./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 */
#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如下:

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

皇冠现金app 1

皇冠现金app 2

 

 

相关文章