mysql中timestamp的使用

今天调整历史数据时，发现一个问题，当修改操作日志表中的用户表字段时，操作时间自动修改为了当前时间。

原因是创建表时，当表字段类型为timestamp，将自动加上属性default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP，这个表示当修改某条记录时，则将此字段修改为当前时间。

如果想设置为每次修改其他字段的值时不自动修改此字段为当前时间的话，可以显示设置属性为default CURRENT_TIMESTAMP。

以下是关于timestamp的具体用法：

mysql> CREATE TABLE t1 (
    ->   id mediumint(9) NOT NULL auto_increment,
    ->   name char(11) default NULL,
    ->   rq timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (id)
    -> ) ;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from t1;

+----+------+---------------------+
| id | name | rq                  |
+----+------+---------------------+
| 1 | a    | 2008-03-22 10:36:30 |
| 2 | b    | 2008-03-22 10:36:33 |
+----+------+---------------------+
2 rows in set (0.00 sec)
mysql> update t1 set name='f'where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+----+------+---------------------+
| id | name | rq                  |
+----+------+---------------------+
| 1 | a    | 2008-03-22 10:36:30 |
| 2 | f     | 2008-03-22 10:37:04 |
+----+------+---------------------+
2 rows in set (0.00 sec)
注意：id=2的字段的日期字段值由“2008-03-22 10:36:33”自动变为了“”2008-03-22 10:37:04!
★思考：rq字段自动变化有什么好处呢?
★解决：现在要根据两个表找出哪些记录变化了,哪些记录是增加的。
演示步骤：
★将t1表备份为t2表,注意备份表t2与t1表中的rq字段值也是完全相同的：
mysql> create table t2 as select * from t1
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+----+------+---------------------+
| id | name | rq                  |
+----+------+---------------------+
| 1 | a    | 2008-03-22 10:36:30 |
| 2 | f     | 2008-03-22 10:37:04 |
+----+------+---------------------+
2 rows in set (0.00 sec)
★修改id=2的字段：
mysql> update t1 set name='g'where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
★增加新记录：
mysql> insert into t1(name) values('h');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+----+------+---------------------+
| id | name | rq                  |
+----+------+---------------------+
| 1 | a    | 2008-03-22 10:36:30 |
| 2 | g    | 2008-03-22 10:43:38 |
| 3 | h    | 2008-03-22 10:44:46 |
+----+------+---------------------+
3 rows in set (0.00 sec)
★查出新增加的记录(ID不在t2表中的)：
mysql> select a.*
    -> from t1 a
    -> where not exists
    -> (
    -> select b.*
    -> from t2 b
    -> where a.id=b.id
    -> );
+----+------+---------------------+
| id | name | rq                  |
+----+------+---------------------+
| 3 | h    | 2008-03-22 10:44:46 |
+----+------+---------------------+
1 row in set (0.00 sec)
★说明：查询哪些记录是被修改过的(ID相同而RQ不同)
mysql> select a.*
    -> from t1 a
    -> where exists
    -> (
    -> select b.*
    -> from t2 b
    -> where a.id=b.id
    -> and   a.rq!=b.rq
    -> )
    -> ;
+----+------+---------------------+
| id | name | rq                  |
+----+------+---------------------+
| 2 | g    | 2008-03-22 10:43:38 |
+----+------+---------------------+
1 row in set (0.00 sec)
★同理,也可以删除哪些记录是被删除的(id在t2中却不在t1中的)：
mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> select a.*
    -> from t2 a
    -> where not exists
    -> (
    -> select b.*
    -> from t1 b
    -> where a.id=b.id
    -> );
+----+------+---------------------+
| id | name | rq                  |
+----+------+---------------------+
| 2 | f    | 2008-03-22 10:37:04 |
+----+------+---------------------+
1 row in set (0.00 sec)
说明：
1、在数据仓库系统的设计中,经常会将系统1的定期送到系统2中中,但是系统1中的数据有增加的、删除的和修改的。其中,哪些记录修改的,一直是比较难于解决的问题。
2、在Oracle、SQLServer都有类似的字段,所以上面的方法是通用的。