MySQL数据导出更改时间 [英] MySQL data export changes times

问题描述

我有一些用于数据库的备份和还原脚本.该表具有时间戳字段.备份脚本如下所示:

I have some backup and restore scripts that I am using for my database. The table has a timestamp field. The backup script looks like this:

mysqldump -u user -ppass database --tab="../" --fields-terminated-by="|" --skip-comments table

它将创建两个文件table.sql和table.txt.恢复脚本如下所示:

It creates two files, table.sql and table.txt. The restore script looks like this:

mysql -u user -ppass database < "../table.sql"
mysqlimport -u user -ppass --local --fields-terminated-by="|" database "../table.txt"

但是备份脚本输出的时间错误-比数据库中的时间晚了一个小时-但导入时并不能纠正它.

However the backup script is outputting the wrong time - it's an hour behind what is in the database - but it doesn't correct it when importing.

例如，一行上的时间为 15:10:25 ，但是运行备份脚本时，table.txt中列出了 14:10:25 .当我运行还原脚本时，同一行现在具有 14:10:25 作为数据库中的时间.如果我再次备份，它会显示 13:10:25 ！等等...

For example the time on one row was 15:10:25 but when the backup script is run, 14:10:25 is listed in table.txt. When I run the restore script, the same row now has 14:10:25 as the time in the database. If I backup again, it says 13:10:25! And so on...

我不知道为什么会这样.时区似乎设置为"SYSTEM"(我在格林尼治标准时间). table.sql文件中有几行提到时区，也许那里出了点问题?这是有问题的完整文件:

I can't figure out why this is. The time zone appears to be set to "SYSTEM" (I'm on GMT). The table.sql file has a few lines mentioning time zones, maybe something is wrong there? Here is the full file in question:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP TABLE IF EXISTS `news_article`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `news_article` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `alias` varchar(65) NOT NULL,
  `author` tinyint(3) unsigned NOT NULL,
  `category` tinyint(3) unsigned NOT NULL,
  `posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `opening` text NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=MyISAM AUTO_INCREMENT=93 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

推荐答案

最终找到了解决方案:在导出脚本中添加--skip-tz-utc选项.

Found a solution in the end: adding the --skip-tz-utc option to the export script.

这只是确保您导出的确切日期是第二个数据库中导入的日期.它对我有用，因为数据库位于相同的时区，但是对于数据库不同的时区的其他人可能不是理想的选择.

This simply makes sure the exact date you export is what's imported in the second database. It works for me since the databases are the same time zone, but may not be ideal for others whose databases are different time zones.

这篇关于MySQL数据导出更改时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！