がるの健忘録

エンジニアでゲーマーで講師で占い師なおいちゃんのブログです。

0000-00-00 と PHP

MySQLの、(少なくとも一部では)悪名高き 0000-00-00 について、は、そこそこブログがあるのですが。
「それを、PHP(plain)とか(PHPの)フレームワークとかでどうやってるんだろ?」というのが、ざっくりググった限りだと案外と無かったので。
調査かてがて、備忘録として。

0000-00-00 について

まずは 0000-00-00 について。
いやまぁググればあちこちで出てくる「MySQLに固有の、奇異なデータ」なのですが。
端的には
・入れ方によって、日付型(DATE、DATETIME、TIMESTAMP)に入る事があるデータ
MySQL以外で 0000-00-00 が入るRDBはない(はず)
・NOT NULL制約が入っているカラムで「0000-00-00」があると、WHERE句の IS NULL、IS NOT NULLの双方で 0000-00-00が引っかかる
てなもの、になります。

http://nippondanji.blogspot.com/2018/05/mysqlzero-date.html
https://soudai.hatenablog.com/entry/2018/05/12/191050
http://sakaik.hateblo.jp/entry/20151227/mysql_date_null

など、言及されている所は少なからずあるか、と思います。
詳しくは、上述のBlogなど見て頂くほうが早いかなぁ、と思います。

少し細かくまとめると。

・カラムにNOT NULL制約がある時
 → 空文字('')をVALUESに与えると、 0000-00-00 になる: modeにSTRICT_TRANS_TABLESがある時はエラーが出る(Incorrect date value: '' for column)
 → NULLをVALUESに与えると、エラーになる(当たり前だ)
・カラムにNOT NULL制約がない時
 → 空文字('')をVALUESに与えると、 0000-00-00 になる: modeにSTRICT_TRANS_TABLESがある時はエラーが出る(Incorrect date value: '' for column)
 → NULLをVALUESに与えると、NULLになる(当たり前だ)

となります。
また
・カラムにNOT NULL制約があり、かつ、DEFAULTが定義されている時
 → 空文字('')をVALUESに与えると、 0000-00-00 になる: modeにSTRICT_TRANS_TABLESがある時はエラーが出る(Incorrect date value: '' for column)
 → NULLをVALUESに与えると、エラーになる(当たり前だ)
 → INSERTの対象カラムからそもそも削除すると、DEFAULTで定義された値になる(当たり前だ)
・カラムにNOT NULL制約がなく、かつ、DEFAULTが定義されている時
 → 空文字('')をVALUESに与えると、 0000-00-00 になる: modeにSTRICT_TRANS_TABLESがある時はエラーが出る(Incorrect date value: '' for column)
 → NULLをVALUESに与えると、NULLになる(DEFAULTの値にはならない)
 → INSERTの対象カラムからそもそも削除すると、DEFAULTで定義された値になる(当たり前だ)

となります。
ここ、後述でちょっと重要になるので、ポイントとして。

フレームワーク無し」で実装すると?

まぁ昨今だととかですかねぇ。古来だとか。
日付を入力してもらう時に、まぁ最終的には文字列で受け取るわけ、なのですが。
これが「必須入力」なら「日付のフォーマットに沿ってるかどうかを判断」とかでいけばよくて……個人的にはstrtotime()使うのが、楽で好みかなぁ。

$t = strtotime($_POST['日付'] ?? '');
if (false === $t) {
    // エラー処理
    return;
}
// else
$date = date('Y-m-d H:i:s', $t);

とかやるとざっくりとフォーマットが整うので、よく使います。
DateTimeとかDateTimeImmutableとかのクラスを使う場合(このチョイスならDateTimeImmutableのほうが好み)、エラー時は「例外を投げてくる」ので、書き方にちょっとだけ注意を払いましょう。

ただ、必須じゃない場合「空文字を許容」する必要がある、と思われるのですが。

$date = $_POST['日付'] ?? '';
if ('' !== $date) {
    $t = strtotime();
    if (false === $t) {
        // エラー処理
        return;
    }
    // else
    $date = date('Y-m-d H:i:s', $t);
}

上述で「パラメタの値を取得して」「SQL作成してINSERTとかUPDATEとか」やると、結局日付系のカラムで「VALUEが空文字なINSERT/UPDATE」が走るので、0000-00-00問題が、回避できないような気がするのですだよ……。

なお

$date = $_POST['日付'] ?? null;

ってやっても変わらない。だって「formには"日付"のnameアトリビュートが存在しているから、空文字が返ってくる事が十分に想起される」から。

