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に直に書いても設定はできます。

 

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

 

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

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

 

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

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

 

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

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

 

-MYSQL

© 2021 エンジニアてんし君ブログ