NO_ZERO_DATEモード、サーバーが '0000-00-00' を有効な日付として許可するかどうか影響チェック

# サーバー SQL モード
MySQLサーバーsql_mode使い方によって正しく設定すれば、 データベースにゴミデータ入れない様に設定できます。例：DATETIME又はDATEカラムに「0000-00-00 00:00:00」か「0000-00-00」を登録又は更新できない様にできます。

存在しない日付を登録と更新できるか、できないか確認しましょう！
### 準備
``` test_order_tableを作成する
CREATE TABLE `test_order_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `insert_date` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
```
### sql_modeを確認
 - show VARIABLES LIKE 'sql_mode';↓

|Variable_name | Value |
|---|---|---|
|sql_mode| |

- show VARIABLES LIKE 'innodb_strict_mode';↓

|Variable_name|	Value|
|---|---|---|
|innodb_strict_mode	|OFF|
innodbのモードも色々設定できます。例：not strict mode ,ALLOW_INVALID_DATES , -- invalid date :0000-00-00
今回sql_modeの、STRICT_ALL_TABLESとNO_ZERO_DATEのみを確認します。

test_order_tableを作成できたら以下を実行する、存在しない日付DBに登録できました。次に書いてあるupdate文も実行する、念の為update実行する前「0000-00-00 00:00:00」の日付now()に更新する。

```実行できる
insert into test_order_table(id, insert_date, updated_at)
values(1, '0000-00-00 00:00:00', now());

update test_order_table set insert_date = now() where id = 1;

-- update文でも0000の日付更新できた、now()->0000を確認
update test_order_table
set insert_date = '0000-00-00 00:00:00'
where id = 1;
```


### sql_mode少し厳しい設定
-- strict mode
show VARIABLES LIKE 'sql_mode';
SET sql_mode = 'STRICT_ALL_TABLES';
Mysqlの仕様に書いてるとおり、動くか先ほどと同じ順番でクエリ実行して、確認しましょう。
「すべてのストレージエンジンについて厳密モードを有効にします。無効なデータ値は拒否されます。」

``` 
-insert文実行できた
insert into test_order_table(id, insert_date, updated_at)
values(2, '0000-00-00 00:00:00', now());
-- insert_date →now()に更新
update test_order_table set insert_date = now() where id = 2;

-- update文も実行できた
update test_order_table
set insert_date = '0000-00-00 00:00:00'
where id = 2;
```


### sql_modeもう少し厳しい設定
仕様通りに設定して、確認する
![NO_ZERO_DATE.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/475245/7223ea6d-f7f8-25b7-4108-b25a91f294b2.png)

- show VARIABLES LIKE 'sql_mode';↓

|Variable_name|	Value|
|---|---|---|
|sql_mode	|STRICT_ALL_TABLES|

- SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE';
- show VARIABLES LIKE 'sql_mode';

|Variable_name	|Value|
|---|---|---|
|sql_mode	|STRICT_ALL_TABLES,NO_ZERO_DATE|
先ほどと同じ順番でクエリを実行しましょう、0000日付でクエリ実行できないはずです。

```
-- 実行できないです
insert into test_order_table(id, insert_date, updated_at)
values(3, '0000-00-00 00:00:00', now());
エラーが表示される：
Incorrect datetime value: '0000-00-00 00:00:00' for column 'insert_date' at row 1

-- 正し日付でデータをinsertする
insert into test_order_table(id, insert_date, updated_at)
values(3, '2021-06-07 12:00:00', now());

-- 0000の日付でupdateする
update test_order_table set insert_date = now() where id = 3;

update test_order_table
set insert_date = '0000-00-00 00:00:00'
where id = 3;
エラーが表示される：
Incorrect datetime value: '0000-00-00 00:00:00' for column 'insert_date' at row 1
```
![zero_date_error.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/475245/2833bad4-fc72-50bf-9d31-ae48afec7df6.png)

--------------------------------------
##### ・興味あればこちらも試してください！
```
Select @@version;
結果：
@@version
5.6.24-log
```

```
-- USER、DBにtest_order_tableテーブル作成してます
select engine 
from   information_schema.tables 
where  table_schema = 'USER'
   and table_name = 'test_order_table' ;
結果：
engine
InnoDB
```

```  
SHOW TABLE STATUS FROM `USER`;
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
test_order_table	InnoDB	10	Compact	5	3276	16384	0	0	0	6	2021-07-06 13:07:25	NULL	NULL	utf8_general_ci	NULL		
```
   
- GLOBAL 変数を設定するには SUPER 権限が必要で、この設定はその時点以降に接続するすべてのクライアントの動作に影響します。SESSION 変数を設定すると、現在のクライアントにのみ影響します。すべてのクライアントは、自分のセッションの sql_mode 値をいつでも変更できます。

```
SELECT @@GLOBAL.sql_mode;
結果：
@@GLOBAL.sql_mode
空
```

```
SELECT @@SESSION.sql_mode;
結果：
@@SESSION.sql_mode
STRICT_ALL_TABLES,NO_ZERO_DATE
```

参考資料：
・[サーバー SQL モード](https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_strict_trans_tables)
・[MySQLがゆるふわなのはどう考えてもSQLモードが悪い！](https://speakerdeck.com/leko/mysqlkayuruhuwanafalsehatoukao-etemosqlmotokae-i?slide=14
)