Help us understand the problem. What is going on with this article?

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

おわり!

rhap
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした