ON UPDATE CURRENT_TIMESTAMP列を作成すると
データを更新した日付が自動的に変更される。


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

mysql> desc BBS2;
+---------------------+--------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------------------+-----------------------------+
| comment | varchar(100) | YES | | NULL | |
| comment_time | timestamp | YES | | 0000-00-00 00:00:00 | |
| comment_update_time | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
+---------------------+--------------+------+-----+---------------------+-----------------------------+
3 rows in set (0.00 sec)

current_timestamp2

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

mysql> select * from BBS2;
+--------------+---------------------+---------------------+
| comment | comment_time | comment_update_time |
+--------------+---------------------+---------------------+
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+--------------+---------------------+---------------------+
1 row in set (0.00 sec)

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

mysql> select * from BBS2;
+--------------+---------------------+---------------------+
| comment | comment_time | comment_update_time |
+--------------+---------------------+---------------------+
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+--------------+---------------------+---------------------+
2 rows in set (0.00 sec)

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

mysql> select * from BBS2;
+--------------+---------------------+---------------------+
| comment | comment_time | comment_update_time |
+--------------+---------------------+---------------------+
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+--------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> update BBS2 set comment = 'Hello World!' where comment ='Hello World';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> show warnings;
Empty set (0.00 sec)

mysql> select * from BBS2;
+--------------+---------------------+---------------------+
| comment | comment_time | comment_update_time |
+--------------+---------------------+---------------------+
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World! | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| Hello World! | 0000-00-00 00:00:00 | 2009-07-09 12:39:05 | <----更新されている。 +--------------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql>

current_timestamp

Comments are closed.

Post Navigation