【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 のデフォルト値は以下の通り。

```sql
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'登録時にエラーが出た昔話。](http://qiita.com/tentatsu/items/fd2177777412ebc2f6b9)

上記を参考にしつつまとめると、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 であったらＯＫ。

```sql
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](https://qiita-image-store.s3.amazonaws.com/0/112838/d1410c4d-9abe-7ae3-8eb1-ce54e0875211.png)

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

```sql:
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'` について

```sql:
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')` について

```sql:
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)
```

なにもマッチしない。どうなっているんだろう。
[前半にあった実行結果](http://qiita.com/rhap/items/26b3e796c68a2adaccf5#-no_zero_date-%E3%81%8C%E6%9C%89%E5%8A%B9%E3%81%A0%E3%81%A8%E5%B9%B4%E3%81%8C%E3%82%BC%E3%83%AD%E3%81%A7%E3%82%82%E6%80%92%E3%82%89%E3%82%8C%E3%81%A6%E3%81%97%E3%81%BE%E3%81%86)にあるように、
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？　しらない。

おわり！