MySQL关于timestamp和mysqldump的一个“bug”【转】
说是MySQL关于timestamp和mysqldump的一个“bug”，其实是MySQLdump 的时候 对timestamp 类型字段的一个转换问题，一直都没注意。看到这篇文章就知道遇到问题如何处理了。
来自：http://dinglin.iteye.com/blog/1747685
复现
来源于一个同事在做数据转储碰到的的问题，简化如下：
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');
