Edited at

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

More than 1 year has passed since last update.


■ 結論

● 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 が有効だと、年がゼロでも怒られてしまう。

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

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』と出るだけで気付きづらい。

image01.png


● 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'
-

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_is-null

上記を参照していただくとわかるように、あえて

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? しらない。

おわり!