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