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

Bertha 。 2024-03-30 14:38 73阅读 0赞

**将远端数据库的数据传输到本地时候出现了这样的错误**  
**\[Err\] \[Dtf\] 1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT\_TIMESTAMP in DEFAULT or ON UPDATE clause**

![在这里插入图片描述][29141ff8dbb14abdb3046ea27389b418.png]  
发生错误的表：

create table 'order_master' (    
    'order_id' varchar(32) not null,    
    'buyer_name' varchar(32) not null comment '买家名字',    
    'buyer_phone' varchar(32) not null comment '买家电话',    
    'buyer_address' varchar(128) not null comment '买家地址',    
    'buyer_openid' varchar(64) not null comment '买家微信openid',    
    'order_amount' decimal(8,2) not null comment '订单总金额',    
    'order_status' tinyint(3) not null default '0' comment '订单状态，默认0新订单'    
    'pay_status' tinyint(3) not null default '0' comment '订单状态，默认0未支付'    
    'create_time' timestamp not null default current_timestamp comment '创建时间',    
    'update_time' timestamp not null default current_timestamp on update current_timestamp comment '修改时间',    
    primary key ('order_id'),    
    key 'idx_buyer_openid' ('buyer_openid') 
    ) comment '订单表';

查了相关资料才知道 MySql 5.5和MySql 5.6之后版本的区别：5.5 只能有一个Timestamp,将其中一列类型改为datetime类型就可以解决。  
远端mysql版本为5.7.17  
![在这里插入图片描述][487de5e693614fe28db58f7dcdfed3d6.png]  
解决方案：

1、备份远程数据转存为sql文件，将其中一列类型Timestamp改为datetime，比如上述错误表改为：

create table 'order_master' (    
    'order_id' varchar(32) not null,    
    'buyer_name' varchar(32) not null comment '买家名字',    
    'buyer_phone' varchar(32) not null comment '买家电话',    
    'buyer_address' varchar(128) not null comment '买家地址',    
    'buyer_openid' varchar(64) not null comment '买家微信openid',    
    'order_amount' decimal(8,2) not null comment '订单总金额',    
    'order_status' tinyint(3) not null default '0' comment '订单状态，默认0新订单'    
    'pay_status' tinyint(3) not null default '0' comment '订单状态，默认0未支付'    
    'create_time' datetime not null comment '创建时间',    
    'update_time' timestamp not null default current_timestamp on update current_timestamp comment '修改时间',    
    primary key ('order_id'),    
    key 'idx_buyer_openid' ('buyer_openid') 
    ) comment '订单表';

2、本地数据库加载修改后的sql文件。  
将远端数据库的数据传输到本地时候出现了这样的错误

[29141ff8dbb14abdb3046ea27389b418.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/db99836fa8094ce7be22bed81be101ee.png
[487de5e693614fe28db58f7dcdfed3d6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/2e46beec0859443ea2bbf8b83574aae1.png

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

发表评论取消回复

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

相关阅读

相关 [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

相关 Synchronizing Record Definitions With Tables

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