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_BY
,STRICT_TRANS_TABLES
,NO_ZERO_IN_DATE
,NO_ZERO_DATE
,ERROR_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_DATE
とSTRICT_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.cnfsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES....//デフォルトはこうなっている ↓ sql_mode='' //書き換え。この場合は全モード削除。
my.cnfをいじった後は再起動してみてください。
再起動後、先程のSQLモード確認コマンドで、想定通りのモードになっていればOKです。
SQLモードについては、DB担当者は把握しておくといいですね。
このあたり、バージョンによってデフォルトの設定も違うみたいなので、バージョンアップ、ダウン時には要注意です...。
てことで今回は以上です。
今日もよきエンジニアライフを。