timestamp型で自動更新

mysql> create table hoge (id integer auto_increment, flag integer, updated_at timestamp, created_at datetime, primary key(id));
Query OK, 0 rows affected (0.02 sec)

mysql> desc hoge;
+------------+-----------+------+-----+-------------------+-----------------------------+
| Field      | Type      | Null | Key | Default           | Extra                       |
+------------+-----------+------+-----+-------------------+-----------------------------+
| id         | int(11)   | NO   | PRI | NULL              | auto_increment              |
| flag       | int(11)   | YES  |     | NULL              |                             |
| updated_at | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| created_at | datetime  | YES  |     | NULL              |                             |
+------------+-----------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

mysql> insert into hoge value(1,0,now(),now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from hoge;
+----+------+---------------------+---------------------+
| id | flag | updated_at          | created_at          |
+----+------+---------------------+---------------------+
|  1 |    0 | 2011-07-05 16:32:51 | 2011-07-05 16:32:51 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update hoge set flag=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from hoge;
+----+------+---------------------+---------------------+
| id | flag | updated_at          | created_at          |
+----+------+---------------------+---------------------+
|  1 |    1 | 2011-07-05 16:34:47 | 2011-07-05 16:32:51 |
+----+------+---------------------+---------------------+
1 row in set (0.00 sec)

→timestamp型のupdated_atが更新され、datetime型のcreated_atは変わらない

version 5.1.38