错误信息:
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause:
sql语句:
CREATE TABLE sckill (
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
DEFAULT CHARSET = utf8
COMMENT '秒杀库存表';错误信息说的是timestamp只能有一列为current_timestamp,但语句中确实只有一列,为什么还报错?
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause:
sql语句:
CREATE TABLE sckill (
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
DEFAULT CHARSET = utf8
COMMENT '秒杀库存表';错误信息说的是timestamp只能有一列为current_timestamp,但语句中确实只有一列,为什么还报错?
原因是mysql会默认为表中的第一个timestamp字段(且设置了NOT NULL)隐式设置DEFAULAT CURRENT_TIMESTAMP。所以说上例那样的设置实际上等同于设置了两个CURRENT_TIMESTAMP,因此而报错,调整之后就可以了。