mysql中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都有類似的字段,所以上面的方法是通用的。

來源：hi.baidu.com/it培訓網

分享到：

2010-03-15 14:51

瀏覽 1552

分類:數據庫

評論

總結

以上是生活随笔為你收集整理的mysql添加timestamp有什么用_mysql中timestamp的使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。