PHPフレームワークはどうなってるんだろ?

と言うわけで様々なフレームワークを……試す根性がありませんでしたすみません orz
ので、現状多分「一番メジャーであろう」Laravelで実験。
とりあえずバージョンは…… 6.4.1 。あら。5.8系かと思ったんだけど、バージョン指定しないとこんな先のバージョンになるのか。
まぁ「新しいほうがより機能は洗練されてる」だろうから、あんまり気にしない(雑

すげぇ単純に
・テーブル作って
・form入りのテンプレート作って
・ざっくりとinsert
こんな感じ。

マニュアルのPageにも
https://readouble.com/laravel/5.8/ja/eloquent.html

        $flight = new Flight;
        $flight->name = $request->name;
        $flight->save();

ってあるので、おんなじようにやってみる……エラー。
どうもConvertEmptyStringsToNullというのがお邪魔しくさりやがってくださっているらしく
・ConvertEmptyStringsToNull Middleware によって「空文字ならnullに置換」される
・対象カラムがNOT NULL制約
・なので「NOT NULL制約の所にNULLぶち込んだから嫌がられる」
という単純構造。

一端 app/Http/Kernel.php に修正を入れて、ConvertEmptyStringsToNull をoff(ついでにTrimStringsもoffしたほうがいいんじゃねぇか疑惑があるんだが一端放置)。
「未入力ならそのまま空文字になる」ように修正して再度実行。

……予想通り 0000-00-00 が入りやがりました orz
この辺、Laravelでも特に「なんか対応」はしていないんだなぁ、と、実感。

今までよく引っかかってこなかったなぁ……と思うんだけど、まぁぶち合ったんだからどうにか考えませんとなぁ。

https://gallu.hatenadiary.jp/entry/2019/11/04/160910 で、実装について少し考察をしてみます。

おまけ:実験結果

mysql> show create table date_tests;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| date_tests | CREATE TABLE `date_tests` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`d_test` date NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
+----+------------+
2 rows in set (0.01 sec)

mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into date_tests(d_test) value(null);
ERROR 1048 (23000): Column 'd_test' cannot be null
mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
+----+------------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date ;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into date_tests(d_test) value(null);
Query OK, 1 row affected (0.01 sec)

mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 5 | NULL |
+----+------------+
5 rows in set (0.00 sec)

mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into date_tests(d_test) value('');
ERROR 1292 (22007): Incorrect date value: '' for column 'd_test' at row 1
mysql> insert into date_tests(d_test) value(null);
Query OK, 1 row affected (0.00 sec)

mysql> select id, d_test from date_tests;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 5 | NULL |
| 6 | 0000-00-00 |
| 7 | NULL |
+----+------------+
7 rows in set (0.00 sec)



mysql> select id, d_test from date_tests where d_test is null;
+----+--------+
| id | d_test |
+----+--------+
| 5 | NULL |
| 7 | NULL |
+----+--------+
2 rows in set (0.00 sec)

mysql> select id, d_test from date_tests where d_test is not null;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date NOT NULL;
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)

mysql> select id, d_test from date_tests where d_test is null;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
4 rows in set (0.00 sec)

mysql> select id, d_test from date_tests where d_test is not null;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
+----+------------+
5 rows in set (0.00 sec)

mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date DEFAULT '1970-1-1';
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into date_tests(d_test) value(null);
Query OK, 1 row affected (0.00 sec)

mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | NULL |
+----+------------+
7 rows in set (0.00 sec)

mysql> ALTER TABLE date_tests MODIFY COLUMN `d_test` date NOT NULL DEFAULT '1970-1-1';
Query OK, 7 rows affected, 1 warning (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 1

mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
+----+------------+
7 rows in set (0.01 sec)

mysql> insert into date_tests(d_test) value('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into date_tests(d_test) value(null);
ERROR 1048 (23000): Column 'd_test' cannot be null
mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
| 10 | 0000-00-00 |
+----+------------+
8 rows in set (0.00 sec)

mysql> insert into date_tests(id) value(null);
Query OK, 1 row affected (0.01 sec)

mysql> select id, d_test from date_tests ;
+----+------------+
| id | d_test |
+----+------------+
| 1 | 0000-00-00 |
| 2 | 2019-11-01 |
| 3 | 0000-00-00 |
| 4 | 0000-00-00 |
| 6 | 0000-00-00 |
| 8 | 0000-00-00 |
| 9 | 0000-00-00 |
| 10 | 0000-00-00 |
| 11 | 1970-01-01 |
+----+------------+
9 rows in set (0.01 sec)