033 MySQLのはまりどころ(その2)〜不正データがエラーにならない - EC-One ナレッジセンターレスキューサービスブログ

こんにちは、id:EC-OneのAkiです。

前回のエントリに引き続き、MySQLの小ネタをひとつ。

MySQLに不正な値を入れると...？

Oracle+Windows-31Jで構築したアプリをMySQL+UTF-8に変更していたエンジニアが気付いたこと。

Oracleは不正な値をINSERTやUPDATEで入れようとするとエラーになります。
不正な値とは、たとえば「カラムサイズを超える長さの文字列」等です。

日本語の文字列の必要バイト数は文字コードがWindows-31Jであれば「1文字2バイト」で計算できます。
しかし、UTF-8にするとそれよりも必要バイト数が多くなります。これはUTF-8が日本語一文字に2〜4バイト使用するためです。

ところが、MySQLに明らかにカラムサイズを超える文字列を入れてもエラーになりません。
おかしいなと思ってデータを見てみると、そのデータの文章が不自然に終わってしまっています。
そうです、なんとMySQLはエラーにせずに黙って格納可能バイト数を超える部分をカットしてしまっていたのです。

日付・時刻の場合、不正な値を入れるとやはりエラーにならずに黙って「ゼロ」にしてくれます。
と言うことはたまたまタイミングよく閏秒の時に23時59分60秒という値をMySQLに入れると、それはゼロになってしまうということです。(最近のMySQLは閏秒のときでもNOW()関数は23時59分59秒を返すようになっているようですが)

「何か間違っていたら最後の砦のDBが叱ってくれるはず」と思っていたのが、「間違っているかどうかは全て手前のアプリが自分で確認しなければならない」となるわけです。
まぁ、もちろんアプリでは入力チェックを行うようには作る＆テストはするのですが、「DBに頼る事はできない」となった時点でちょっと負担が増える気はしますね...
しかし、この点だけを理由にMySQLをあきらめる必要はありません！なぜなら、不正な値に対してエラーを発生させる設定があるからです。

不正な値を入れるとエラーにしてくれる「STRICT_ALL_TABLES」設定

マニュアルの「SQLモード」のページにその設定に関する以下の記述があります。

STRICT_ALL_TABLES
すべてのストレージエンジンに対して、Strict Mode を有効にする。無効データは排除の対象になる。
http://dev.mysql.com/doc/refman/5.1/ja/server-sql-mode.html#id3524189

この「STRICT_ALL_TABLES」設定によって、「不正データを水際で止める」事が出来るようになります！
この設定を使用するには、mysqldの起動引数に「--sql-mode="STRICT_ALL_TABLES"」オプションを追加するか、または設定ファイルmy.cnf(Unix)かmy.ini(Windows)に「sql-mode="STRICT_ALL_TABLES"」を追記します。
あー、良かった！

EC-Oneナレッジセンターは、OracleとMySQLのような製品間の差異に関する相談でも親身に受け付けます！