rails 以降のデータベース設計では、従来の主キーや論理削除フラグに替わって、サロゲートキーと作成日時、更新日時、削除日時を全てのテーブルに定義することが多くなりました。
この各日時列を mysql の timestamp 型で定義すると色々と面倒くさいですよ、というお話。
not null と default
例えば部署コードと部署名を持つ部署マスタを設計する場合、脊髄反射的にこんな ddl を書くと思います。
CREATE TABLE DEPARTMENT ( ID INT NOT NULL AUTO_INCREMENT, CODE CHAR(4) NOT NULL, NAME VARCHAR(100) NOT NULL, CREATED_AT TIMESTAMP NOT NULL, UPDATED_AT TIMESTAMP, DELETED_AT TIMESTAMP, PRIMARY KEY (ID) );
作成日時は必ずセットする。更新日時と削除日時はデフォルトを null にしたい。そんな想いがひしひしと伝わってくる素直な ddl です。
これを実行すると特にエラーも無く終了するのですが、出来上がったテーブルの desc を見てみると
mysql> desc department; +------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | CODE | char(4) | NO | | | | | NAME | varchar(100) | NO | | | | | CREATED_AT | timestamp | NO | | CURRENT_TIMESTAMP | | | UPDATED_AT | timestamp | NO | | 0000-00-00 00:00:00 | | | DELETED_AT | timestamp | NO | | 0000-00-00 00:00:00 | | +------------+--------------+------+-----+---------------------+----------------+
作成日時に指定した覚えの無いデフォルト値が設定されています。更新日時と削除日時に至っては勝手に NOT NULL になっている上に謎のデフォルト値が。
なんだこりゃ！と思ったのですが、MySQLのドキュメント にばっちり書いてあり、そういうものなんだそうです。
null の設定
勝手に世話を焼かれるのは鬱陶しい気もするのですが、作成日時のデフォルト値は確かにシステム時間にしたいし、更新日時と削除日時が NOT NULL になっていても、デフォルト値が 0000-00-00 00:00:00 なら zeroDateTimeBehavior で null 扱いにすれば期待する振る舞いと変わらないので、これでいいや、と思ってこんな初期値投入 dml を書いたりするわけです。
insert into department (code, name) values ('0001', 'テスト部署1'); insert into department (code, name) values ('0002', 'テスト部署2'); insert into department (code, name) values ('0003', 'テスト部署3');
結果を確認。
mysql> select * from department order by id; +----+------+-------------+---------------------+---------------------+---------------------+ | ID | CODE | NAME | CREATED_AT | UPDATED_AT | DELETED_AT | +----+------+-------------+---------------------+---------------------+---------------------+ | 1 | 0001 | テスト部署1 | 2009-12-27 09:29:42 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 2 | 0002 | テスト部署2 | 2009-12-27 09:29:42 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 3 | 0003 | テスト部署3 | 2009-12-27 09:29:42 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +----+------+-------------+---------------------+---------------------+---------------------+
期待通りの振る舞いです。ところが、これを一般的な O/R マッパが吐く下記のような dml で実行すると
insert into department (code, name, created_at, updated_at, deleted_at) values ('0001', 'テスト部署1', null, null, null); insert into department (code, name, created_at, updated_at, deleted_at) values ('0002', 'テスト部署2', null, null, null); insert into department (code, name, created_at, updated_at, deleted_at) values ('0003', 'テスト部署3', null, null, null);
こうなります。
mysql> select * from department order by id; +----+------+-------------+---------------------+---------------------+---------------------+ | ID | CODE | NAME | CREATED_AT | UPDATED_AT | DELETED_AT | +----+------+-------------+---------------------+---------------------+---------------------+ | 1 | 0001 | テスト部署1 | 2009-12-27 09:29:42 | 2009-12-27 09:29:42 | 2009-12-27 09:29:42 | | 2 | 0002 | テスト部署2 | 2009-12-27 09:29:42 | 2009-12-27 09:29:42 | 2009-12-27 09:29:42 | | 3 | 0003 | テスト部署3 | 2009-12-27 09:29:42 | 2009-12-27 09:29:42 | 2009-12-27 09:29:42 | +----+------+-------------+---------------------+---------------------+---------------------+
いやいやいやいや！道理で where deleted_at is null でレコードが取れないわけだよ！何してくれちゃってるわけ？
と思ったのですが、やはりこちらも MySQL のドキュメント にばっちり書いてあり、曰く
TIMESTAMP カラムは INSERT または UPDATE 操作の日付と時刻を記録するのに役立ちます。自分で値を指定しない限り、テーブルの TIMESTAMP カラムはデフォルトで一番最近の操作の日付と時刻に自動的にセットされます。NULL 値を指定する事で、現在の日付と時刻を TIMESTAMP カラムに設定する事もできます。
http://dev.mysql.com/doc/refman/5.1/ja/date-and-time-type-overview.html
ということなんだそうです。
datetime
あまりデータベースの機能に依存した設計は好きではないので、各種日時は datetime 型で統一する方向で検討してみようと思います。
