テーブルに対してINSERT処理をすると同時にINSERTされた時間が
データが作成された時間列(created )とデータが更新された時間列(updated)
に同時に格納されるには以下のようにTIMESTAMP列に対して
”DEFAULT 0”と” on update CURRENT_TIMESTAMP”をDEFAULT値として
設定しておく。


CREATE DATABASE `STUDY` /*!40100 DEFAULT CHARACTER SET utf8 */

CREATE TABLE TIMESTAMP_FUNC (
created TIMESTAMP DEFAULT 0,
updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
data CHAR(30)
);

mysql> CREATE TABLE TIMESTAMP_FUNC (
-> created TIMESTAMP DEFAULT 0,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc TIMESTAMP_FUNC;
+---------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+---------------------+-----------------------------+
| created | timestamp | NO | | 0000-00-00 00:00:00 | |
| updated | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
| data | char(30) | YES | | NULL | |
+---------+-----------+------+-----+---------------------+-----------------------------+
3 rows in set (0.00 sec)

mysql> insert into TIMESTAMP_FUNC(created,updated,data) values(NULL,NULL,'This is timestamp test');
Query OK, 1 row affected (0.00 sec)

mysql> select * from TIMESTAMP_FUNC;
+---------------------+---------------------+------------------------+
| created | updated | data |
+---------------------+---------------------+------------------------+
| 2009-07-11 10:12:10 | 2009-07-11 10:12:10 | This is timestamp test |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

mysql> update TIMESTAMP_FUNC set data ='This is timestmp update test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from TIMESTAMP_FUNC;
+---------------------+---------------------+------------------------------+
| created | updated | data |
+---------------------+---------------------+------------------------------+
| 2009-07-11 10:12:10 | 2009-07-11 10:13:51 | This is timestmp update test |
+---------------------+---------------------+------------------------------+
1 row in set (0.00 sec)

mysql>

以下のようにデータが更新された列のupdate列はデータ更新日になっている。

確認①
time_stamp
確認②
time_stamp_2


Web Applicationを作成していてデータが入力された時間を
保存しておきたいという事が多々あります。
MS SQLでは時間列のDefault値として(getdate())を設定しますが
MY SQLではCURRENT_TIMESTAMPを利用します。

(例)


mysql> CREATE TABLE `BBS` (
-> `comment` varchar(100) NULL DEFAULT NULL,
-> `comment_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> desc BBS;
+--------------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-------+
| comment | varchar(100) | YES | | NULL | |
| comment_time | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------------+--------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into BBS(comment) values('Hello World!');
Query OK, 1 row affected (0.00 sec)

mysql> select * from BBS;
+--------------+---------------------+
| comment | comment_time |
+--------------+---------------------+
| Hello World! | 2009-07-09 12:26:23 |
+--------------+---------------------+
1 row in set (0.01 sec)

mysql>

bbs