Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT

ゞ浴缸里的玫瑰 2023-05-28 14:23 50阅读 0赞

# 在CENTOS7安装的Mariadb，执行如下sql：报错 #

CREATE TABLE `info` (
       `Id` varchar(30) NOT NULL COMMENT '商户ID',
       `Name` varchar(30) NOT NULL COMMENT '名称',
       `Type` varchar(24) NOT NULL COMMENT '类型',
       `ReqKey` varchar(128) NOT NULL COMMENT '请求私钥',
       `ResKey` varchar(128) NOT NULL COMMENT '响应私钥',
       `State` tinyint(6) NOT NULL DEFAULT '1' COMMENT '商户状态,0-停止使用,1-使用中',
       `CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
       `UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
       PRIMARY KEY (`Id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商户信息表';

表中出现多个timestamp并设置为current\_timestamp的时候报错

Incorrect table definition; there can be only oneTIMESTAMP column with CURRENT\_TIMESTAMP in DEFAULT or ON UPDATEclause

原因是当你给一个timestamp设置为on updatecurrent\_timestamp的时候，其他的timestamp字段需要显式设定default值

但是如果你有两个timestamp字段，但是只把第一个设定为current\_timestamp而第二个没有设定默认值，[MySQL][]能成功建表,但是反过来就不行...

修改后：成功执行

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

CREATE TABLE `info` (
       `Id` varchar (30) NOT NULL COMMENT '商户ID',
       `Name` varchar (30) NOT NULL COMMENT '名称',
       `Type` varchar (24) NOT NULL COMMENT '类型',
       `ReqKey` varchar (128) NOT NULL COMMENT '请求私钥',
       `ResKey` varchar (128) NOT NULL COMMENT '响应私钥',
       `State` tinyint (6) NOT NULL DEFAULT '1' COMMENT '商户状态,0-停止使用,1-使用中',
       `CreateTime` datetime NOT NULL COMMENT '创建时间',
       `UpdateTime` timestamp not null default CURRENT_TIMESTAMP COMMENT '更新时间', 
       PRIMARY KEY (`Id`) 
     ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '商户信息表';

参考网络资料

[MySQL]: http://lib.csdn.net/base/mysql
[aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]: /images/20230528/54315cd5def84b3b810f889718bbfbe8.png

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 [Err] [Dtf] 1293 - Incorrect table definition； there can be only one TIMESTAMP column with CURRENT_T

相关 Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT

相关 Incorrect table definition； there can be only one auto column and it must be defined as a key 解决办法

相关 Mysql 1293 - Incorrect table definition; there can be only one TIMESTAMP column with C

相关 [Err] 1293 - there can be only one TIMESTAMP column with CURRENT_TIME in DEFAULT or ON UPDATE clause

相关 1075-Incorrect table definition；there can be only one auto column and it must be defined as a key

相关 1293 - Incorrect table definition; there can be only oneTIMESTAMP column with CURRENT_TIMESTAMP

相关 1075 - Incorrect table definition;there can be only one auto column and it must be defined as a key

相关 Array constants can only be used in initializers

相关 mysql导入sql脚本出现there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE