there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

最新推荐文章于 2022-08-21 19:55:19 发布

weixin_33953384

最新推荐文章于 2022-08-21 19:55:19 发布

阅读量74

点赞数

本文详细介绍了MySQL中如何使用时间戳字段来记录数据创建和更新的时间。通过具体示例展示了如何创建包含时间戳字段的表，并演示了插入和更新操作时时间戳的变化情况。

建表语句:

create table test_table(
id integer not null auto_increment primary key,
stamp_created timestamp default '0000-00-00 00:00:00',
stamp_updated timestamp default now() on update now()
);

测试:

 mysql> insert into test_table(stamp_created, stamp_updated) values(null, null); 
Query OK, 1 row affected (0.06 sec)

mysql> select * from t5; 
+----+---------------------+---------------------+ 
| id | stamp_created | stamp_updated |
+----+---------------------+---------------------+
| 2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> update test_table set id = 3 where id = 2; 
Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test_table;
+----+---------------------+---------------------+
| id | stamp_created | stamp_updated | 
+----+---------------------+---------------------+ 
| 3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 | 
+----+---------------------+---------------------+ 
2 rows in set (0.00 sec)