奇怪的 mysql timestamp

先看一个示例：

mysql> create table t1 (id int,
t1 timestamp,
t2 timestamp,
t3 timestamp default current_timestamp,
t4 timestamp default current_timestamp on update current_timestamp,
t5 timestamp default 0 );
Query OK, 0 rows affected (0.08 sec)
--t1直接写timestamp
--t2也直接写timestamp
--t3默认值为当前时间戳
--t4默认值为当前时间戳，且带更新
--t5默认为零
mysql> desc t1;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL                |                             |
| t1    | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| t2    | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
| t3    | timestamp | NO   |     | CURRENT_TIMESTAMP   |                             |
| t4    | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| t5    | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+
6 rows in set (0.01 sec)
--查看各个列的处理方式，从上面的信息来看
--"t1 timestamp" 自动做了处理，等同于 "t4 timestamp default current_timestamp on update current_timestamp"；
--"t2 timestamp" MySQL自动做了处理，默认值变成了0，虽然和"t1 timestamp"写法完全相同；
--"t3 timestamp default current_timestamp" 挺正常，定义的什么就显示的什么；

--当前t1表为空
mysql> select * from t1;
Empty set (0.00 sec)

--插入一条数据id=1
mysql> insert into t1(id) values(1);
Query OK, 1 row affected (0.00 sec)

--查看现在的t1表：
mysql> select * from t1\G
*************************** 1. row ***************************
id: 1
t1: 2013-08-31 00:42:11
t2: 0000-00-00 00:00:00
t3: 2013-08-31 00:42:11
t4: 2013-08-31 00:42:11
t5: 0000-00-00 00:00:00
1 row in set (0.00 sec)
--1.因为"t1 timestamp"列被MySQL自动转成了"t1 timestamp default current_timestamp on update current_timestamp"，所以现在的t1列的内容为当前时间戳；
--2.因为"t2 timestamp"列也被MySQL自动转成了"t2 timestamp default 0"；
--3."t3 timestamp default current_timestamp"默认值为现在的时间戳，不带自动更新；
--4."t4 timestamp default current_timestamp on update current_timestamp"，默认值为现在的时间戳，带自动更新；
--5."t5 timestamp default 0"默认为0

--现在更新id=1为id=2：
mysql> update t1 set id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--再来查看下现在的t1表：
mysql> select * from t1\G
*************************** 1. row ***************************
id: 2
t1: 2013-08-31 00:43:44
t2: 0000-00-00 00:00:00
t3: 2013-08-31 00:42:11
t4: 2013-08-31 00:43:44
t5: 0000-00-00 00:00:00
1 row in set (0.00 sec)
对于这个问题，mysql官方文档给出了详细的说明：
In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:
1，With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.
当有DEFAULT CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP 两个表达式定义时，列会将当前的timestamp作为他的缺省值，并自动更新
2，With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
如果没有DEFAULT 且没有 ON UPDATE表达式，效果与DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 相同。
3,With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
如果有DEFAULT CURRENT_TIMESTAMP 表达式，没有ON UPDATE 表达式，则这一列会将当前的timestamp作为缺省值，但不自动更新。
4，With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.
如果没有DEFAULT 表达式，但是有ON UPDATE 表达式，则这一列会有一个0的缺省值，但不自动更新。
5，With a constant DEFAULT value, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.如果有一个DEFAULT常量，则该列会使用这个给定的缺省值，且不会自动更新。如果有ON UPDATE，则会自动更新，否则不自动更新。
In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither. (For example, you can specify ON UPDATE to enable auto-update without also having the column auto-initialized.) The following column definitions demonstrate each of the possiblities:

Auto-initialization and auto-update: （自动初始化，自动更新）ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Auto-initialization only: （仅自动初始化）ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Auto-update only: （仅自动更新）ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

Neither: （既不初始化，也不更新）ts TIMESTAMP DEFAULT 0