資料類型datetime和timestamp的差別

mysql> desc user;

+-------+-------------+------+-----+-------------------+-----------------------------+

| Field | Type        | Null | Key | Default           | Extra                       |

| id    | int(11)     | NO   |     | 0                 |                             |

| name  | varchar(15) | YES  |     | NULL              |                             |

| time1 | datetime    | NO   |     | NULL              |                             |

| time2 | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

4 rows in set (0.00 sec)

mysql> select * from user;

+----+------+---------------------+---------------------+

| id | name | time1               | time2               |

|  1 | a    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

|  2 | b    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

|  3 | c    | 2012-04-28 06:23:45 | 2012-04-28 14:04:29 |

| 10 | g    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |

此時，更新一條記錄，

mysql> update user set name='cc' where id=3;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

|  3 | cc   | 2012-04-28 06:23:45 | 2012-04-28 14:13:48 |

mysql>

timestamp那列字段的時間随着更新，時間自動更改為目前時間。

本文轉自 liang3391 51CTO部落格，原文連結:http://blog.51cto.com/liang3391/846622