mysql 5.1のtimestamp型におけるDEFAULT値の不思議?

基本は timestamp型ではDEFAULT CURRENT_TIMESTAMP

mysql5.1でtimestamp型を使用する場合、DEFAULTにはCURRENT_TIMESTAMPが設定されるようです。

CREATE TABLE time_test_1 (
   id         int primary key AUTO_INCREMENT,
   time_1     timestamp  );

このことは、「show create table」でも確認できますし、「select * from hoge」でも確認できます。

> show create table time_test_1;
+-------------+-----------------------------------------------------+
| Table       | Create Table                                        |
+-------------+-----------------------------------------------------+
| time_test_1 | CREATE TABLE `time_test_0` (                        |
|             |   `id`     int(11)   NOT NULL AUTO_INCREMENT,       |
|             |   `time_1` timestamp NOT NULL                       |
|             |                        DEFAULT CURRENT_TIMESTAMP    |
|             |                        ON UPDATE CURRENT_TIMESTAMP, |
|             |    PRIMARY KEY (`id`))                              |
|             |    ENGINE=InnoDB DEFAULT CHARSET=utf8               | 
+-------------+-----------------------------------------------------+

> insert into time_test_1 values();
> select * from time_test_1;
+----+---------------------+
| id | time_1              |
+----+---------------------+
|  1 | 2009-04-03 18:53:33 | 
+----+---------------------+

timestamp型が複数だと最初のカラムのみDEFAULT CURRENT_TIMESTAMP

http://dev.mysql.com/doc/refman/5.1/ja/timestamp-4-1.html

mysqlのドキュメントにも記載されていますが、timestamp型が複数ある場合、最初のカラムのみDEFAULT CURRENT_TIMESTAMPとなり、2番目以降のtimestamp型は、DEFAULT '0000-00-00 00:00:00'となるので、ご存じない型は気に止めておくといいかもしれません。

CREATE TABLE time_test_2 (
 id         int primary key AUTO_INCREMENT,
 time_1     timestamp,
 time_2     timestamp
);

mysql> show create table time_test_2;
+-------------+------------------------------------------------------+
| Table       | Create Table                                         |
+-------------+------------------------------------------------------+
| time_test_2 | CREATE TABLE `time_test` (                           |
|             |    `id` int(11) NOT NULL AUTO_INCREMENT,             |
|             |    `time_1` timestamp NOT NULL                       |
|             |                       DEFAULT CURRENT_TIMESTAMP      |
|             |                         ON UPDATE CURRENT_TIMESTAMP, |
|             |    `time_2` timestamp NOT NULL                       |
|             |                       DEFAULT '0000-00-00 00:00:00', |
|             |     PRIMARY KEY (`id`))                              |
|             | ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8  | 
+-------------+------------------------------------------------------+

> insert into time_test_2 values();
> select * from time_test_2;
+----+---------------------+---------------------+
| id | time_1              | time_2              |
+----+---------------------+---------------------+
|  1 | 2009-04-03 18:37:24 | 0000-00-00 00:00:00 | 
+----+---------------------+---------------------+