「第18回 MySQL5.7のデフォルトのSQLモードを確認してみる」、「第60回 SQLモードについて[その1]」、「第64回 SQLモードについて[その2]」と、SQLモードについて3回にわたって紹介してきました。今回も引き続きSQLモードについて説明していきます。
SQLモードの設定方法や変更の方法は第60回ですでに説明を行っているので、改めて説明を行いません。
ANSI_QUOTES
MySQLの予約語を識別子(テーブル名やカラム名などを指します)として利用したい場合もあるかと思います。そういった場合に、その文字列を通常は`(バッククオート)記号で囲むことで、予約語ではなく識別子として扱うことができます。
このSQLモードを有効にすると`記号以外にも、"(ダブルクオート)記号でも同様の事ができるようになります。
まず、MySQLの予約語をカラムにしたい場合を考えます。たとえば、select
という名前のTEXT型のカラムを作成する場合を考えます。通常通りに作成を行うと、以下のようにエラーになってしまいます。
続けて、`記号で囲った場合の処理を確認してみます。こちらは以下のようにテーブルが作成されていることがわかります。
現在SQLモードには何も設定がされていない状態で、今度は"記号を使ってテーブルを作成してみようと思います。先ほど作成したテーブルを削除してから、テーブルを作成します。
このように構文エラーとなってしまいました。続いてSQLモードにANSI_QUOTES
を設定して試してみます。
このように、作成することができました。ここで気になるのは、show create table
文の中で"select"
となっている部分でSQLモードが変更となった場合にどうなってしまうのかですが、以下のようにSQLモードを元に戻した場合`記号に置き換わり、問題は起こりません。
このモードの使いどころとしては、他のDBを利用して作成したコードをMySQLに移植したい場合などに、用意したコードを変更せずとも対応できるという点にあります。またこのモードを使用すると、"で囲われた文字列は文字列として使用することが出来ないので注意が必要です。
HIGH_NOT_PRECEDENCE
このモードは過去の一部のMySQLのバージョンとの整合性のために用意されているSQLモードです。名前の通りNOTの演算子の優先順位を入れ替えるために利用されます。MySQLの演算子の優先順位については、公式のドキュメント 12.3.1 「演算子の優先順位」に記載されています。このモードを有効にした場合、!
と同じ優先順位優先順になります。
ここでは例として、select not 1 + 1;
というクエリを考えます。通常のsql_modeであれば、加算が先に演算されてその後にnot演算がされます。そのため、select not (1 + 1);
からselect not 2;
と評価され、結果が0になります。
ところが、このSQLモードを設定すると以下のように結果が変わります。
こちらはselect (not 1) + 1;
として判断され、select 0 + 1;
となり最終的に結果が1になりました。
このように計算の結果が変わってしまうため、このSQLモードを利用する際には注意が必要です。
NO_DIR_IN_CREATE
このSQLモードが設定されていると、CREATE
文で設定をしたDATA DIRECTORY
やINDEX DIRECTORY
を無視することができます。masterとslaveで、違った構成をディレクトリ構成を取りたい場合などに利用できます。
PIPES_AS_CONCAT
このSQLモードは名前の通り、通常OR演算と同等に扱われる||
をCONCAT構文として扱えるようにするモードです。
通常ではOR演算と同等に判断されていますが、続けてSQLモードを有効にした場合を見てみましょう。
このように文字列が結合され、CONCAT関数と同様の結果が得られました。これらは他のDBとの互換のために用意されたモードです。
REAL_AS_FLOAT
このSQLモードは通常はdoubleという型のエイリアスであるREALをfloatに変更します。以下のCREATE TABLE文を使って確認してみましょう。
上記のように、real型で作成したカラムはdouble型として定義されていることがわかります。今度はSQLモードを変えて実行していきます。
こちらはfloat型として定義されていることがわかります。このようにSQLモードでreal型は変更されてしまうので、何かおかしなことがあったら疑ってみると良いかもしれません。
STRICT_ALL_TABLES
このモードは「第18回 MySQL5.7のデフォルトのSQLモードを確認してみる」で説明を行ったSTRICT_TRANS_TABLES
と同様に厳密モードを設定するモードです。STRICT_TRANS_TABLE
ではトランザクションが利用できるストレージエンジンでのみ適用されていたのですが(例外として、MyISAMはトランザクション利用できませんが適用されます)、STRICT_ALL_TABLES
では全てのストレージエンジンに適用されます。しかしながら、当然トランザクションが利用できないテーブルで設定をするとロールバックはできません。そのため、複数行の更新中にエラーが発生した場合に中途半端な適用の状態が発生してしまう可能性があることに注意をしてください。
また、トランザクションが利用できないストレージエンジンでは、STRICT_ALL_TABLES
ではエラーが発生した箇所までしか更新が進みませんが、STRICT_TRANS_TABLE
を使用している場合は警告を出して最後まで更新するといった違いもあることに注意をしましょう。
まとめ
詳細なSQLモードについて今回までで説明を終えました。これらのモードは安全にアプリケーションを作成する際に非常に心強い味方になると思いますが、反面副作用が大きなモードも多いので注意をして適用をする際にはよく検証を行いましょう。