问题场景
我们要从数据库A中导出test表里create_pub_time在2019-10-30的0点到4点的数据到数据库B中的test表里,其中两个库里的test表表结构完全一致,create_pub_time字段类型为timestamp。
首先,导出sql文件。在服务器上执行以下命令:
mysqldump -h主机名 -u用户名 -p密码 -P3306 --default-character=utf8 -t A test --where="create_pub_time>='2019-10-30 00:00:00' and create_pub_time< '2019-10-30 04:00:00' " --replace > '/data/test/test.sql';
得到test.sql文件后,再将文件导入到数据库B中。在服务器上执行以下命令(或者,在Mysql客户端直接运行test.sql文件):
mysql -h主机名 -u用户名 -p密码 -P3306 --default-character=utf8 -t B < '/data/test/test.sql';
我们发现,数据库B的test表里导入的数据并不是create_pub_time在0点到4点的数据,而是8点到12点的数据。
原因
参考连接:https://www.cnblogs.com/mr-totoro/p/5807636.html
在导出时,如果没有加其他选项,在我们的导出文件中有这样几行:
….
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
….
也就是说,在导出时,首先会将时区time_zone设置为’+00:00’,而time_zone这个值在数据库中默认是什么呢
是系统默认时区,查看系统默认时区:
在导出数据时,时间会先做转换,在原来的时间基础上减去8h,然后再根据create_pub_time>=‘2019-10-30 00:00:00’ and create_pub_time< '2019-10-30 04:00:00’条件进行过滤,所以,我们实际得到的是8点到12点的数据。
解决办法
- 方法1
在执行mysqldump命令使用参数 –skip-tz-utc,规避时区的影响,即
mysqldump -h主机名 -u用户名 -p密码 -P3306 --default-character=utf8 -t 数据库名 test --where="time>='2019-10-30 02:00:00' and time< '2019-10-30 04:00:00' " --skip-tz-utc --replace > '/data/test/test.sql';
- 方法2
timestamp字段修改为datetime - 方法3
更改Mysql Server使用UTC时区
先将需要导出的库的时区进行设置
set time_zone='+00:00';
导出结束后再恢复:
set time_zone='SYSTEM';
拓展
1. 查看Mysql时间相关命令
查看Mysql时区
mysql> show variables like '%time_zone%';
Variable_name | Value |
---|---|
system_time_zone | CST |
time_zone | SYSTEM |
- system_time_zone 表示系统使用的时区是CST即北京时间。
- time_zone 表示 MySQL 采用的是系统的时区。也就是说,如果在连接时没有设置时区信息,就会采用这个时区配置。
查看Mysql当前时间
mysql> select now();
提示:如果您在使用 Sql Server 数据库,请使用 getdate() 函数来获得当前的日期时间。
2. 查看系统默认时区时区
date -R
上面命令输出了+0800表示东八区,也就是我们国家的时间。
3. 关于时间的几个标准
- CST:中国标准时间(China Standard Time)。
- JST:日本标准时间(Japan Standard Time)。
- UTC:协调世界时,又称世界标准时间,简称UTC,从英文国际时间/法文协调时间”Universal Time/Temps Cordonné”而来。中国大陆、香港、澳门、台湾、蒙古国、新加坡、马来西亚、菲律宾、澳洲西部的时间与UTC的时差均为+8,也就是UTC+8。
- GMT:格林尼治标准时间(旧译格林威治平均时间或格林威治标准时间;英语:Greenwich Mean Time,GMT)是指位于英国伦敦郊区的皇家格林尼治天文台的标准时间,因为本初子午线被定义在通过那里的经线。
注:我们国家跨越了东五区、东六区、东七区、东八区、东九区五个时区,一般都统一采用东八区计时时间。
东八区(UTC/GMT+08:00)是比世界协调时间(UTC)/格林尼治时间(GMT)快8小时的时区,即北京时间。