【MySQL】DATE/DATETIME型の'0000-00-00'について整理してみる

■ 結論

● sql_mode が NO_ZERO_DATE (NO_ZERO_IN_DATE) であり、かつ STRICT_TRANS_TABLE (STRICT_ALL_TABLES) であるとき、'0000-00-00' は SELECT しかできない。
（MySQL5.7 以上のデフォルト値であるときの挙動）

● sql_mode が NO_ZERO_DATE (NO_ZERO_IN_DATE) でない、または STRICT_TRANS_TABLE (STRICT_ALL_TABLES) でないとき、NULLを許可しない列に限り、'0000-00-00' は IS NULL にもマッチする。
（MySQL5.6 以下のデフォルト値であるときの挙動）

■ はじめに

● MySQLでは0年0月0日、すなわち '0000-00-00' などといった日付を登録することができる。

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format
上記を参照していただくとわかるようにMySQLは、『月』と『日』が許可する値にゼロを含んでいる。

● '0000-00-00' の取り扱いについて

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
sql_mode の NO_ZERO_DATE (NO_ZERO_IN_DATE), STRICT_TRANS_TABLE (STRICT_ALL_TABLES) で、取り扱いの変更をすることができる。

MySQL5.7.19での sql_mode のデフォルト値は以下の通り。

SHOW VARIABLES LIKE 'sql_mode';

+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+

※以下、特に対比されていないとき NO_ZERO_DATE は NO_ZERO_IN_DATE、STRICT_TRANS_TABLE は STRICT_ALL_TABLESと読み替えても差し支えない。

■ 実行時のWarning, Errorについて

● DML実行時について

MySQLのsql_modeのせいで'0000-00-00'登録時にエラーが出た昔話。

上記を参考にしつつまとめると、DML実行時の挙動は sql_mode によって下記のように異なる。

NO_ZERO_DATE	STRICT_TRANS_TABLE	DML実行時(SELECTのぞく)	SELECT実行時
はい	はい	失敗 (Errorが出力)	成功 (Warningが出力)
はい	いいえ	成功 (Warningが出力)	成功 (Warningが出力)
いいえ	いいえ	成功	成功

● NO_ZERO_DATE が有効だと、年がゼロでも怒られてしまう。

もちろん時分秒はＯＫ。また NO_ZERO_DATE でなく NO_ZERO_IN_DATE であったらＯＫ。

SELECT 
    STR_TO_DATE('0000/00/00', '%Y/%m/%d') AS '0000/00/00',
    STR_TO_DATE('0000/00/01', '%Y/%m/%d') AS '0000/00/01',
    STR_TO_DATE('0000/01/01', '%Y/%m/%d') AS '0000/01/01',
    STR_TO_DATE('0001/01/01', '%Y/%m/%d') AS '0001/01/01',
    STR_TO_DATE('0001/01/01 00:00:00', '%Y/%m/%d %H:%i:%s') AS '0001/01/01 00:00:00'
;

+------------+------------+------------+------------+---------------------+
| 0000/00/00 | 0000/00/01 | 0000/01/01 | 0001/01/01 | 0001/01/01 00:00:00 |
+------------+------------+------------+------------+---------------------+
| NULL       | NULL       | NULL       | 0001-01-01 | 0001-01-01 00:00:00 |
+------------+------------+------------+------------+---------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '0000/00/00' for function str_to_date
*************************** 2. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '0000/00/01' for function str_to_date
*************************** 3. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '0000/01/01' for function str_to_date
3 rows in set (0.00 sec)

● MySQL Workbench で SELECT すると、しれっと Message に『1 row(s) returned』と出るだけで気付きづらい。

● NO_ZERO_DATE は、CREATE TABLE するときのデフォルト値にも影響する。

CREATE TABLE `for_test`.`new_table` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `datetime_nullable` DATETIME NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`id`)
);

ERROR 1067 (42000): Invalid default value for 'datetime_nullable'

