掲載日と更新日の管理の仕方

質問

phpmyadminを使ってmysqlのデータを更新しています。
掲載日：2008年5月25日
更新日：2012年6月04日
というようにphpで表示し、掲載順、更新順といったソートをしたいと思っています。

phpmyadminで作ったテーブルは

CREATE TABLE IF NOT EXISTS `data_data` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL,
`modified_date` date NOT NULL,
`create_date` date NOT NULL,
PRIMARY KEY (`id`),
KEY `modified_date` (`modified_date`,`create_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `data_data` (`id`, `name`, `modified_date`, `create_date`) VALUES
(1, '23434', '2012-06-04', '2012-04-02'),
(2, '464645', '2012-06-03', '2012-03-04'),
(3, '940968', '2012-06-03', '2011-03-23'),
・・・
(15000, '940968', '2012-06-03', '2011-03-23');

のようになっています。

SELECT * from data_data ORDER BY modified_date DESC LIMIT 0,50;
phpでこの構文で取得します。

・更新日を手作業で指定し直さないといけない。
・掲載日より前の日付で更新日を入ることができて、おかしくなってしまう。
といったことになってしまいます。
phpmyadminを使っている限り仕方がないのでしょうか。
たとえば、一流なサイトはどうやって掲載日・更新日を管理しているのしょうか。

回答No.4

yambejp · ベストアンサー

>modified_dateはそのときの日時に更新されますが、
>create_dateは更新されずに作成した日時のままです。

ああ、そういえば最近のMySQLはタイムスタンプカラムを２つ以上もてる
んでしたね、その場合ON UPDATE CURRENT_TIMESTAMP属性がついている
カラムが更新対象になるので、ご指摘のとおりとなると思います
ちなみにもともとはタイムスタンプは１テーブルに１つしか設定できないという
特性がありました、それは更新日時をいれるカラムなのだから二つ以上
同じ内容を入れておく必要がないという理由からでしょう

現在はポリシーもかわり二つ以上のタイムスタンプをもつことができるようになりましたが
前提としてタイムスタンプとはももと現日時を更新させるものですので
初回投入時にいれるような作成日時などには向いていないということです

タイムスタンプのもうひとつの特徴としては作成・更新日時をいれる器ですから
UNIXタイムの範囲だけ確保できればすむ分、確かにとりうる範囲が狭くてすみます
（つまり、はるか昔やはるか未来を持つ必要がないということです）
それでも検索効率やメモリに対する影響はさほど大きいとはいえないでしょうし
極力１テーブル１タイムスタンプは守るくせをつけておいたほうが無難かと

お礼

本当にありがとうございます。
そういう意味だったのですか、よく分かりました。

>前提としてタイムスタンプとはももと現日時を更新させるものですので
>初回投入時にいれるような作成日時などには向いていないということです
>極力１テーブル１タイムスタンプは守るくせをつけておいたほうが無難かと
どうしても自動に魅力があるので、タイムスタンプでやってみます。
よく覚えておきます。ありがとうございました。

この内容に関連するサンプルを作ってみたのですが、もしよければ見て下さい。
http://oshiete.goo.ne.jp/qa/7518982.html

回答No.3

yambejp

>自動的に作成日時が指定されて、メモリも少ないtimestampでなく、
>datetimeの方がいいのはなぜでしょうか？

タイムスタンプにすると、レコードのどこかデータを修正すると
その時点の日時に書き変わってしまうので、「作成日」としては役にたたないです
もちろん投入したデータが金輪際書き変わらないない前提であれば
タイムスタンプでもいい気がしますが

お礼

＞タイムスタンプにすると、レコードのどこかデータを修正すると
＞その時点の日時に書き変わってしまう
nameのデータを変えると、
modified_dateはそのときの日時に更新されますが、
create_dateは更新されずに作成した日時のままです。

変更するカラムが１つだけだから？と思い、
カラムをもう１つ追加してみましたが、create_dateは更新されません。

バージョンが少し低いからですか？
Mysqlのバージョンは5.0.51a です。

そういうことでなく、誤動作だとcreate_dateが更新されやすいということでしょうか？

回答No.2

yambejp

>| create_data | timestamp |

typoだとは思いますがクリエートデータ？

create_dateはレコードを最初に投入した日時を入れておくものだと
推察されるので、動的に更新されるタイムスタンプである必要はなく
datetime型がよいのでは？

またmodified_dateについては、わざわざ指定ししなくても
INSERTやUPDATEに応じて書き変わっていくので、INSERT項目からはずして
問題ないかと思います

お礼

datetime：自動更新無し・メモリの消費多い
timestamp：自動更新有り・メモリの消費少ない
http://www.dbonline.jp/mysql/type/index4.html
http://okwave.jp/qa/q5504237.html

＞create_dateはレコードを最初に投入した日時を入れておくものだと推察
その通りです。

＞動的に更新されるタイムスタンプである必要はなくdatetime型がよいのでは？
そうすると、ローを追加するときに手動で指定する必要が出てくると思います。
メモリの消費も多くなってソートとかするときにパフォーマンス的に心配です。
自動的に作成日時が指定されて、メモリも少ないtimestampでなく、
datetimeの方がいいのはなぜでしょうか？

＞またmodified_dateについては、わざわざ指定ししなくても
＞INSERTやUPDATEに応じて書き変わっていく
そうなんですか。ありがとうございます。
phpmyadminで追加するときに勝手に入ってしまっているので、
でもそれはそれで良いということですね。

回答No.1

yambejp

＞・更新日を手作業で指定し直さないといけない。

タイムスタンプをつかうなり、更新時にCURDATE()で上書きするなりしてみては？

＞・掲載日より前の日付で更新日を入ることができて、おかしくなってしまう。
といったことになってしまいます。

もし手で更新日を入力する仕組みをつくっていれば、矛盾があって当然では？
ただし運用の問題なので、システムとしては特にないと思います
どうしても気になるなら更新日を手で触らせないようにすべき

お礼

http://isoldeblog.blog93.fc2.com/blog-entry-14.html
http://www.dbonline.jp/mysql/table/index6.html
こちらを参考にして試してみました。

+---------------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------+------+-----+-------------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| modified_date | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
| create_data | timestamp | YES | MUL | NULL | |
+---------------+-----------+------+-----+-------------------+-------+

modified_dateの属性にはon　update　CURRENT_TIMESTAMPを設定し、
rowの追加をするときはmodified_dateはCURRENT_TIMESTAMPになっていて
create_dataは空のままにして実行ボタンを押すと、

INSERT INTO `data_test`.`data_data` (`id`, `name`, `modified_date`, `create_data`) VALUES ('6', '23456', CURRENT_TIMESTAMP, NOW());

というようになり自動的に日付が変わるようになりました。
このようなやり方で問題はないでしょうか。