自动更新修改时间:
mysql> create table z(a int ,b timestamp on update current_timestamp); mysql> insert into z select 1,current_timestamp;
mysql> select * from z;+------+---------------------+| a | b |+------+---------------------+| 1 | -06-26 07:18:45 |+------+---------------------+1 row in set (0.00 sec)
mysql> insert into z select 2,current_timestamp; Query OK, 1 row affected (0.18 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from z;+------+---------------------+| a | b |+------+---------------------+| 1 | -06-26 07:18:45 || 2 | -06-26 07:19:05 |+------+---------------------+2 rows in set (0.00 sec)
mysql> update z set a=a+100 where a=1;Query OK, 1 row affected (0.20 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from z;+------+---------------------+| a | b |+------+---------------------+| 101 | -06-26 07:19:48 || 2 | -06-26 07:19:05 |+------+---------------------+2 rows in set (0.00 sec)
记录首次插入时间:
mysql> create table x (a int, b timestamp default current_timestamp); Query OK, 0 rows affected (0.22 sec)mysql> select * from x;Empty set (0.00 sec)mysql> insert into x(a) values(1); Query OK, 1 row affected (0.18 sec)mysql> select * from x;+------+---------------------+| a | b |+------+---------------------+| 1 | -06-26 07:14:04 |+------+---------------------+1 row in set (0.00 sec)mysql> insert into x(a) values(2);Query OK, 1 row affected (0.19 sec)mysql> select * from x;+------+---------------------+| a | b |+------+---------------------+| 1 | -06-26 07:14:04 || 2 | -06-26 07:14:35 |+------+---------------------+2 rows in set (0.00 sec)