MySQL關於timestamp和mysqldump的“bug” 復現 www.2cto.com 來源於一個同事在做數據轉儲碰到的的問題，簡化如下： 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 返回為空，也就是說導不到數據。 www.2cto.com 分析 從上面的結論看上去，似乎是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'); www.2cto.com 可以看到，這個貌似就是我們要的結果，導出的結果也很合理。 進一步 如果這個這麼好，為什麼mysqldump的開發者不把—tz-utc=0作為默認行為呢？也就是說哦這樣做有什麼風險？ 實際上是因為要防止跨時區導數據。假設你把中國一個機器上的數據導入到美國的一個mysqld（想起@plinux 說的b2b就有這種情況），若不顯式地設置一個時區，在導入時就會出錯了。因為都用系統默認的時區，相同的字符串值會得到不同的時間戳。如我們前面說的， 時間戳是以整型方式存儲的。 解決2 所以上面的--tz-utc=0存在風險。當然如果你確定源和目標系統時區沒變，是ok的。我們討論看看有沒有更保險的方法。 既然是時間戳是保險的，其實可以考慮，用時間戳來做where條件。 mysql> select unix_timestamp(c) from tb; +-------------------+ | unix_timestamp(c) | +-------------------+ | 1355416965 | +-------------------+ www.2cto.com 按照表裡的這個值，我們的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');
