MySQL时间戳字段在INSERT和UPDATE时都自动更新 2015-08-07 19:05

介绍

一个表的时间戳字段,当对这个表进行INSERT或者UPDATE时,都会自动更新。

用法

1
2
CREATE TABLE `t1` (`p_c` INT(11) NOT NULL, `p_time` 
    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

测试:

[LemonHouse]> CREATE TABLE `t1` (`p_c` INT(11) NOT NULL, `p_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.14 sec)

[LemonHouse]> SELECT * FROM t1;
Empty set (0.00 sec)

[LemonHouse]> INSERT INTO t1(p_c) VALUES(1);
Query OK, 1 row affected (0.01 sec)

[LemonHouse]> INSERT INTO t1(p_c) VALUES(2);
Query OK, 1 row affected (0.02 sec)

[LemonHouse]> INSERT INTO t1(p_c) VALUES(3);
Query OK, 1 row affected (0.01 sec)

[LemonHouse]> SELECT * FROM t1;
+-----+---------------------+
| p_c | p_time              |
+-----+---------------------+
|   1 | 2015-08-07 09:34:06 |
|   2 | 2015-08-07 09:34:26 |
|   3 | 2015-08-07 09:34:32 |
+-----+---------------------+
3 rows in set (0.00 sec)

[LemonHouse]>
[LemonHouse]> UPDATE t1 SET p_c = 10 WHERE p_c=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[LemonHouse]> SELECT * FROM t1;
+-----+---------------------+
| p_c | p_time              |
+-----+---------------------+
|  10 | 2015-08-07 09:34:59 |
|   2 | 2015-08-07 09:34:26 |
|   3 | 2015-08-07 09:34:32 |
+-----+---------------------+
3 rows in set (0.00 sec)

[LemonHouse]>

参考文档

  1. MySQL timestamp自动更新时间分享
Tags: #MySQL    Post on Database