MySQL關於timestamp和mysqldump的一個“bug”

復現

來源於一個同事在做資料轉儲碰到的的問題，簡化如下：

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