復(fù)現(xiàn)
來(lái)源于一個(gè)同事在做數(shù)據(jù)轉(zhuǎn)儲(chǔ)碰到的的問(wèn)題,簡(jiǎn)化如下:
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“出錯(cuò)”
mysqldump -Srun/mysql.sock -uroot test tb --where='c="2012-12-14 00:42:45"' | grep INSERT
返回為空,也就是說(shuō)導(dǎo)不到數(shù)據(jù)。
分析
從上面的結(jié)論看上去,似乎是mysqldump的”bug”,看得到的數(shù)據(jù)都導(dǎo)不出來(lái)。 如果我們先不加where條件,
mysqldump -Srun/mysql.sock -uroot test tb |grep INSERT
INSERT INTO `tb` VALUES ('2012-12-13 16:42:45');
接下來(lái)我們要說(shuō)說(shuō)關(guān)于timestamp這個(gè)字段類(lèi)型。
首先,從大小上你可以看出來(lái),它不是個(gè)字符串,實(shí)際上是一個(gè)整型。所以當(dāng)我們執(zhí)行 where c=” 2012-12-14 00:42:45”的時(shí)候,需要將其轉(zhuǎn)換為整型。這就涉及到轉(zhuǎn)換規(guī)則。也就是說(shuō),對(duì)于相同的時(shí)間戳,在不同的時(shí)區(qū)顯示的結(jié)果是不一樣的。反過(guò)來(lái)也一樣,相同的字符串,在不同的時(shí)區(qū)解釋下,會(huì)得到不同的時(shí)間戳。
我們來(lái)看一下整個(gè)mysqldump的結(jié)果。在文件頭部,可以看到
/*!40103 SET TIME_ZONE='+00:00' */; 字樣,說(shuō)明mysqldump在默認(rèn)情況下,是按’+00:00’(中時(shí)區(qū)).
而mysql客戶(hù)端的默認(rèn)值呢:
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
這個(gè)SYSTEM表示MySQL取操作系統(tǒng)的默認(rèn)時(shí)區(qū),因此是東8區(qū)。如果我們?cè)O(shè)置為與mysqldump相同時(shí)區(qū),
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)
就跟我們上面看到的全表導(dǎo)出的結(jié)果一樣了。
也就是說(shuō),這個(gè)問(wèn)題是因?yàn)?/span>mysqldump強(qiáng)行設(shè)置了時(shí)區(qū)為中時(shí)區(qū)造成的。
解決1
從mysqldump的代碼中我們看到,可以用 --tz-utc=0 參數(shù)去掉前面的設(shè)置時(shí)區(qū)的動(dòng)作。這樣用的也是默認(rèn)時(shí)區(qū)。
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');
可以看到,這個(gè)貌似就是我們要的結(jié)果,導(dǎo)出的結(jié)果也很合理。
進(jìn)一步
如果這個(gè)這么好,為什么mysqldump的開(kāi)發(fā)者不把—tz-utc=0作為默認(rèn)行為呢?也就是說(shuō)哦這樣做有什么風(fēng)險(xiǎn)?
實(shí)際上是因?yàn)橐乐箍鐣r(shí)區(qū)導(dǎo)數(shù)據(jù)。假設(shè)你把中國(guó)一個(gè)機(jī)器上的數(shù)據(jù)導(dǎo)入到美國(guó)的一個(gè)mysqld(想起@plinux 說(shuō)的b2b就有這種情況),若不顯式地設(shè)置一個(gè)時(shí)區(qū),在導(dǎo)入時(shí)就會(huì)出錯(cuò)了。因?yàn)槎加孟到y(tǒng)默認(rèn)的時(shí)區(qū),相同的字符串值會(huì)得到不同的時(shí)間戳。如我們前面說(shuō)的, 時(shí)間戳是以整型方式存儲(chǔ)的。
解決2
所以上面的--tz-utc=0存在風(fēng)險(xiǎn)。當(dāng)然如果你確定源和目標(biāo)系統(tǒng)時(shí)區(qū)沒(méi)變,是ok的。我們討論看看有沒(méi)有更保險(xiǎn)的方法。
既然是時(shí)間戳是保險(xiǎn)的,其實(shí)可以考慮,用時(shí)間戳來(lái)做where條件。
mysql> select unix_timestamp(c) from tb;
+-------------------+
| unix_timestamp(c) |
+-------------------+
| 1355416965 |
+-------------------+
按照表里的這個(gè)值,我們的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');
這次對(duì)了,而且與是否使用 --tz-utc=0 無(wú)關(guān),都能得到結(jié)果,區(qū)別只是顯示問(wèn)題。
不過(guò)對(duì)MySQL比較熟悉的同學(xué)就知道,這個(gè)寫(xiě)法還是存在一個(gè)問(wèn)題:用不上索引,因?yàn)槲覀冊(cè)谧侄紊献隽?/span>unix_timestamp這個(gè)操作。有時(shí)候我們?cè)谶@種表上為了導(dǎo)出方便有一個(gè)索引專(zhuān)門(mén)建在timestamp字段上。
因此想到用逆函數(shù)
mysqldump -Srun/mysql.sock -uroot test tb --where='c= from_unixtime(1355416965)' | grep INSERT
INSERT INTO `tb` VALUES ('2012-12-13 16:42:45');
聯(lián)系客服