MySQLでデータ更新時に自動的に更新日時をセットする方法

MySQL5.6およびMariaDB 10.0 以降のカラム定義にてdatetime型にON UPDATE オプションを付けると、データ更新時に日付時刻を自動でレコードにセットすることができるらしい。

3ステップでしっかり学ぶ MySQL入門 [改訂2版]

早速ですが試してみたいと思います！！

何が言いたいかを理解していただくためにこんなテーブルを作ってみました。

テスト用のテーブル

 CREATE TABLE t1 (
  id int(11) NOT NULL,
  updated_at datetime DEFAULT CURRENT_TIMESTAMP,
  val1 int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

当該テーブルを以下のSQLでデータを格納します

t1 テーブルへINSERT

ここで、INSERTの指定時に、updated_at を入れていません。

INSERT INTO t1 (id,val1) VALUES (1,1) ;

実践ハイパフォーマンスMySQL 第3版

上記テーブルを、SELECT

さぁ結果はどうなるでしょうか？

SELECT * FROM t1 ;                                                              
+----+---------------------+------+
| id | updated_at          | val1 |
+----+---------------------+------+
|  1 | 2014-10-17 14:42:51 |    1 |
+----+---------------------+------+

updated_atは、DEFAULT指定しているので、INSERT時に指定した、CURRENT_TIMESTAMP がはいるわけですね。これはわかると思います。では、このままの指定で、対象レコードをアップデートしてみます。この際も、updated_at を指定しないで、UPDATE分をかけてみます。

UPDATE

当然ですけど、日付の値は変わりません。これはわかっていたのですが・・

UPDATE t1 SET val1 = 99 WHERE id = 1 ;
SELECT * FROM t1 ;  
+----+---------------------+------+
| id | updated_at          | val1 |
+----+---------------------+------+
|  1 | 2014-10-17 14:42:51 |   99 |
+----+---------------------+------+

では、ON UPDATE OPTION を追加してt1を再度作成してみます。ちょっとまとめてCREATE TABLE ～ INSERTまでを実行します

updated_at に ON UPDATE オプション追加

DROP TABLE IF EXISTS t1 ;

CREATE TABLE t1 (
  id int(11) NOT NULL,
  updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  val1 int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

INSERT INTO t1 (id,val1) VALUES (1,1) ;

SELECT * FROM t1 ;

+----+---------------------+------+
| id | updated_at          | val1 |
+----+---------------------+------+
|  1 | 2014-10-17 14:56:33 |    1 |
+----+---------------------+------+

上記テーブルを更新し、結果をSELECT

さきほどと同様のUPDATE分を流してみますね。どうなるでしょうか？

UPDATE t1 SET val1 = 99 WHERE id = 1 ;
SELECT * FROM t1 ;  
---
+----+---------------------+------+
| id | updated_at          | val1 |
+----+---------------------+------+
|  1 | 2014-10-17 14:59:00 |   99 |
+----+---------------------+------+

見事に、updated_at 列も、更新されているのがわかるかと思います。これ、すごく便利で全テーブルに共通の基準カラムを授けて、アプリケーション側が意識せずに、更新日フィールドを設定できるという利点があります！！ たとえば、更新差分を管理する何かのしくみがあったりすると、ここの値は、更新時に絶対入っているので、確実で且つ、管理しやすいですよね！（削除はデータ自体が消えてしまうので、だめなのですが・・・）