数据类型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 |
+----+------+---------------------+---------------------+
4 rows in set (0.00 sec)

此时，更新一条记录，

mysql> update user set name='cc' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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 | cc   | 2012-04-28 06:23:45 | 2012-04-28 14:13:48 |
| 10 | g    | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

timestamp那列字段的时间随着更新，时间自动更改为当前时间。

本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/846622