MYSQL5.5中timestamp默认值问题

最新推荐文章于 2022-04-30 18:52:41 发布

梦飞梦

最新推荐文章于 2022-04-30 18:52:41 发布

阅读量690

点赞数 1

本文链接：https://blog.csdn.net/weixin_43903105/article/details/106289309

版权

MySQL 5.5中如果同一表中两个timestamp列的默认值都设置为CURRENT_TIMESTAMP，会有以下的错误提示：

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

有下列的解决方法：
1.建表语句
create table test(
id integer not null auto_increment primary key,
 name varchar(20) not null ,
 created timestamp not null default '0000-00-00 00:00:00',
 updated timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
);

2.插入数据方法(timestamp列为not null时，设置为null会自动更新为当前时间)

mysql> insert into test(name,created) values("litao", null);
mysql> select * from test;
+----+-------+---------------------+---------------------+
| id | name  | created             | updated             |
+----+-------+---------------------+---------------------+
|  1 | litao | 2014-06-15 11:24:27 | 2014-06-15 11:24:27 |
+----+-------+---------------------+---------------------+
1 row in set

3.更新时

mysql> update test set name = 'lt' where id = 1;
mysql> select * from test;
+----+------+---------------------+---------------------+
| id | name | created             | updated             |
+----+------+---------------------+---------------------+
|  1 | lt   | 2014-06-15 11:24:27 | 2014-06-15 11:28:29 |
+----+------+---------------------+---------------------+
1 row in set