Database JUNKY

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

トップ > ALTER TABLE > 知らなかった・・ ALTER TABLE .. ON UPDATE オプションの存在

知らなかった・・ ALTER TABLE .. ON UPDATE オプションの存在

ALTER TABLE create table current_timestamp database default maria MariaDB MySQL ON UPDATE
logomysql MariaDB_Logo_from_SkySQL_Ab

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

DEFAULTとは違います！DEFAULT とは。。

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

テスト用のテーブル

[code lang=sql] CREATE TABLE t1 ( id int(11) NOT NULL, updated_at datetime DEFAULT CURRENT_TIMESTAMP, val1 int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 [/code]

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

t1 テーブルへINSERT

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

[code lang=sql] INSERT INTO t1 (id,val1) VALUES (1,1) ; [/code]

上記テーブルを、SELECT

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

[code lang=sql] SELECT * FROM t1 ;
+----+---------------------+------+ | id | updated_at | val1 | +----+---------------------+------+ | 1 | 2014-10-17 14:42:51 | 1 | +----+---------------------+------+ [/code]

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

UPDATE

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

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

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

updated_at に ON UPDATE オプション追加

[code lang=sql] 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 | +----+---------------------+------+ [/code]

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

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

[code lang=sql] UPDATE t1 SET val1 = 99 WHERE id = 1 ;

SELECT * FROM t1 ;

+----+---------------------+------+ | id | updated_at | val1 | +----+---------------------+------+ | 1 | 2014-10-17 14:59:00 | 99 | +----+---------------------+------+ [/code]

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