はじめに
今回はMySQLのDATETIME型とTIMESTAMP型の挙動の違いを検証してみました。
MySQL5.6を使用しています。
検証
値のサポート範囲の違い
| DATETIME型 | TIMESTAMP型 |
|---|---|
| '1000-01-01 00:00:00' ～ '9999-12-31 23:59:59' | '1970-01-01 00:00:01' UTC ～ '2038-01-19 03:14:07' UTC |
TIMESTAMP型はサポートされている範囲が狭いため注意が必要です。
DATETIME型は月や日付に 00 を許容するため、こちらも注意が必要です。
登録される値の違い
以下のような値を指定したとき、データベースにはどのような値が登録されるのか検証しました。
- 値を指定しない
- 空文字を指定する
- NULLを指定する
- 0を指定する
- 月や日付に
00を指定する(2016-00-00 00:00:00) - 数字以外の文字を含む値を指定する(aaaa-aa-aa aa:aa:aa)
DATETIME型のとき
DATETIME型のカラムを持つテーブルを作成します。
CREATE TABLE datetime_tb (
id int NOT NULL AUTO_INCREMENT,
description varchar(20),
no_default datetime,
default_null datetime DEFAULT NULL,
default_zero datetime DEFAULT 0,
PRIMARY KEY (id)
);
実際に作られたテーブルの構成を確認してみます。
datetime型のカラムでDEFAULTに値を指定しなかった場合、
DEFAULTは NULL となりました。
mysql> SHOW COLUMNS FROM datetime_tb;
+--------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| descreption | varchar(20) | YES | | NULL | |
| no_default | datetime | YES | | NULL | |
| default_null | datetime | YES | | NULL | |
| default_zero | datetime | YES | | 0000-00-00 00:00:00 | |
+--------------+-------------+------+-----+---------------------+----------------+
5 rows in set (0.00 sec)
このテーブルに対して以下のようなデータを投入します。
INSERT INTO datetime_tb (description) VALUE ("指定しない");
INSERT INTO datetime_tb (description, no_default, default_null, default_zero) VALUE ("空文字", "", "", "");
INSERT INTO datetime_tb (description, no_default, default_null, default_zero) VALUE ("NULL", NULL, NULL, NULL);
INSERT INTO datetime_tb (description, no_default, default_null, default_zero) VALUE ("0", 0, 0, 0);
INSERT INTO datetime_tb (description, no_default, default_null, default_zero) VALUE ("月、日付が00", "2016-00-00 00:00:00", "2016-00-00 00:00:00", "2016-00-00 00:00:00");
INSERT INTO datetime_tb (description, no_default, default_null, default_zero) VALUE ("数値でない", "aaaa-aa-aa aa:aa:aa", "aaaa-aa-aa aa:aa:aa", "aaaa-aa-aa aa:aa:aa");
実際にテーブルにはどのような値が登録されたのか、下記のようにして確認しました。
mysql> SELECT * FROM datetime_tb;
+----+-------------------+---------------------+---------------------+---------------------+
| id | description | no_default | default_null | default_zero |
+----+-------------------+---------------------+---------------------+---------------------+
| 1 | 指定しない | NULL | NULL | 0000-00-00 00:00:00 |
| 2 | 空文字 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 3 | NULL | NULL | NULL | NULL |
| 4 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 5 | 月、日付が00 | 2016-00-00 00:00:00 | 2016-00-00 00:00:00 | 2016-00-00 00:00:00 |
| 6 | 数値でない | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-------------------+---------------------+---------------------+---------------------+
6 rows in set (0.00 sec)
- 基本的にはINSERTで指定した値が入る
- カラムに値を指定しなかった場合とアルファベットなど数値としてふさわしくない値が入る場合は、DEFAULT値が入る
- 月や日付に
00を許容する
TIMESTAMP型のとき
続いてTIMESTAMP型のカラムを持つテーブルを作成します。
CREATE TABLE timestamp_tb (
id int NOT NULL AUTO_INCREMENT,
description varchar(20),
no_default timestamp,
default_zero timestamp DEFAULT 0,
PRIMARY KEY (id)
);
実際に作られたテーブルの構成を確認してみます。
timestamp型のカラムでDEFAULTに値を指定しなかった場合、
DEFAULTは CURRENT_TIMESTAMP となり、レコードが更新された時に自動で日時が更新されるように設定されました。
mysql> SHOW COLUMNS FROM timestamp_tb;
+--------------+-------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| description | varchar(20) | YES | | NULL | |
| no_default | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| default_zero | timestamp | NO | | 0000-00-00 00:00:00 | |
+--------------+-------------+------+-----+---------------------+-----------------------------+
4 rows in set (0.00 sec)
また、timestamp型のカラムのDEFAULTに、
NULL を指定したところエラーとなりました。
mysql> CREATE TABLE timestamp_tb (
-> id int NOT NULL AUTO_INCREMENT,
-> description varchar(20),
-> no_default timestamp,
-> default_null timestamp DEFAULT NULL,
-> default_zero timestamp DEFAULT 0,
-> PRIMARY KEY (id)
-> );
ERROR 1067 (42000): Invalid default value for 'default_null'
このテーブルに対して以下のようなデータを投入します。
INSERT INTO timestamp_tb (description) VALUE ("指定しない");
INSERT INTO timestamp_tb (description, no_default, default_zero) VALUE ("空文字", "", "");
INSERT INTO timestamp_tb (description, no_default, default_zero) VALUE ("NULL", NULL, NULL);
INSERT INTO timestamp_tb (description, no_default, default_zero) VALUE ("0", 0, 0);
INSERT INTO timestamp_tb (description, no_default, default_zero) VALUE ("月、日付が00", "2016-00-00 00:00:00", "2016-00-00 00:00:00");
INSERT INTO timestamp_tb (description, no_default, default_zero) VALUE ("数値でない", "aaaa-aa-aa aa:aa:aa", "aaaa-aa-aa aa:aa:aa");
実際にテーブルにはどのような値が登録されたのか、下記のようにして確認しました。
mysql> SELECT * FROM timestamp_tb;
+----+-------------------+---------------------+---------------------+
| id | description | no_default | default_zero |
+----+-------------------+---------------------+---------------------+
| 1 | 指定しない | 2016-03-16 01:40:12 | 0000-00-00 00:00:00 |
| 2 | 空文字 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 3 | NULL | 2016-03-16 01:41:12 | 2016-03-16 01:41:12 |
| 4 | 0 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 5 | 月、日付が00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 6 | 数値でない | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+-------------------+---------------------+---------------------+
7 rows in set (0.00 sec)
- 値を指定しなかった場合やNULLが指定された場合は、DEFAULT値が入る。
- 空文字や0、日付としてふさわしくない値が指定された場合は、
0000-00-00 00:00:00が入る - 月や日付に
00を許容しない
まとめ
datetime型とtimestamp型では同じ値を指定した場合でも、登録される値に差があることがわかりました。
また、型によって値をサポートする範囲にも差があることも考慮し、
どちらの型を使うべきか検討するように気を付けようと思います。
参考サイト
MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型