Mysql5.5中实现createTime和updateTime自动更新

最新推荐文章于 2023-02-22 09:23:53 发布

weixin_34321977

最新推荐文章于 2023-02-22 09:23:53 发布

阅读量1.1k

点赞数

文章标签：数据库 python java

原文链接：https://my.oschina.net/u/565871/blog/601327

版权

2019独角兽企业重金招聘Python工程师标准>>>

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

转载于:https://my.oschina.net/u/565871/blog/601327