MySQLで「#1292 – Incorrect date value」が出たとき

MySQLでタイトルのエラーが出て困っているということで相談を受けました。

対象のMySQLバージョンは 8.0.25 です。

答えだけ知りたいかたは下方にある「答え」の見出しをご覧ください。

背景

古くから運用しているWebサイトのリニューアル案件でした。

リニューアルに伴ってMySQLを最新版にしようという動きになり、ローカル環境で構築していたときに発生しました。

ちなみに、データベースに蓄積されている内容はほぼそのまま生かす方式です。

原因

MySQLには「mysql_mode」という設定があり、その中の１つに"0000-00-00" を正しい日付として受け入れるかというものがあります。

今回発生していたエラーは次の内容です。

#1292 - Incorrect date value: '0000-00-00' for column 'sample_date' at row 1

#1292 はエラー番号を意味します。
英語で書かれているとおりですが、「"0000-00-00"はダメよ」ってことです。

再現手順 / エラーまでの流れ

説明のため最小限の形にしますが、次のようなテーブルです。

CREATE TABLE `example` (
  `id` int UNSIGNED NOT NULL COMMENT 'ID',
  `sample_date` date DEFAULT NULL COMMENT 'サンプル日'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='サンプル';

ALTER TABLE `example`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `example`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID';

こちらには、sample_date = "0000-00-00" という値がたくさん登録されていました。

一番最初のバージョンではsample_dateは Not NULL でした。
数年前に作られたWEBシステムならあるあるですのでこれ自体はしょうがないと思います。

`sample_date` date NOT NULL COMMENT 'サンプル日'

これを少し前の改修で DEFAULT NULL にして、PHPプログラムのほうも "0000-00-00" と NULL を混在させて動作するような改修を行いました。

バージョンアップを見据えてのことです。

`sample_date` date DEFAULT NULL COMMENT 'サンプル日'

そして、最新版のサーバ環境を用意し、"0000-00-00" で登録されている個所を NULL に一括更新しようとしたときに発生したのが今回のエラーです。

#1292 - Incorrect date value: '0000-00-00' for column 'sample_date' at row 1

なぜエラーになったか

きっかけは「MySQLを最新版に・・・」ということだったんですが、「最新版だからダメ」じゃなく「最新版のデフォルト設定が厳密になった」ということです。

最新版でも「mysql_mode」を以前のバージョンと合わせてやると動くようになると思います。

ただしそう単純なものでもないので、この機会に "0000-00-00" は一掃することをオススメしています。

ちなみに公式のドキュメントを確認したい場合はこちらです。
https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html

sql_modeの変更で解決させる場合

まずはオススメしない方法からです。

現状の確認

次のSQLを実行することで現在どうなっているか確認ができます。

SELECT @@GLOBAL.sql_mode;

phpMyAdminを使っているなら上記をコピペして「SQL」から実行しても良いですが次の方法もあります。

左上のロゴ下の家アイコンをクリック

上部メニュー「その他」から「変数」

含まれている文字「sql_mode」で検索

上図の結果に「NO_ZERO_IN_DATE」と「NO_ZERO_DATE」が見えると思います。
ちなみにこちらはカスタマイズ後のものなので、デフォルトではありません。

解決策

次の設定値が含まれている場合は、それらを取り除くことで"0000-00-00"も受け入れられるようになります。

• NO_ZERO_IN_DATE
• NO_ZERO_DATE
• TRADITIONAL

詳しくは▲のリンク先をご覧ください。

なお、sql_modeの変更での解決はオススメしない方法なので、このあたりとさせて頂きます。

本当の解決策

前置きが長くなってしまいましたが本題です。

みなさんが知りたいのは

UPDATE example SET
    sample_date = NULL
WHERE sample_date = '0000-00-00'

このように "0000-00-00" の値を一括で NULL にしたかったのだと思います。

あ、違いますか？

SELECT
    *
FROM example
WHERE sample_date = '0000-00-00'

このように"0000-00-00" の値を取り出したかったですか？

どちらの場合も

#1292 - Incorrect date value: '0000-00-00' for column 'sample_date' at row 1

このエラーに悩まされますね。

答え

"0000-00-00" じゃなく 0 でした。

SELECT
    *
FROM example
WHERE sample_date = 0

こちらのSQLを実行してみてください。

"0000-00-00" が抽出されていることと思います。

ということで、"0000-00-00" を一括で NULL にするには次のようになります。

UPDATE example SET
    sample_date = NULL
WHERE sample_date = 0

長く運用しているWebサイトのメンテナンス承ります

ということで宣伝です。

弊社ではPHP・MySQL・javascriptを使ったWebサイトを得意としています。
これらは2005年頃から触り続けてきていることもあり得意中の得意です。

大幅なリニューアルではなく、現在の状態を生かしつつ効果のある施策を加えていくのが賢明なやり方です。

特にプログラム開発が必要になるようなカスタマイズは遠慮なくご相談ください。

デザイナーの方、広告業の方、ディレクター専門の方などお互いの得意分野を生かして長いお付き合いの出来る方を募集しています。

お気軽にお問い合わせください。