はじめに
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 を入れらないようにするなどしたほうがいいと思います。
