返回 登录
0

MySQL中,关于TIMESTAMP的初始化相关摘录

阅读1439

摘自Automatic Initialization and Updating for TIMESTAMP

  • With both DEFAULT CURRENTTIMESTAMP and ON UPDATE CURRENTTIMESTAMP, the column has the current timestamp for its default value and is automatically updated to the current timestamp.

    CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENTTIMESTAMP
    );

  • With neither DEFAULT CURRENTTIMESTAMP nor ON UPDATE CURRENTTIMESTAMP, it is the same as specifying both DEFAULT CURRENTTIMESTAMP and ON UPDATE CURRENTTIMESTAMP.

    CREATE TABLE t1 (
    ts TIMESTAMP
    );

  • With a DEFAULT clause but no ON UPDATE CURRENT_TIMESTAMP clause, the column has the given default value and is not automatically updated to the current timestamp.

  • The default depends on whether the DEFAULT clause specifies CURRENTTIMESTAMP or a constant value. With CURRENTTIMESTAMP, the default is the current timestamp.

    CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

  • With a constant, the default is the given value. In this case, the column has no automatic properties at all.

    CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT 0
    );

  • With an ON UPDATE CURRENT_TIMESTAMP clause and a constant DEFAULT clause, the column is automatically updated to the current timestamp and has the given constant default value.

    CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
    );

  • With an ON UPDATE CURRENT_TIMESTAMP clause but no DEFAULT clause, the column is automatically updated to the current timestamp. The default is 0 unless the column is defined with the NULL attribute, in which case the default is NULL.

    CREATE TABLE t1 (
    ts TIMESTAMP ON UPDATE CURRENTTIMESTAMP -- default 0
    );
    CREATE TABLE t2 (
    ts TIMESTAMP NULL ON UPDATE CURRENT
    TIMESTAMP -- default NULL
    );

评论