MySQL の datetime の無難な最小値は ‘0001-01-01 00:00:00’ だった

まとめ

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE である MySQL の話
カラムに挿入できる datetime の最小値は ‘0000-01-01 00:00:00’
STR_TO_DATE 関数で扱える最小値は ‘0001-01-01 00:00:00’
したがって、 NMySQL の datetime で実際に使うときに無難な最小値は ‘0001-01-01 00:00:00’

環境

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15    |
+-----------+
1 row in set (0.00 sec)

mysql>
mysql> 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_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.02 sec)

mysql>

INSERT 最小値の検証

MySQLで型の最小値・最大値を超える値を設定してみる – yk5656 diary を参考にしました。

create table sample (c_datetime datetime;) で検証用テーブルを作り、レコードを挿入して調べました。結果、 0000-01-01 00:00:00 が最小値となりました。

mysql> create table sample (c_datetime datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc sample;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| c_datetime | datetime | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql>
mysql> insert into sample (c_datetime) values ('0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'c_datetime' at row 1
mysql> insert into sample (c_datetime) values ('0000-00-00 00:00:01');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:01' for column 'c_datetime' at row 1
mysql> insert into sample (c_datetime) values ('0000-00-00 00:01:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:01:00' for column 'c_datetime' at row 1
mysql> insert into sample (c_datetime) values ('0000-00-00 01:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 01:00:00' for column 'c_datetime' at row 1
mysql> insert into sample (c_datetime) values ('0000-00-01 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-01 00:00:00' for column 'c_datetime' at row 1
mysql> insert into sample (c_datetime) values ('0000-01-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-01-00 00:00:00' for column 'c_datetime' at row 1
mysql> insert into sample (c_datetime) values ('0000-01-01 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from sample;
+---------------------+
| c_datetime          |
+---------------------+
| 0000-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table sample;
Query OK, 0 rows affected (0.02 sec)

mysql>

STR_TO_DATE で変換できる最小値の検証

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

と同じ、 0000-01-01 は NULL で 0001-01-01 は OK という結果となりました。また、もう一つ調べる項目を増やしました。すると、 0000-01-01 00:00:00 は NULL で 0001-01-01 00:00:00 は OK という結果となりました。

mysql> 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('0000/01/01 00:00:00', '%Y/%m/%d %H:%i:%s') AS '0000/01/01 00:00:00',
    ->     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 | 0000/01/01 00:00:00 | 0001/01/01 00:00:00 |
+------------+------------+------------+------------+---------------------+---------------------+
| NULL       | NULL       | NULL       | 0001-01-01 | NULL                | 0001-01-01 00:00:00 |
+------------+------------+------------+------------+---------------------+---------------------+
1 row in set, 4 warnings (0.01 sec)

mysql>
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
*************************** 4. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '0000/01/01 00:00:00' for function str_to_date
4 rows in set (0.00 sec)

mysql>

テーブル作成時のデフォルト値の検証

datetime のデフォルト値で、 0000-01-01 00:00:00 は OK でした。 0001-01-01 00:00:00 ももちろん OK でした。

mysql> create table sample (id INT, c_datetime datetime DEFAULT '0000-01-01 00:00:00');
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sample (id) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from sample;
+------+---------------------+
| id   | c_datetime          |
+------+---------------------+
|    1 | 0000-01-01 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> drop table sample;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> create table sample (id INT, c_datetime datetime DEFAULT '0001-01-01 00:00:00');
Query OK, 0 rows affected (0.02 sec)

mysql> insert into sample (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from sample;
+------+---------------------+
| id   | c_datetime          |
+------+---------------------+
|    1 | 0001-01-01 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> drop table sample;
Query OK, 0 rows affected (0.02 sec)

mysql>

おわりに

次の参考ページとは違った結果となり、びっくりしました! MySQL のバージョンか、 sql_mode が異なっていたのだと思います。

MySQLで型の最小値・最大値を超える値を設定してみる – yk5656 diary

次のページ、

MySQLの0000-00-00 00:00:00は使ってはならない – そーだいなるらくがき帳

にて

せめてNULLだったり 0001-01-01 00:00:00 を使いましょう。って7年前の俺に言いたい。

とありましたが、結論として 0001-01-01 00:00:00 が無難でベターな値と納得できました♪

以上です。

まとめ

環境

INSERT 最小値の検証

STR_TO_DATE で変換できる最小値の検証

テーブル作成時のデフォルト値の検証

おわりに

関連

コメントを残すコメントをキャンセル