Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
31
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

@rhap

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

もちろん時分秒は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' -

上記を参照していただくとわかるように、あえて
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? しらない。

おわり!

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
31
Help us understand the problem. What are the problem?