MySQL的timestamp類型自動更新

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的時候指定一下。