mysql建立表示，時間欄位timetamp碰到的問題

標籤：

在mysql中建立表時，在新加的欄位時間上，碰到下面的問題：

建表語句如下：

CREATE TABLE seckill(seckill_id bigint NOT NULL AUTO_INCREMENT COMMENT ‘商品庫存id‘,name varchar (120) NOT NULL COMMENT ‘商品名稱‘,number int NOT NULL COMMENT ‘庫存數量‘,start_time timestamp NOT NULL COMMENT ‘秒殺開啟時間‘,end_time timestamp NOT NULL COMMENT ‘秒殺結束時間‘,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘建立時間‘,PRIMARY KEY (seckill_id),key idx_start_time(start_time),key idx_end_time(end_time),key idx_create_time(create_time))ENGINE=InnoDB AUTO_INCREMENT=1000 COMMENT=‘秒殺庫存表‘;

執行上面的語句，回報錯:

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

我使用的mysql版本是5.5.經過查詢得到原因：

Changes in MySQL 5.6.5 (2012-04-10, Milestone 8)

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.

 

意思是：timestamp沒有加預設的值，建表語句改為：

CREATE TABLE seckill(seckill_id bigint NOT NULL AUTO_INCREMENT COMMENT ‘商品庫存id‘,name varchar (120) NOT NULL COMMENT ‘商品名稱‘,number int NOT NULL COMMENT ‘庫存數量‘,start_time timestamp DEFAULT 0 NOT NULL COMMENT ‘秒殺開啟時間‘,end_time timestamp DEFAULT 0 NOT NULL COMMENT ‘秒殺結束時間‘,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘建立時間‘,PRIMARY KEY (seckill_id),key idx_start_time(start_time),key idx_end_time(end_time),key idx_create_time(create_time))ENGINE=InnoDB AUTO_INCREMENT=1000 COMMENT=‘秒殺庫存表‘;

就可以建立成功了。

 

mysql建立表示，時間欄位timetamp碰到的問題