MySQL5.6におけるdatetimeの挙動を実際に試してみた

はじめに

MySQL5.6におけるdatetimeの挙動で？？？ってなったので、仕様を確認しつつ、実際に挙動を確かめてみました。

datetimeの仕様

デフォルト値に関しては、ドキュメントにこのように記載されています。

DATETIME は、NOT NULL 属性で定義されていないかぎり (この場合、デフォルトは 0 です)、デフォルトは NULL です。

SELECTに関しては、ドキュメントにこのように記載されています。

NOT NULL として宣言された DATE および DATETIME カラムでは、次のようなステートメントを使用することで、特殊な日付 '0000-00-00' を検索できます。
SELECT * FROM tbl_name WHERE date_column IS NULL

datetimeの挙動

先程の仕様を実際にテーブルを作って、データを入れて確かめてみます。

datetimeがNOT NULLでデフォルト値なしの場合

CREATE TABLE `datetime_not_null` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `datetime_not_null` (`id`)
VALUES(1);
// created_atには0000-00-00 00:00:00が入ります

INSERT INTO `datetime_not_null` (`id`, `created_at`)
VALUES(2, NULL);
// エラーになります

INSERT INTO `datetime_not_null` (`id`, `created_at`)
VALUES(3, '');
INSERT INTO `datetime_not_null` (`id`, `created_at`)
VALUES(4, 'aaa');
// created_atには0000-00-00 00:00:00が入ります
// 「Data truncated for column 'created_at' at row 1」という警告が出ます

SELECT * FROM datetime_not_null WHERE created_at IS NULL
// idが1, 3, 4のデータを取得できます

datetimeがNOT NULLでデフォルト値ありの場合

CREATE TABLE `datetime_not_null` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `datetime_not_null` (`id`)
VALUES(1);
// created_atにはデフォルト値の現在時刻が入ります

INSERT INTO `datetime_not_null` (`id`, `created_at`)
VALUES(2, NULL);
// エラーになります

INSERT INTO `datetime_not_null` (`id`, `created_at`)
VALUES(3, '');
INSERT INTO `datetime_not_null` (`id`, `created_at`)
VALUES(4, 'aaa');
// created_atには0000-00-00 00:00:00が入ります
// 「Data truncated for column 'created_at' at row 1」という警告が出ます

datetimeがNULL許容で、デフォルト値なしの場合

CREATE TABLE `datetime_null` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `datetime_null` (`id`)
VALUES(1);
INSERT INTO `datetime_null` (`id`, `created_at`)
VALUES(2, NULL);
// created_atにはNULLが入ります

INSERT INTO `datetime_null` (`id`, `created_at`)
VALUES(3, '');
INSERT INTO `datetime_null` (`id`, `created_at`)
VALUES(4, 'aaa');
// created_atには0000-00-00 00:00:00が入ります
// 「Data truncated for column 'created_at' at row 1」という警告が出ます

datetimeがNULL許容で、デフォルト値ありの場合

CREATE TABLE `datetime_null` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `datetime_null` (`id`)
VALUES(1);
// created_atにはデフォルト値の現在時刻が入ります

INSERT INTO `datetime_null` (`id`, `created_at`)
VALUES(2, NULL);
// created_atにはNULLが入ります

INSERT INTO `datetime_null` (`id`, `created_at`)
VALUES(3, '');
INSERT INTO `datetime_null` (`id`, `created_at`)
VALUES(4, 'aaa');
// created_atには0000-00-00 00:00:00が入ります
// 「Data truncated for column 'created_at' at row 1」という警告が出ます

おわりに

MySQLのdatetimeに 0000-00-00 を入れると、思わぬ動作をする可能性があるので、注意が必要です。
また、この機能はSQL標準ではなく、MySQL独自のもので、DB移行時に移行先のDBが 0000-00-00 に対応していなかったり、ORMがデフォルトで対応していなかったりして面倒なのでMySQLがdatetimeでサポートしている値（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59）をデフォルト値として入れる、モードの設定で 0000-00-00 を入れらないようにするなどしたほうがいいと思います。