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

分手后的思念是犯贱 2022-08-18 12:07 152阅读 0赞

**复现**

来源于一个同事在做数据转储碰到的的问题，简化如下：

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');

发表评论取消回复

表情：

评论列表（有 0 条评论，152人围观）

还没有评论，来说两句吧...

相关阅读

相关分享（2）一个 mysqldump 备份 MySQL 数据库的脚本

编写目的使用 mysqldump 对 MySQL 数据库进行在线备份，并指定轮转次数说明因为个人原因，有点强迫症，喜欢编写能够复用且使用简单的脚本，提供简单的功能

缺乏、安全感/ 2024年03月25日 23:10/ 0 赞/ 50 阅读

相关 MySQL 主从复制的一个BUG

发现一个主从复制的BUG，从库会自动跳过对主库来说不存在的列，看例子。主库有一个sam表，其中id是自增主键： mysql> desc sam; +--

た入场券/ 2023年06月27日 05:39/ 0 赞/ 9 阅读

相关 timestamp mysql修改_mysql 设置和修改timestamp的自动更新

创建自动更新的 timestamp (插入或修改时 uptime都会自动更新) CREATE TABLE \`hello\` ( \`id\` int(11) NOT NU

桃扇骨/ 2023年01月11日 13:14/ 0 赞/ 212 阅读

相关 MySQL datetime和timestamp区别

dataTime和timestamp的相同点：二者都是用来表示时间的 YY-MM-DD HH:MM:SS 不同点：对于timestamp，它会把客户端插入的时间

超、凢脫俗/ 2022年12月25日 14:54/ 0 赞/ 162 阅读

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

复现来源于一个同事在做数据转储碰到的的问题，简化如下： 1、建表 drop table if exists tb; CREATE TABLE tb ( c ti

分手后的思念是犯贱/ 2022年08月18日 12:07/ 0 赞/ 153 阅读

相关 mysql timestamp的两个默认值：CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP

timestamp有两个属性，分别是CURRENT\_TIMESTAMP 和ON UPDATE CURRENT\_TIMESTAMP两种，使用情况分别如下：

ゝ一世哀愁。/ 2022年06月03日 04:51/ 0 赞/ 414 阅读

相关 mysql中的CURRENT TIMESTAMP和ON UPDATE CURRENT TIMESTAMP

分享一下我老师大神的人工智能教程！零基础，通俗易懂！[http://blog.csdn.net/jiangjunshow][http_blog.csdn.net_jiangju

àì夳堔傛蜴生んèń/ 2022年04月16日 05:41/ 0 赞/ 264 阅读

相关 mysql的datetime和timestamp

TIMESTAMP和DATETIME的相同点： 1> 两者都可用来表示YYYY-MM-DD HH:MM:SS\[.fraction\]类型的日期。 TIMESTAMP和DA

朴灿烈づ我的快乐病毒、/ 2022年04月08日 08:10/ 0 赞/ 257 阅读

相关关于MySQL的timestamp类型

如果需要表示年月日时分秒，datetime和timestamp都是可以选择的数据类型。但是， 1.从存储空间上，datetime占8 bytes，timestamp占4by

╰+哭是因爲堅強的太久メ/ 2022年02月16日 12:08/ 0 赞/ 241 阅读

相关 .NET framework 一个关于OleDbParameters的Bug

最近在写一个简单的小应用程序，出现了一个让我头疼大半天的问题，搞到最后才弄清楚，原来是OleDbParameters的一个Bug 具体情况如下：开始我在使

缺乏、安全感/ 2021年12月05日 14:05/ 0 赞/ 196 阅读