手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL关于timestamp和mysqldump的一个“bug”

[日期:2012-12-16] 来源:Linux社区  作者:dinglin [字体: ]

复现
 
来源于一个同事在做数据转储碰到的的问题,简化如下:
 
1、建表
 
drop table if exists tb;
 
CREATE TABLE tb (
 
  c timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
 
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
 
insert into tb values(now());
 
select * from tb;
 
 
 
返回
 
MySQL> select * from tb;
 
+---------------------+
 
| c                  |
 
+---------------------+
 
| 2012-12-14 00:42:45 |
 
+---------------------+
 
1 row in set (0.00 sec)
 
 
 
2、dump“出错”
 
  mysqldump  -Srun/mysql.sock -uroot  test tb  --where='c="2012-12-14 00:42:45"' | grep INSERT
 
返回为空,也就是说导不到数据。
 
 
 
 
 
分析
 
从上面的结论看上去,似乎是mysqldump的”bug”,看得到的数据都导不出来。 如果我们先不加where条件,
 
mysqldump  -Srun/mysql.sock -uroot  test tb    |grep INSERT                         

INSERT INTO `tb` VALUES ('2012-12-13 16:42:45');
 
 
 
接下来我们要说说关于timestamp这个字段类型。
 
首先,从大小上你可以看出来,它不是个字符串,实际上是一个整型。所以当我们执行 where c=” 2012-12-14 00:42:45”的时候,需要将其转换为整型。这就涉及到转换规则。也就是说,对于相同的时间戳,在不同的时区显示的结果是不一样的。反过来也一样,相同的字符串,在不同的时区解释下,会得到不同的时间戳。
 
我们来看一下整个mysqldump的结果。在文件头部,可以看到
 
/*!40103 SET TIME_ZONE='+00:00' */; 字样,说明mysqldump在默认情况下,是按’+00:00’(中时区).
 
而mysql客户端的默认值呢:
 
mysql> select @@time_zone;
 
+-------------+
 
| @@time_zone |
 
+-------------+
 
| SYSTEM      |
 
+-------------+
 
 
 
这个SYSTEM表示MySQL取操作系统的默认时区,因此是东8区。如果我们设置为与mysqldump相同时区,
 
mysql> set time_zone='+00:00'; 

Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> select * from tb;
 
+---------------------+
 
| t                  |
 
+---------------------+
 
| 2012-12-13 16:42:45 |
 
+---------------------+
 
1 row in set (0.00 sec)
 
就跟我们上面看到的全表导出的结果一样了。

也就是说,这个问题是因为mysqldump强行设置了时区为中时区造成的。
 
 
 
解决1
 
从mysqldump的代码中我们看到,可以用 --tz-utc=0 参数去掉前面的设置时区的动作。这样用的也是默认时区。
 
mysqldump  --tz-utc=0 -Srun/mysql.sock -uroot  test tb  --where='c="2012-12-14 00:42:45"'  |grep INSERT
 
INSERT INTO `tb` VALUES ('2012-12-14 00:42:45');
 
 
 
可以看到,这个貌似就是我们要的结果,导出的结果也很合理。
 
 
 
进一步
 
如果这个这么好,为什么mysqldump的开发者不把—tz-utc=0作为默认行为呢?也就是说哦这样做有什么风险?
 
实际上是因为要防止跨时区导数据。假设你把中国一个机器上的数据导入到美国的一个mysqld(想起@plinux 说的b2b就有这种情况),若不显式地设置一个时区,在导入时就会出错了。因为都用系统默认的时区,相同的字符串值会得到不同的时间戳。如我们前面说的, 时间戳是以整型方式存储的。
 
 
 
解决2
 
所以上面的--tz-utc=0存在风险。当然如果你确定源和目标系统时区没变,是ok的。我们讨论看看有没有更保险的方法。
 
既然是时间戳是保险的,其实可以考虑,用时间戳来做where条件。
 
mysql> select unix_timestamp(c) from tb;
 
+-------------------+
 
| unix_timestamp(c) |
 
+-------------------+
 
|        1355416965 |
 
+-------------------+
 
 
 
按照表里的这个值,我们的dump命令改成
 
mysqldump  -Srun/mysql.sock -uroot  test tb  --where=' unix_timestamp(c)=1355416965' | grep INSERT

INSERT INTO `tb` VALUES ('2012-12-13 16:42:45');
 
这次对了,而且与是否使用 --tz-utc=0  无关,都能得到结果,区别只是显示问题。
 
 
 
不过对MySQL比较熟悉的同学就知道,这个写法还是存在一个问题:用不上索引,因为我们在字段上做了unix_timestamp这个操作。有时候我们在这种表上为了导出方便有一个索引专门建在timestamp字段上。
 
 
 
因此想到用逆函数
 
mysqldump  -Srun/mysql.sock -uroot  test tb  --where='c= from_unixtime(1355416965)'  | grep INSERT         

INSERT INTO `tb` VALUES ('2012-12-13 16:42:45');

linux
相关资讯       mysqldump  MySQL数据库教程  timestamp 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款