長い間MySQLを使ってアプリケーションやサービスを提供していると、セキュリティの都合や機能の高性能化によってアップグレードが求められることがあります。アップグレードを行った際にアプリケーションがそのまま動いてくれる場合もあるのですが、SQLモードの設定によって動かなくなってしまうこともあります。
アプリケーションが動かなくなってしまい、仕方がないのでよくわからないけどSQLモードの設定を空にする、なんて事はありませんか? 今回はそんなSQLモードの確認方法や、デフォルトの設定がどのような意味を持っているのかを紹介していきたいと思います。
デモンストレーション環境
この原稿を書いている時点で最新版である5.7.12を第5回 Dockerで複数バージョンのMySQLを開発環境に用意するで作成した環境で実行して確認していきます。
また、今回使用するデータは第2回 MySQLにはじめてのデータを入れてみるで紹介している郵便番号のテーブルを用いて紹介を行います。
MySQL 5.7のデフォルトのSQLモード
それではまず、現在のSQLモードを確認してみましょう。SELECT @@global.sql_mode;
でデータベースに設定されているsql_modeの情報を確認することができます。
実行結果を見てみると、見やすい表のような形にまとめられています。MySQL5.7では7個のSQLモードがデフォルトで設定されています。以下、各sql_modeの設定について確認してみましょう。
各設定に関してもっと詳しく知りたい場合は、MySQLの公式ドキュメントを参照してください。
ONLY_FULL_GROUP_BY
この設定はGROUP BYに関する設定です。もし有効にした場合、GROUP BY句で名前が指定されていない非集約カラムをSELECT、Having条件、Order条件で指定しているクエリを拒否します。
今回の例では、具体的には以下の様なクエリは拒否されてしまいます。
SELECTのリストとprefectureがGROUP BY句で指定がされていないため、実行すると以下のようなエラーが発生してしまいます。
しかし、SQLモードを変更し、ONLY_FULL_GROUP_BYを下のように一度外して実行してみると、以下のように結果が帰ってきてしまいます。
この非集約カラムを設定できる機能は標準SQLでは規定されておらず、MySQL独自の設定となっています。そのため他のRDBMSで実行しようとすると、エラーになります。5.7からこの設定が有効になったので、この独自拡張を使ってクエリを書いている場合は、アップグレードの時点では一旦無効化した場合でも、クエリの書き換えを行った方が良いと思います。
STRICT_TRANS_TABLES
この設定は厳密モードとも呼ばれInsertやUpdateをした値がテーブルの指定に従っていない場合に、SQLの実行を中止します。例えば、old_zipcodeの文字列が5文字以上であった場合INSERTを中止します。
今回例として使用しているzipcodeテーブルのCREATE TABLE文を再掲します。
以上のようにold_zipcodeは5文字以下の長さであると宣言されているため、STRICT_TRANS_TABLESが有効になっている場合は以下のようなクエリは実行できません。
old_zipcodeに6文字を設定した結果、old_zipcodeが長すぎるというエラーが発生しました。
続いてSTRICT_TRANS_TABLESを無効にした場合は以下のようになります。
以上のようにINSERT文が実行できてしまいました。こちら中身がどうなっているのか?と不思議に思われた方も多いと思います。それでは中身を確認して見ましょう。
old_zipcodeの値が123456
から上記の結果のように12345
に切り詰められていることがわかります。このように最悪の場合、自動的に結果が変わってしまうため、できる限りこのオプションを無効にしないほうが良いでしょう。
NO_ZERO_DATE
この設定は '0000-00-00'
という日付の暗黙的デフォルト値を挿入された時の挙動を決めるための設定です。
- この設定が無効な場合は、日付の暗黙的デフォルト値である
'0000-00-00'
を挿入できます。
- この設定だけ有効な場合は警告を出し、
'0000-00-00'
を挿入します。
- この設定と前述のSTRICT_TRANS_TABLESのような厳密モードを指定する設定が有効な場合はエラーとなり挿入がされません。
NO_ZERO_IN_DATE
この設定は '2000-00-01'
や'2000-01-00'
の様な日や月に0が入った値を挿入された時の挙動を決めるための設定です。
- この設定が無効な場合は、日や月が0の場合に暗黙的デフォルト値である
'0000-00-00'
を挿入します。
- この設定だけが有効である場合には警告を出力し
'0000-00-00'
を挿入します。
- この設定と前述のSTRICT_TRANS_TABLESのような厳密モードを指定する設定が有効な場合はエラーとなり挿入がされません。
ERROR_FOR_DIVISION_BY_ZERO
この設定は 0除算(MOD(N, 0)や1/0等)を含むINSERTやUPDATEがあった場合の動作の設定になります。
- この設定が無効な場合はNULLを挿入します。
- この設定だけが有効である場合は警告を出力し、NULLを挿入します。
- この設定と前述のSTRICT_TRANS_TABLESのような厳密モードを指定する設定が有効な場合はエラーとなり挿入がされません。
NO_AUTO_CREATE_USER
この設定は管理用のコマンドの設定なのでアプリケーションには直接影響出ることは少ないと思います。この設定が有効になっていると、以下のようなGRANT文で存在しないユーザに対してGRANTを行った場合にエラーになります。
この設定を外して実行した場合は、以下のようにGRANTを行うときに自動的にユーザを作成します。
サーバの構築を行う際などにはご注意ください。
NO_ENGINE_SUBSTITUTION
この設定は、選択したストレージエンジンがCREATE TABLEやALTER TABLEが無効だった場合や利用できないストレージエンジンだった場合に、エラーを返す設定です。
こちらの設定を無効にすると、利用できないストレージエンジンだった場合はデフォルトに設定されているストレージエンジンが選択されます。
まとめ
今回はMySQL 5.7でデフォルトで適用されているSQLモードを確認しました。もし、アップグレードした際にどうしても動かない場合などは、一時的に設定を無効化してしまうこともあるかもしれません。しかし、どの設定も標準のSQLの仕様に近づけるための設定となっているので、リファクタリングを行ってなるべく追随していけるようにしていきましょう。