MySQL日付がゼロの処理について

前言:
前の記事では、日付と時刻フィールドのクエリー方法について説明しましたが、最近、日付値がゼロの問題に遭遇しました.知ってたとsql_modeパラメータの設定については特に詳しくはありませんが、この記事ではMySQLが日付値がゼロの問題をどのように処理するかを探ります.
1.問題の説明
ここで私たちが言う日付がゼロ値とは、年、月、日がゼロ、すなわち「000-00-00」を意味します.明らかに、これは合法的ではない日付値ですが、設計の問題や履歴に問題が残っているため、データベースに類似の日付値がゼロのデータがある場合があります.デフォルトでは、ゼロ値の日付を挿入するとエラーが発生します.パラメータsql_を変更できます.modeモードでこの問題を回避します.デフォルトでゼロを挿入した場合を次に示します.

#        
CREATE TABLE `t_zerodate` (
  `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '    ',
  `year_col` year DEFAULT NULL COMMENT ' ',
  `date_col` date DEFAULT NULL COMMENT '  ',
  `dt_col` datetime DEFAULT NULL COMMENT 'datetime  ',
  `ts_col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp  ',
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='zerodate';

#   sql_mode  
mysql> select @@sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                         |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+

#      、 、      
#    0000     
mysql> insert into t_zerodate (year_col) values (0000);
Query OK, 1 row affected (0.02 sec)

#  、            
mysql> insert into t_zerodate (date_col) values ('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-01-00'); 
ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-00-01');
ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-01-01'); 
Query OK, 1 row affected (0.02 sec)

2.sql_mode変更テスト
sql_についてmode、もとは1篇の文章を書いたことがあって、sql_modeは複数の変数の異なる組合せ、異なるsql_をサポートするmodeは、サービス側がサポートするSQL構文およびデータ検証ルールに影響します.このうちNO_ZERO_IN_DATE、NO_ZERO_DATEの2つの変数は、MySQLの日付ゼロ値の処理に影響します.上記のテストでは、厳密なモードでsql_modeにNO_を含むZERO_IN_DATE,NO_ZERO_DATEの2つの変数の場合、月と日がゼロでない場合に挿入できます.
一見、NO_ZERO_IN_DATEとNO_ZERO_DATEの2つの変数は似ていますが、役割は何が違いますか?次に、この2つの変数の役割を示し、具体的なテストを行います.NO_ZERO_DATEモードは、サービス側が'000-00-00'を有効日として許可するかどうかに影響します.その効果はsql_にも依存しますmodeが厳格モードを有効にしているかどうか.

このモードが有効でない場合、「0000-00-00」は挿入を許可し、警告は発生しません.

このモードのみがオンの場合、'0000-00-00'は挿入を許可しますが、警告が発生します.

このモードと厳格モードが有効になっている場合、'0000-00-00'は不正とみなされ、挿入にもエラーが発生します.IGNOREが同時に装着されていない限り、INSERT IGNOREおよびUPDATE IGNOREの場合、'0000-00-00'は挿入は許可されますが、警告が発生します.

NO_ZERO_IN_DATEモードは、サービス側が年部分がゼロではないが月または日部分が0の日付を挿入することを許可するかどうかに影響します.(例えば'2010-00-01'または'2010-01-00'ですが、日付'000-00-00'には影響しません)も、sql_によって異なります.modeが厳格モードを有効にしているかどうか.

このモードが有効になっていない場合、部分的にゼロの日付を挿入でき、警告は発生しません.

このモードのみが有効になっている場合は、ゼロ値の日付を'000-00-00'に挿入し、警告を生成します.

このモードと厳格モードが有効になっている場合、IGNOREが同時に指定しない限り、ゼロの日付を挿入することはできません.INSERT IGNOREおよびUPDATE IGNOREの場合、このゼロ値の日付を'000-00-00'に挿入し、警告を生成します.

また、公式文書では、NO_ZERO_DATEとNO_ZERO_IN_DATEは厳格モードの一部ではありませんが、厳格モードと組み合わせて使用し、厳格モードが有効でない場合NO_ZERO_DATEまたはNO_ZERO_IN_DATEは警告を生成し、逆に(sql_modeにはSTRICT_TRANS_TABLESが含まれており、一般的には厳格モードが有効と考えられる).
次に、厳密モードで2つの変数をそれぞれ有効または無効にする効果をテストします.

1.          NO_ZERO_DATE、NO_ZERO_IN_DATE

#        ，       、            
#     insert ignore into
mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t_zerodate;
Query OK, 0 rows affected (0.07 sec)

mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert ignore into t_zerodate (date_col) values ('2010-00-00');    
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert ignore into t_zerodate (date_col) values ('2010-01-00'); 
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert ignore into t_zerodate (date_col) values ('2010-00-01');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select date_col from t_zerodate;
+------------+
| date_col   |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+

#   ：
       、            ；
  INSERT IGNORE     ，      ，          '0000-00-00'。

2.         NO_ZERO_DATE

mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> truncate table t_zerodate;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t_zerodate (date_col) values ('0000-00-00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_zerodate (date_col) values ('2010-00-01');
ERROR 1292 (22007): Incorrect date value: '2010-00-01' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('2010-01-00'); 
ERROR 1292 (22007): Incorrect date value: '2010-01-00' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-00-01');
ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('0000-01-00');
ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1

#   ：
'0000-00-00'      ，   、           ；
  INSERT IGNORE     ，      ，          '0000-00-00'。

3.         NO_ZERO_IN_DATE

mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> truncate table t_zerodate;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t_zerodate (date_col) values ('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1
mysql> insert into t_zerodate (date_col) values ('2010-00-00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_zerodate (date_col) values ('2010-00-01');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_zerodate (date_col) values ('2010-01-00');
Query OK, 1 row affected (0.00 sec)

mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select date_col from t_zerodate;
+------------+
| date_col   |
+------------+
| 2010-00-00 |
| 2010-00-01 |
| 2010-01-00 |
| 0000-00-00 |
+------------+

#   ：
'0000-00-00'    ，   、            ；
  INSERT IGNORE     '0000-00-00'，      。

4.      NO_ZERO_DATE NO_ZERO_IN_DATE    

mysql> set session sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> truncate table t_zerodate;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t_zerodate (date_col) values ('0000-00-00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_zerodate (date_col) values ('2010-00-00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_zerodate (date_col) values ('2010-00-01');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_zerodate (date_col) values ('2010-01-00');
Query OK, 1 row affected (0.01 sec)

#   ：
'0000-00-00'    、             。

3.結論と提案
簡単にまとめると、NO_ZERO_DATEモードは'0000-00-00'日付の挿入に影響し、NO_ZERO_IN_DATEモードは、「0000-00-00」以外の月、日がゼロの日付の挿入に影響します.また、いずれのモードにおいても、YEARタイプでは0000挿入が許可されており、この2つの変数は、DATE、DATETIME、TIMESTAMPの3つのフィールドタイプにおける日付部分がゼロの処理に影響を与える.
この2つのモデルを有効にするかどうかは、ビジネスニーズに依存します.ビジネスにゼロ値の日付を挿入する必要がある場合は、sql_を選択します.modeにNO_を含まないZERO_DATEとNO_ZERO_IN_例えば、あるフィールドはDATEタイプに設定され、空ではなく、デフォルト値は'000-00-00'に設定される必要があります.一般的に、NO_ZERO_DATEとNO_ZERO_IN_DATEは、ゼロ日付値を挿入する必要があれば両方を除去することができ、このような必要がなければ両方を保持することができることを提案しています.ここで注意すると、公式ドキュメントでは、この2つの変数は将来のバージョンでは独立した変数として使用されなくなるため、公式では推奨されません.
まとめ:
こんなにたくさん書いて、どれだけ真剣に読んだか分かりませんが、実はこの文章の話は簡単です.もしあなたがこのような問題に遭遇したことがあるならば、更にこの文章を見てもっと深く理解することができて、このような問題に遭遇したことがない小さい仲間、この文章はあなたにMySQLがゼロ値の日付に対して異なる処理があることを知ってほしいです.