MYSQL

MySQLでDATETIMEのデフォルト値変更時にSQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default valueエラーが出たときの対処法

拒否 no

 

MySQLでTIMESTAMP型や、DATETIME型のカラムにデフォルト値を設定しようとすると「SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'カラム名'」というエラーが出るときがあります。

今回はこのような状態の解決方法を紹介していきます。

 

【前提】

・MySQL8.0

 

MySQLでDATETIMEのデフォルト値変更時に Invalid default valueエラーが出たときの対処法

 

MySQLでTIMESTAMP型や、DATETIME型のデフォルト値を変更しようとした時に起きるInvalid default valueエラーの解決方法は以下の2通りです。

 

・その①:入れることができる値を入れる

・その②:SQLモードを変更する

 

それぞれの対処法について解説します。

 

①TIMESTAMPとDATETIMEに入れることができる値にしましょう

 

1つ目の解決方法は「TIMESTAMPやDATETIMEに入れることができる値にする」という方法です。

特に問題ない場合は、こちらをオススメします。

 

Invalid default valueエラーが出ている理由として「適した値が入っていない」ということが考えられるので、TIMESTAMPやDATETIMEに適した値を入れてあげる感じですね。

 

公式ドキュメントによると、入れることにできる値は以下のように決まっています。

DATE 型は、日付部分を含むが時間部分は含まない値に使用されます。 MySQL は、DATE 値を'YYYY-MM-DD'形式で取得して表示します。 サポートしている範囲は '1000-01-01' から '9999-12-31' です。

DATETIME 型は、日付と時間の両方の部分を含む値に使用されます。 MySQL は、DATETIME 値を'YYYY-MM-DD hh:mm:ss'形式で取得して表示します。 サポートしている範囲は '1000-01-01 00:00:00' から '9999-12-31 23:59:59' です。

 

あなたが入れようとしてい日付はおかしくなっていませんか?

それか、あなたは「'0000-00-00 00:00:00'」とか入れようとしてるんじゃないですかね?

後述しますが、その場合デフォルトのSQLモードだと失敗してしまいます。

 

もう一度、適切な値を設定して再度クエリを実行してみてください。

 

②SQLモードを変更する

 

2つ目の手段が、SQLモードを変更することです。

実は、デフォルトのSQLモードだと、以下のように、いくつかの規制がかかっています。

(モードという言葉で定義されています)

 

MySQL 8.0 のデフォルトの SQL モードには、次のモードが含まれます: ONLY_FULL_GROUP_BYSTRICT_TRANS_TABLESNO_ZERO_IN_DATENO_ZERO_DATEERROR_FOR_DIVISION_BY_ZERO および NO_ENGINE_SUBSTITUTION

公式ドキュメントより

 

このうち、「NO_ZERO_DATE」と「STRICT_TRANS_TABLES」が今回のInvalid default valueエラーの原因になっている可能性も高いです。

結論から話すと、これらのモードのどちらかが有効になっていると、「0000-00-00」は許可されません。

 

NO_ZERO_DATE

有効になっていると、サーバーが '0000-00-00' を許可しません。

 

STRICT_TRANS_TABLES

STRICTモード(厳密モード)を有効にします。

有効になっていると、明確にignoreで指定しない限り、 '0000-00-00' を許可しません。

 

見てわかるように、「0000-00-00」の規制は両者が関わってきます。

つまり、どちらかをSQLモードから削除したとしても、どちらかが残っていると挿入はできないのです。

このあたりの正確な内容は公式ドキュメントのサーバーSQLモードでも解説されています。

(実際に、NO_ZERO_DATEについては「この影響は、厳密 SQL モードが有効かどうかにも依存します。」と記載されています。)

 

ちなみに、NO_ZERO_DATEを削除しつつ、STRICTモード(厳密モード)でignoreで指定すれば0000-00-00でも挿入自体はできます。

ただし、警告はでるので、あまりオススメはできません。

 

✔ STRICTモードを解除するのはオススメしません

 

中には「そもそもSTRICTモードも消してしまえば良いのでは?」と思う方もいるかもです。

しかし、それはオススメできません。

 

STRICTモードを解除すると、制約の1部が意味をなさなくなります。

