データベースに更新日時を格納する際、今の日時を取得するSQLの関数を利用できます。さらに、データベース管理ソフトによっては、レコードを更新した日時を自動で格納することも可能です。
ここでは、このような更新日時の保存を、データベース管理ソフトの機能を使って実現するためのSQLを、例を使って解説します。
SQLで更新日時を登録するには
データベースを利用した仕組みでは、更新日時を登録しておき、それをデータの検証などで活用するケースがよくあります。次から、SQLで実現できる更新日時を登録する仕組みについて紹介します。
SQLの関数を使って更新日時をセットする
Oracle DatabaseやMySQLといったSQLが使えるデータベース管理ソフトには、今の日時を取得する機能が用意されています。そして、データを更新する際に、SQLからその機能を使うことで、簡単に更新日時を登録できます。
なお、SQLで更新日時をデータベースに登録するためには、テーブルを作成する際に、更新日時を登録する列をTIMESTAMP型で登録しなければなりません。
また、今の時点の日時を取得するSQLの標準関数は、CURRENT_TIMESTAMPです。また、データベース管理ソフトによっては、NOW関数が使用できます。
よく使われるOracle Database, Microsoft SQL Server, MySQL, PostgreSQLでは、現在の時刻を取得するのにCURRENT_TIMESTAMPが使えます。
また、MySQLとPostgreSQLでは、現在の時刻を取得する関数として、NOW()も使えます。
テーブルのデフォルトで日時を自動登録
データベース管理ソフトには、INSERT文で指定しなくても、自動でデータを格納する機能があります。そして、この機能は、CREATE文の中でDEFAULT句を使用して指定します。
なお、この機能は、レコードを作成した時点でデータがセットされなかった場合に登録する値を、予め設定しておく機能です。
そして、DEFUALT句に、前出のCURRENT_TIMESTAMP関数が設定されている場合、レコードが生成された際に自動的にこの関数が呼び出されて、その時点の日時が登録されます。
レコードの更新日時を自動登録する設定
先ほど、DEFUALT句を利用し、レコードを生成した時点の日時を自動登録する設定について紹介しましたが、MySQLでは、ON UPDATEを使うことで、レコードが更新された際に自動的にその更新日時を登録できます。
なお、今使われているデータベース管理ソフトは、SQLの国際標準に準拠していますが、それぞれ独自の拡張もあります。今回紹介するレコードの更新日時を自動登録する設定も、独自の拡張機能です。そのため、それぞれの書き方は、他のデータベース管理ソフトでは使えないので注意してください。
標準的な更新日時を使うSQLの例
通常、SQLで日時を格納するには、その列をTIMESTAMP型で定義します。このTIMESTAMP型で更新日時を登録するテーブルを作り、CURRENT_TIMESTAMP関数を格納する例を次に紹介します。
更新日時を格納するテーブルの例 CREATE TABLE ts ( id INT(11) NOT NULL PRIMARY KEY, updated_at TIMESTAMP, val INT(11) );
登録時の日時を登録する例 INSERT INTO ts (id, updated_at, val) VALUES (1, CURRENT_TIMESTAMP, 1);
更新時の日時を登録する例 UPDATE ts SET val=2, updated_at=CURRENT_TIMESTAMP where id=1;
MySQL特有の機能を使う
オープンソースのデータベース管理ソフトとして人気のMySQLには、標準SQLに準拠した機能の他に、MySQL特有の機能が多く実装されています。そして、データの更新日時を格納する機能にも、MySQL特有の便利な機能もあるので紹介します。
MySQLはNOW()が使える
先ほど、更新日時を取得する関数としてCURRENT_TIMESTAMPを紹介しましたが、MySQLの更新日時を取得する関数は、NOW関数です。なお、MySQLでは、CURRENT_TIMESTAMP関数も使えます。なお、内部では、CURRENT_TIMESTAMPが呼ばれた際に実施に動作するのはNOW関数です。
また、標準SQLでは日時の登録にTIMESTAMP型を使いますが、MySQLではDATETIME型を使います。これは、TIMESTAMP型がUNIXの時刻処理をそのまま利用しており、2038年1月19日に誤動作するためで、DATETIME型ではそのような問題がありません。
次に、MySQL特有の機能を利用した例を紹介します。
更新日時を格納するテーブルの例 CREATE TABLE ts ( id INT(11) NOT NULL PRIMARY KEY, updated_at DATETIME, val INT(11) );
登録時の日時を登録する例 INSERT INTO ts (id, updated_at, val) VALUES (1, NOW(), 1);
更新時の日時を登録する例 UPDATE ts SET val=3, updated_at=NOW() where id=1;
MySQLのON UPDATEの例
先ほども説明したように、MySQLには、テーブル作成時にDEFUALT句でON UPDATEを利用することで、レコードの列を更新した際に、自動で更新日時を更新させることが可能です。次に、この例を紹介します。
MySQLのON UPDATEを利用したSQLの例 CREATE TABLE t1 ( id int(11) NOT NULL PRIMARY KEY, updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, val int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
上の例で作成されたテーブルt1では、valの値を更新すると、自動でupdated_atに、更新日時が登録されます。
PostgreSQLのfunctionを活用する
SQLには、特定のテーブルの列が変化したら、それをトリガーとして処理する、TORIGGERという機能があります。先ほど解説したMySQLのON UPDATEを機能は、TRIFGGERを使うことで、他のデータベース管理ソフトでも実現できます。
ただし、TORIGGERの中に、特定のテーブルの特定の列に現在の日時を書きこむ処理をSQLで記述するのは簡単ではありません。
その点、PostgreSQLでは、functionという関数を作る機能を活用することで、MySQLのON UPDATEと同じ処理を実現できます。
functionとtriggerを使った更新日時を自動登録する例
create table t2 (\
id int not null primary key,
updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
val int not null
);
create function set_at() returns opaque as '
begin
new.updated_at := now();
return new;
end;
' language 'plpgsql';
create trigger update_try before update on t2 for each row
execute procedure set_at();
insert into t2 (id, val) values (1,1);
上記のSQLでテーブルを作成した場合、次のように、そのレコードに含まれる1つの列を更新するだけで、更新日時を自動で書き込みます。
列を更新するSQLの例 update t2 set val=10 where id=1;
まとめ
データベースに格納しているデータを更新した日時を、SQLの現在の日時を取得する関数を使って格納する方法について、紹介してきました。
もちろん、プログラム側で日時の文字列を作成すれば、簡単なSQLで格納できます。しかし、今回紹介したように、データベース管理ソフトの機能を使うことで、自動で更新日時を格納することも可能です。
ぜひ、データベース管理ソフトの機能をうまく利用するスキルを身に付けてください。
