mysql - 仅使用一次时出现 "only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause"错误

Question

我正在使用以下 sql 创建一个数据库表:

CREATE  TABLE `cs3_ds1` (
`ID` INT NOT NULL ,
`TIME` TIMESTAMP NOT NULL ,
`USER` VARCHAR(45) NOT NULL ,
`TIME1` TIMESTAMP NOT NULL ,
`TIME2` TIMESTAMP NOT NULL ,
`INSERT_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
 PRIMARY KEY (`ID`) )
 ENGINE = InnoDB

虽然我只在一列中使用当前时间戳作为默认时间戳，但我收到以下错误:

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

为什么会出现这个错误？

Answer 1

MySQL 使用 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 自动初始化 TIMESTAMP 列，因此您的第一列 TIME 默认添加了 CURRENT_TIMESTAMP。因此，当您明确地将 DEFAULT 添加到列时，一个列已经存在。您需要更改列的定义顺序:

CREATE  TABLE `silas`.`cs3_ds1` (
`ID` INT NOT NULL ,
`INSERT_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
`TIME` TIMESTAMP NOT NULL ,
`USER` VARCHAR(45) NOT NULL ,
`TIME1` TIMESTAMP NOT NULL ,
`TIME2` TIMESTAMP NOT NULL ,
 PRIMARY KEY (`ID`) )
 ENGINE = InnoDB

或者将默认值添加到您的其他时间戳列:

CREATE  TABLE `silas`.`cs3_ds1` (
`ID` INT NOT NULL ,
`TIME` TIMESTAMP NOT NULL DEFAULT 0,
`USER` VARCHAR(45) NOT NULL  DEFAULT 0,
`TIME1` TIMESTAMP NOT NULL DEFAULT 0 ,
`TIME2` TIMESTAMP NOT NULL DEFAULT 0 ,
`INSERT_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
 PRIMARY KEY (`ID`) )
 ENGINE = InnoDB

参见 MySQL Docs了解更多信息。