MySQL的timestamp類型自動更新

今天建了一個表，裡面有一個列是timestamp類型，我本意是在資料更新時，這個欄位的時間能自動更新。豈知對這個類型的值還不甚瞭解，導致出錯。發現這個欄位只是在這行資料建立的時候有值，在更新的卻無變化。

    尋找資料，發現是我建表的語句有問題：

以下是程式碼片段：

代碼如下	複製代碼
CREATE TABLE `test` (   `t1` timestamp NOT NULL default CURRENT_TIMESTAMP,   `ww` varchar(5) NOT NULL ) ENGINE=MyISAM ;

 

    而實際上，以下兩個建表語句的效果是一樣的：

以下是程式碼片段：

代碼如下	複製代碼
CREATE TABLE `test` ( `t1` TIMESTAMP NOT NULL , `ww` VARCHAR( 5 ) NOT NULL ) ENGINE = MYISAM CREATE TABLE `test` (   `t1` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,   `ww` varchar(5) NOT NULL ) ENGINE=MyISAM ;

 

    比較之下，我的語句少了“on update CURRENT_TIMESTAMP”或多了“default CURRENT_TIMESTAMP”。如此一來，這個timestamp欄位只是在資料insert的時間建立時間，而update時就不會有變化了。當然，如果你就是想達到這個目的倒也無所謂。

    尋找英文手冊（http://dev.mysql.com/doc/refman/5.1/en/timestamp.html），得到更詳細的解說，翻譯如下：

-----------------------------------翻譯開始--------------------------------

    在CREATE TABLE語句中，第1個TIMESTAMP列可以用下面的任何一種方式聲明：

    1： 如果定義時DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句都有，列值為預設使用當前的時間戳記，並且自動更新。

    2： 如果不使用DEFAULT或ON UPDATE子句，那麼它等同於DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

    3： 如果只有DEFAULT CURRENT_TIMESTAMP子句，而沒有ON UPDATE子句，列值預設為目前時間戳但不自動更新。

    4： 如果沒用DEFAULT子句，但有ON UPDATE CURRENT_TIMESTAMP子句，列預設為0並自動更新。

    5： 如果有一個常量值DEFAULT，該列會有一個預設值，而且不會自動初始化為目前時間戳。如果該列還有一個ON UPDATE CURRENT_TIMESTAMP子句，這個時間戳記會自動更新，否則該列有一個預設的常量但不會自動更新。

    換句話說，你可以使用當前的時間戳記去初始化值和自動更新，或者是其中之一，也可以都不是。（比如，你在定義的時候可以指定自動更新，但並不初始化。）下面的欄位定義說明了這些情況：

以下是程式碼片段：

代碼如下	複製代碼
自動初始化和更新: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 只自動初始化: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP 只自動更新 ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP 只是給一個常量（註：0000-00-00 00:00:00） ts TIMESTAMP DEFAULT 0

 

-----------------------------------翻譯結束--------------------------------

    上面我用紅色字型標出了"第1個TIMESTAMP列"，這是因為，一個表有多個timestamp列的話，定義起來是不一樣的。

    比如上面的第二條：“如果不使用DEFAULT或ON UPDATE子句，那麼它等同於DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。”如果是第二個timestamp列就不一樣了，請看：

以下是程式碼片段：

代碼如下	複製代碼
CREATE TABLE `test` ( `ww` VARCHAR( 9 ) NOT NULL , `t1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `t2` TIMESTAMP NOT NULL ) ENGINE = MYISAM

 

    這個情況下，資料插入時，t1會記錄目前時間，t2為預設值（0000-00-00 00:00:00），等同下面的語句：

以下是程式碼片段：

代碼如下	複製代碼
CREATE TABLE  `test` ( `ww` VARCHAR( 9 ) NOT NULL , `t1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `t2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE = MYISAM

 
    而根據上面的第二條，應該是：

以下是程式碼片段：

代碼如下	複製代碼
CREATE TABLE `test` ( `ww` VARCHAR( 9 ) NOT NULL , `t1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `t2` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE = MYISAM

    但實際上，上面的語句是個語法錯誤，mysql會返回：

以下是引用片段：

代碼如下	複製代碼
#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

 
    我曾經想這樣設計一個表，這個表有兩個timestamp列，一個可以記錄更新時間，一個可以記錄初始時間，但是嘗試多次以後，我發現mysql好像做不到這一點，不知道這個是mysql的缺陷還是自我最佳化，因為，這個功能可以使用datetime實現記錄初始化的時間，只是需要insert的時候指定一下。