■ '0000-00-00' と NULLの関係性について

● 準備

以降は STRICT_TRANS_TABLE が有効でないときのおはなし。

CREATE TABLE `for_test`.`aaa` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `datetime_is_not_null` DATETIME NOT NULL,
    `datetime_nullable` DATETIME NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO `for_test`.`aaa` (`datetime_is_not_null`) VALUES ('0000-00-00 00:00:00');
INSERT INTO `for_test`.`aaa` (`datetime_is_not_null`, `datetime_nullable`) VALUES ('0000-00-00 00:00:00', '0000-00-00 00:00:00');
INSERT INTO `for_test`.`aaa` (`datetime_is_not_null`, `datetime_nullable`) VALUES (NOW(), NOW());

id	datetime_is_not_null	datetime_nullable
1	0000-00-00 00:00:00	NULL
2	0000-00-00 00:00:00	0000-00-00 00:00:00
3	2017-09-07 19:29:09	2017-09-07 19:29:09

● NULLを許可する列で試してみた結果

特にこれといって異常は見受けられなかった。

WHERE \ マッチする行	NULL (id=1)	0000-00-00 00:00:00 (id=2)	2017-09-07 19:29:09 (id=3)
datetime_nullable IS NULL	○	-	-
datetime_nullable IS NOT NULL	-	○	○
datetime_nullable='0000-00-00 00:00:00'	-	○	-
datetime_nullable='2017-09-07 19:29:09'	-	-	○

● NULL を許可しない列で試してみた結果

'0000-00-00 00:00:00' が IS NULL でもマッチしていることがわかった。
（ IS NULL でも IS NOT NULL でも = '0000-00-00 00:00:00' でもマッチしている）

WHERE \ マッチする行	0000-00-00 00:00:00 (id=1, id=2)	2017-09-07 19:29:09 (id=3)
datetime_is_not_null IS NULL	○	-
datetime_is_not_null IS NOT NULL	○	○
datetime_is_not_null='0000-00-00 00:00:00'	○	-
datetime_is_not_null='2017-09-07 19:29:09'	-	○

上記を参照していただくとわかるように、あえて
NULLを許可しない列に限り '0000-00-00 00:00:00'（ないし '0000-00-00'）は IS NULLにマッチする 仕様となっているようだ。

● WHERE ... = '0000-00-00 00:00:00' について

SELECT 
    *
FROM
    for_test.aaa
WHERE
    datetime_nullable = '0000-00-00 00:00:00'
;

+----+----------------------+---------------------+
| id | datetime_is_not_null | datetime_nullable   |
+----+----------------------+---------------------+
|  2 | 0000-00-00 00:00:00  | 0000-00-00 00:00:00 |
+----+----------------------+---------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '0000-00-00 00:00:00' for column 'datetime_nullable' at row 1
*************************** 2. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '0000-00-00 00:00:00' for column 'datetime_nullable' at row 1
2 rows in set (0.00 sec)

WHERE に '0000-00-00 00:00:00' を使っても NO_ZERO_DATE が有効だとやっぱり怒られてしまう。

● WHERE ... = STR_TO_DATE('0000-00-00 00:00:00', '%Y/%m/%d %H:%i:%s') について

SELECT 
    *
FROM
    for_test.aaa
WHERE
    datetime_nullable = STR_TO_DATE('0000/00/00 00:00:00', '%Y/%m/%d %H:%i:%s')
;

Empty set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '0000/00/00 00:00:00' for function str_to_date
1 row in set (0.00 sec)

なにもマッチしない。どうなっているんだろう。
前半にあった実行結果にあるように、
STR_TO_DATE('0000/00/00 00:00:00', '%Y/%m/%d %H:%i:%s') がNULLとなってしまったのかも。

ちなみに NO_ZERO_DATE が有効でないときは WHERE ... = '0000-00-00 00:00:00' と同様にマッチする。

TIMESTAMP？　しらない。

おわり！