Go to Qiita Advent Calendar 2024 Top
LoginSignup
49
35

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@rhap

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

Last updated at Posted at 2017-09-07

■ 結論

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

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

おわり！

49
35
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
49
35

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?