例えば、特定のカラムにvarchar(5)という制約をかけたとします。

STRICTモードを解除すると、この制約が意味をなさなくなります。

具体的には7文字入れると、エラーを返さないで、都合よくvarchar(5)を満たすようにデータが入ってしまうのです。

(STRICTモードならエラーを返す)

このように、STRICTモードを廃止すると、想定していないデータが入り込むなど、バグの原因となる可能性が生まれやすくなってしまうのです。

 

整理すると、以下のような感じですかね。

 

NO_ZERO_DATESTRICT_TRANS_TABLESを削除すれば0を意味するデータも挿入可能

STRICT_TRANS_TABLESを削除するとバグの温床を生みやすい

・よって無理にSTRICT_TRANS_TABLESを削除することは避けるべき

・ちなみにNO_ZERO_DATEを削除しつつ、STRICTモード(厳密モード)でignoreで指定すれば0000-00-00でも挿入自体はできる

 

僕の場合は、無理に「0000-00-00」を入れないで「1000-01-01 00:00:00」とか入れちゃいます。

自分の場合はnullを極力入れたくないのが目的だったので、まぁいいかなという感じです。

 

補足:SQLモードの確認、設定方法

 

ということで、Invalid default valueエラーの解決方法でした。

参考になれば幸いです。

最後に、補足としてSQLモードの確認コマンド、設定コマンドを紹介しておきます。

 

【確認コマンド】

SELECT @@global.sql_mode;

 

【設定コマンド】

SET sql_mode = '設定したいモード';
//例  SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

 

ちなみに、my.cnfに直に書いても設定はできます。

 

my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES....//デフォルトはこうなっている ↓ sql_mode='' //書き換え。この場合は全モード削除。

 

my.cnfをいじった後は再起動してみてください。

再起動後、先程のSQLモード確認コマンドで、想定通りのモードになっていればOKです。

 

SQLモードについては、DB担当者は把握しておくといいですね。

このあたり、バージョンによってデフォルトの設定も違うみたいなので、バージョンアップ、ダウン時には要注意です...。

 

てことで今回は以上です。

今日もよきエンジニアライフを。

 

プログラミングスクールに関する情報

【無料あり】プログラミングスクールおすすめランキング【WEBエンジニアになりたい方向け】と書かれた画像
【無料あり】プログラミングスクールおすすめランキング【WEBエンジニアになりたい方向け】

  あなたプログラミングスクールに通いたいけどたくさんありすぎてどこがいいのかわかりません。   最近はエンジニアブームということもありプログラミングスクールが増えました。 一方でたくさんありすぎてど ...

続きを見る

返金保証ありのプログラミングスクールランキング【エンジニアが選びました】と表示されたパソコンの画面
【転職保証つき】返金保証ありのプログラミングスクールランキング【エンジニアが選びました】

  あなた返金保証がついているオススメのプログラミングスクールってありますか?   結論から話すと、返金保証つきのプログラミングスクールランキングは次の通りです。   1位:DMMWEBキャンプ  【 ...

続きを見る

wordpress
【無料あり】WEB制作を学べるオンラインスクールランキング【副業で稼ぎたい方向け】

  あなたWeb制作の副業で稼ぎたいけど、どのスクールで学ぶのが良いの? スクール選びで失敗はしたくないです。   ↑このような疑問や不安を解決するべく、現役のWebエンジニアがWEB制作を学べるスク ...

続きを見る

フリーランス
無料あり:フリーランスを目指せるプログラミングスクールランキング【体験をもとに徹底分析しました】

  あなたフリーランスエンジニアを目指すことができるプログラミングスクールってありますか?   最近では、高年収や働きやすさの魅力からフリーランスエンジニアを目指す方も増えてきました。 結論から話すと ...

続きを見る

WEB系自社開発企業にいけるプログラミングスクールランキング【現役エンジニアおすすめ】と書かれた画像
WEB系自社開発企業にいけるプログラミングスクールランキング【現役エンジニアおすすめ】

  あなたWEB系の自社開発企業に就職したいです。 オススメのスクールを教えて下さい。   結論から話すと、WEB系自社開発に行きやすいスクールのランキングは以下の通り。   第1位:テックアカデミー ...

続きを見る

-MYSQL

S