テーブルに対して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

Comments are closed.

Post Navigation