[PHP][MySQL]レコードの作成時間と更新時間の型とか

どうしようか悩んだので。
今回は、型の選択と、結論TIMESTAMP選びましたの場合の注意点をメモ


1,型の選択


型の選択肢は
・TIMESTAMP型
・DATETIME型
・INT型(UNIXTIMESTAMP)


型の選択で考えた要素は、
・プログラムの時のめんどくささ
　TIMESTAMPはON UPDATE CURRENT TIMESTAMPで自動更新してくれる！他は一緒！


・クエリの実行速度
　MySQLでDATETIME型のデータを高速に検索する方法 | 深追い Fukaoi.org 
　INTだと早いという話
　MySQL5.5.3-m3のDATETIME型のバグ。あとMySQLのDATETIME型は本当に遅いのか検証してみた
　 INDEXすればどれも変わらないよという話


　上記を参考に、特に気にしないことにした。将来まじめに作るときはベンチマークとかしないと。


・データ解析時のめんどくささ
　UNIXTIMEはマジで視認性が悪い。条件でしぼるとき本当にストレス。
　毎回FROM_UNIXTIMEって付けてました。


・その他安定感
　TIMESTAMP型とDATETIME型
　TIMESTAMPには2037年問題があるらしい。


というわけで、気張らず使うときはTIMESTAMP、真面目に作るときはちゃんベンチとかしたうえでDATETIME使うと思う。多分。




2、TIMESTAMPの注意点


TIMESTAMP型には、初期値と自動更新属性を付与できるのですが、下記点に注意です。


・(ON UPDATE or DEFAULTの)CURENT_TIMESTAMPは1テーブル1個しか使えない
・テーブル内先頭のTIMESTAMP型カラムには、デフォルトで「DEFAULT　CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP」属性が付与される


例えばテーブル内にカラムの作成時間と更新時間を入れたいときは

   CREATE TABLE IF NOT EXISTS bbs_topics(
   ・
   created timestamp DEFAULT 0,
   updated timestamp DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
   ・

こんな感じにして、CREATE時にはプログラムの方で時間を挿入してやる必要があるみたいです（NULLを送ればCURRENT_TIMEが入る）。これはめんどくさい…


上のでcreatedの「DEFAULT 0」を抜くと、自動付与されたCURRENT_TIMESTAMP属性と、updatedのCURRENT_TIMESTAMP属性がかぶって、エラーになります
createdにDEFAULT CURRENT_TIMESTAMPを入れても当然エラーだけどしょうがないのかな