mysql資料匯入遇到的timestamp類型問題

標籤：sam   解決方案   sql檔案   isa   模式   null   class   ges   匯入   

今天準備把最新的表匯入自己以前的機子上做臨時開發，在資料庫匯入的時候遇到一個問題：Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

更具匯入的日誌提示是：表的錯誤定義；在一個表中只能有一個列指定為TIMESTAMP類型的列，並且使用CURRENT_TIMESTAMP 作為預設值或者是使用 ON UPDATE 子句。以前真沒有遇到過這個問題。。。是我操作資料庫少啊。。

搜尋一下大部分說是應為我的資料庫版本是在5.6之後。。我看了下我本地的mysql的版本。原來我用的還是以前的5.5。。應該就是這個原因了。。。只要找到原因就有辦法解決：開啟要操作的sql檔案，可以看到如下：確實在一個表中定義了有兩列定義成了TIMESTAMP類型。

轉載別人的解決方案如下：

註：本文討論的範圍為未啟用MAXDB模式的表！

    今天建了一個表，裡面有一個列是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 = MYISAMCREATE 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的時候指定一下。

轉自：http://blog.sina.com.cn/s/blog_49a665e10100cb52.html

mysql資料匯入遇到的timestamp類型問題