MySQL レコードの更新日を自動的にセットする

f:id:hit10231023:20180309104332j:plain

いやーーー全然・・知らなかったですが・・ALTER TABLEっていうかCREATE TABLEの時も同じですが、ON UPDATE オプションを付けると、データ更新時に指定された値を自動でセットすることができるらしい。。

DEFAULTとは違うんです・・詳細は以降から・・・

f:id:hit10231023:20160510112622p:plain

以下のテーブルをサンプルに説明します

テスト用のテーブル

 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) ;

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

Database JUNKY

MySQL,MariaDBを中心としたブログです