2015年12月14日月曜日

MySQLは ON UPDATE CURRENT_TIMESTAMPで更新日時を自動的に更新できる

MySQLのタイムスタンプ型では、DEFAULT句に追加でon updateをつけることで更新時にも日時を自動設定できるらしいので試してみた。
試した時に使ったバージョンはMySQL Communityの5.7.10です。


DDLとテーブル定義

テーブル定義を見ると、on updateを指定したカラムはExtra列で確認することができる。
create table test_table (
    id int AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    last_modified TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp,
    PRIMARY KEY (id)
)

mysql> desc test_table;
+---------------+--------------+------+-----+-------------------+-----------------------------+
| Field         | Type         | Null | Key | Default           | Extra                       |
+---------------+--------------+------+-----+-------------------+-----------------------------+
| id            | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name          | varchar(100) | NO   |     | NULL              |                             |
| last_modified | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+--------------+------+-----+-------------------+-----------------------------+

結果

insertやupdate時に対象のカラムを指定しないと、自動的にcurrent_timestampの値が入ることがわかる。
update時には、カラムの値が変更されている場合のみ、対象カラムのtaimustampが変更されている。

なお、insertやupdate時に任意の値を直接設定することもできる。
-- INSERT
mysql>  insert into test_table (name) values ('hoge');
Query OK, 1 row affected (0.07 sec)

-- 自動的にtimestampが設定される。
mysql> select * from test_table;
+----+------+---------------------+
| id | name | last_modified       |
+----+------+---------------------+
|  1 | hoge | 2015-12-14 12:24:09 |
+----+------+---------------------+
1 row in set (0.00 sec)

-- UPDATE
mysql> update test_table set name = 'fuga';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- timestampの値が更新される。
mysql> select * from test_table;
+----+------+---------------------+
| id | name | last_modified       |
+----+------+---------------------+
|  1 | fuga | 2015-12-14 12:24:32 |
+----+------+---------------------+
1 row in set (0.03 sec)

-- 値が変更されないUPDATE
mysql> update test_table set name = 'fuga';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

-- timestampは更新されない
mysql> select * from test_table;
+----+------+---------------------+
| id | name | last_modified       |
+----+------+---------------------+
|  1 | fuga | 2015-12-14 12:24:32 |
+----+------+---------------------+
1 row in set (0.00 sec)
時刻: