深入淺出timestamp

蘭春發表於2016-08-19

前提

對於timestamp相關的錯誤，之前也有耳聞，但是並沒有詳細去了解，導致昨天有位同事描述的錯誤場景不能及時回答，這說明自己對Mysql的理解還是知之甚少。故，這裡詳細談談timestamp

錯誤場景

好了，這裡直奔主題吧。昨天有位同事遇到的錯誤ERROR 1293 (HY000):

* Mysql version 5.1.54

create table lc_test_0(
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT `cms時間` ,
    `upload_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT `上傳時間`
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=`已推送客戶列表`;

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

通過以上錯誤，查查手冊就知道，timestamp型別不允許有兩個CURRENT_TIMESTAMP作為default值。

既然如此，那我們就去掉一個唄

create table lc_test_3(
    `upload_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT `上傳時間`,
    `update_time` timestamp NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=`已推送客戶列表`;

Query OK, 0 rows affected (0.00 sec)

so easy，這不就解決了嘛。

可是問題真是這樣嘛？有些同學比較嚴謹和認真，將兩個欄位的順序對調一下，則

create table lc_test_2(
    `update_time` timestamp NOT NULL ,
    `upload_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT `上傳時間`
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=`已推送客戶列表`;

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

你看看，問題又來了，這樣徹底暈了，同樣的建表語句，只是欄位順序變掉了，就錯了？

莫非這是Mysql 的 BUG？ 去Mysql Buglist中去查檢視，結果沒有類似bug。

目前能想到的就是仔細去看看官方文件對於CURRENT_TIMESTAMP的描述

* Mysql 5.1 *

One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

看的仔細的同學就會發現，current timestamp as the default value for initializing the column，意思就是Mysql 會初始化第一個TIMESTAMP欄位的default值為‘current timestamp’。一個表裡面多個TIMESTAMP column 只能擁有一個‘current timestamp’值。

so，這下豁然開朗,既然如此，那麼就開始測試以上理論吧。

  • 既然第一個timestamp欄位的預設default為‘current timestamp’,那我顯示更改預設值總可以吧
create table lc_test_1(
    `update_time` timestamp NOT NULL default `0000-00-00`,
    `upload_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT `上傳時間`
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=`已推送客戶列表`;
Query OK, 0 rows affected  (0.10 sec)
  • 第一個timestamp欄位的預設default為‘current timestamp’，第二個欄位總不會了吧
root:test> create table lc_test_4(
      `update_time` timestamp NOT NULL ,
      `upload_time` timestamp NOT NULL
     )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=`已推送客戶列表`;
Query OK, 0 rows affected (0.00 sec)

root:test> desc lc_test_4;
+-------------+-----------+------+-----+---------------------+-----------------------------+
| Field       | Type      | Null | Key | Default             | Extra                       |
+-------------+-----------+------+-----+---------------------+-----------------------------+
| update_time | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| upload_time | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------------+-----------+------+-----+---------------------+-----------------------------+
2 rows in set (0.01 sec)

ok，到這裡，我想這個問題應該徹底明白了吧。

那我們又回過頭來思考一下，為什麼只能擁有一個CURRENT_TIMESTAMP default值呢？說實話，我還真沒想明白。但是，我知道Mysql 5.5 高版本和Mysql5.6 以及更高版本以及去掉了這個限制

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

測試：

* Mysql 5.6 *

dbadmin:test> create table lc_test_1(
     `update_time` timestamp NOT NULL,
     `upload_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT `上傳時間`
     )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=`已推送客戶列表`;
Query OK, 0 rows affected (0.01 sec)

dbadmin:test> create table lc_test_2(
        `upload_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT `上傳時間`,
     `update_time` timestamp NOT NULL
    )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=`已推送客戶列表`;
Query OK, 0 rows affected (0.02 sec)

結論

  • 以上問題，均源於對Mysql 官方文件的不細緻學習造成。
  • Mysql官方文件目前雖然講解的原理不是很深，很細，但是確實值得我們仔細閱讀。


相關文章