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导出文件还原