HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

TIMESTAMP型の位置づけ

SQL MySQL SQL Server

知っている人は知っている、というお話(だと思う)。

以前に、Microsoft SQL ServerでTIMESTAMP型の列にsetTimestampできないというのをどこかで読んでからずっと引っかかっていたこと。(参照：メモ: SQL Server の timestamp 型に insert すると Disallowed implicit conversion from data type datetime to data type timestamp が発生する - #侍ズム)
そして、先日の検証(CURRENT_TIMESTAMPをTIMESTAMP型の列のデフォルト値に指定してみる - HHeLiBeXの日記 正道編)の際に、(書いていないことも含めると)次のような現象を確認していた。

  • MySQLでは、TIMESTAMP型の列にDEFAULT CURRENT_TIMESTAMPやON UPDATEを指定できるのは1つの列に対してのみ
  • SQL Serverでは、TIMESTAMP型の列は1つのテーブルに1つしか定義できない

先日の検証での内容の確認だが(謎)、前者のMySQLの場合に発生していたエラーは次のようなもの：

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

同様に、後者のSQL Serverの場合に発生していたエラーは次のようなもの：

com.microsoft.sqlserver.jdbc.SQLServerException: テーブルには timestamp 列を 1 つだけ含めることができます。テーブル 'hoge1' には timestamp 列が既に 1 つあるので、列 'ts2' を追加できません。

そして、いろんなドキュメント等を読み漁ってみる：

すると、MySQLおよびSQL ServerにおけるTIMESTAMP型というのは、SQL標準規格で定められているTIMESTAMP型とは別物ではないだろうかということが見えてくる。
事実そのとおりで、SQL標準規格におけるTIMESTAMP型には、MySQLおよびSQL ServerではDATETIME型が対応する。
そこで、次のようなクエリを各DBMSに対して実行してみた。(わかりやすくするために、各SQL文を';'で区切っている)(諸事情(何)により、テーブル名が"foo3"と"foo4"になっているが気にしてはいけない(謎))

CREATE TABLE foo3(id INT NOT NULL, c DATETIME ) ;
CREATE TABLE foo4(id INT NOT NULL, c TIMESTAMP) ;

INSERT INTO foo3(id) VALUES(31) ;
INSERT INTO foo3(id) VALUES(32) ;
UPDATE foo3 SET id = 33 WHERE id = 32 ;

INSERT INTO foo4(id) VALUES(41) ;
INSERT INTO foo4(id) VALUES(42) ;
UPDATE foo4 SET id = 43 WHERE id = 42 ;

結果は次のような感じ。

  • TIMESTAMP型のみサポート、デフォルト値はNULL
  • TIMESTAMP型とDATETIME型が同義、デフォルト値はNULL
  • TIMESTAMP型とDATETIME型の意味が違う
    • TIMESTAMP型はinsert/update時刻(指定されるオプションによる)
    • TIMESTAMP型はシステム内でユニークなバイナリ値(Stringとして取得すると"00000000000007DC"みたいなのが返される)

MySQLでは、TIMESTAMP型はデフォルト値がNULLでないあたりからしてSQL標準規格やほかのDBMSとは違う。
SQL Serverにいたっては、日時とは全然関係ない(実際、SQL Server 2000以降ではROWVERSIONシノニムを使用することが推奨されている)。


ところで、先日の検証(CURRENT_TIMESTAMPをTIMESTAMP型の列のデフォルト値に指定してみる - HHeLiBeXの日記 正道編)で列のデフォルト値をCURRENT_TIMESTAMPにするという話があったが、(SQL Serverは前回記事に書いてあるのでいいとして)MySQLではDATETIME型を使えばすべて解決かというと、そう簡単な話ではない。

CREATE TABLE hoge(
      id INT NOT NULL
    , ts1 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    , ts2 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)

MySQLでこれを実行すると、「ts1の初期値が不正です」みたいなエラーが発生する。つまり、CURRENT_TIMESTAMPをDATETIME型の列のデフォルト値にできないということ。
要するに、(CREATE TABLE文は別として)対象とする全DBMSで同じSQL文を使用しようと思ったら、「DEFAULT CURRENT_TIMESTAMP」するのではなく、insert時にCURRENT_TIMESTAMPを明示してやる必要がある。
実際は、DAOとかでSQL文は覆い隠すだろうから、次のようなSQL文を書いておくという感じか：

String sql = "INSERT INTO hoge(id, ts1, ts2) VALUES(?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();


これを知らずにMySQLでTIMESTAMP型を使用しているケースはありそうだな。知らずに「regist_date TIMESTAMP」とか書いてて、登録日時が毎回変わってしまう、みたいな(怖)。