mysqlの日付けについて

過去記事です。消えたデータの一部が復旧？できたので。

どうも。 せっかくの土日で超いい天気なのに、体調不良で引きこもっているundertreeです。。。

あぁ自転車（ロード）に乗りたい。

最近ERD書いてて、作成日と更新日の日付けの管理にすごく悩んでて、時間もなかったので、

マイナーなtimestampで進めていたのですが、再度別のERDを書くことになったので、

気になったので調べました。

結論

5.6.4以降であれば、5byteになるので、datetimeが適切かな。

5.6.4以前であれば、timestampで運用しつつ、2038年に近づいたら、datatimeに変更もあり。

※しかし、データが多いと変更が困難かも。。。

mysqlは、後方互換性を担保しているので、5.6.4以降になることを見据えて、

はじめからdatetimeでやっておくのが、一番いいかも。

カラムのストレージ容量とかは、サーバのメモリ増強とかスケールでなんとかなるので。

日付け型のカラム

MySQLの日付型の扱い方や機能をまとめてみました

DATETIME

日付 + 時刻

表示形式 : YYYY-MM-DD HH:MM:SS

範囲 : 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

DATE

日付

表示形式 : YYYY-MM-DD

範囲 : 1000-01-01 ~ 9999-12-31

TIMESTAMP

• MySQLバージョン
• SQLモード

によって異なる

日付 + 時刻

表示形式 : YYYY-MM-DD HH:MM:SS

範囲 : 1970-01-01 00:00:00 ~ 2038-12-31 23:59:59

ストレージ容量

引用元

注意点

不正な日付が入力された場合は 指定フォーマットに全て0が代入された値が入力される。

フォーマットは、変更可能だが、全ての日付に適応される。 例) YY-MM-DDなど

検索について

int型で保存して、使用する

MySQLでDATETIME型のデータを高速に検索する方法

int型で保存して、表示するときに変換して使用した方が3~5倍高速になるらしい。

mysql5.5以上ならdatetimeでも速い？

日付けの型にINDEXを使用する場合

datetime型など日付け用の型の検索の場合は、 BETWEENを使用することで、INDEXを使用した検索が可能。

timestamp => datetimeに型を変更

timestamp => datetimeに型を変更しても問題ないか確認

※計算や関数を使った場合の違いなど、細かい確認はしていないので、ご注意ください。

あくまで、過去のデータが 0000-00-00 などに消失、破損しないか確認したものになります。

-- 作成したテーブル

CREATE TABLE `date_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp,
  `updated_at` timestamp,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

-- 確認

desc date_test;

+------------+-----------+------+-----+---------------------+-----------------------------+
| Field      | Type      | Null | Key | Default             | Extra                       |
+------------+-----------+------+-----+---------------------+-----------------------------+
| id         | int(11)   | NO   | PRI | NULL                | auto_increment              |
| created_at | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| updated_at | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+------------+-----------+------+-----+---------------------+-----------------------------+

3 rows in set (0.00 sec)

-- 適当にinsert

select * from date_test;

+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 2014-05-18 06:09:53 | 2014-05-18 06:09:53 |
|  2 | 2014-05-18 06:10:03 | 2014-05-18 06:10:03 |
|  3 | 2014-05-18 06:10:04 | 2014-05-18 06:10:04 |
|  4 | 2014-05-18 06:10:05 | 2014-05-18 06:10:05 |
|  5 | 2014-05-18 06:10:05 | 2014-05-18 06:10:05 |
+----+---------------------+---------------------+

5 rows in set (0.00 sec)

-- timestamp => datetimeに型を変更

alter table date_test change created_at created_at datetime;

-- 変更後確認

select * from date_test;

+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 2014-05-18 06:09:53 | 2014-05-18 06:09:53 |
|  2 | 2014-05-18 06:10:03 | 2014-05-18 06:10:03 |
|  3 | 2014-05-18 06:10:04 | 2014-05-18 06:10:04 |
|  4 | 2014-05-18 06:10:05 | 2014-05-18 06:10:05 |
|  5 | 2014-05-18 06:10:05 | 2014-05-18 06:10:05 |
+----+---------------------+---------------------+

5 rows in set (0.00 sec)

desc date_test;

+------------+-----------+------+-----+---------------------+----------------+
| Field      | Type      | Null | Key | Default             | Extra          |
+------------+-----------+------+-----+---------------------+----------------+
| id         | int(11)   | NO   | PRI | NULL                | auto_increment |
| created_at | datetime  | YES  |     | NULL                |                |
| updated_at | timestamp | NO   |     | 0000-00-00 00:00:00 |                |
+------------+-----------+------+-----+---------------------+----------------+

3 rows in set (0.00 sec)