MySQL道普請便り

第67回SQLモードについて[その3]

「第18回 MySQL5.7のデフォルトのSQLモードを確認してみる」「第60回 SQLモードについて[その1⁠⁠」「第64回 SQLモードについて[その2⁠⁠」と、SQLモードについて3回にわたって紹介してきました。今回も引き続きSQLモードについて説明していきます。

SQLモードの設定方法や変更の方法は第60回ですでに説明を行っているので、改めて説明を行いません。

ANSI_QUOTES

MySQLの予約語を識別子(テーブル名やカラム名などを指します)として利用したい場合もあるかと思います。そういった場合に、その文字列を通常は`(バッククオート)記号で囲むことで、予約語ではなく識別子として扱うことができます。

このSQLモードを有効にすると`記号以外にも、"(ダブルクオート)記号でも同様の事ができるようになります。

まず、MySQLの予約語をカラムにしたい場合を考えます。たとえば、selectという名前のTEXT型のカラムを作成する場合を考えます。通常通りに作成を行うと、以下のようにエラーになってしまいます。

mysql> SET SESSION sql_mode='';
mysql> create table test (select TEXT);
ERROR 1054 (42S22): Unknown column 'TEXT' in 'field list'

続けて、`記号で囲った場合の処理を確認してみます。こちらは以下のようにテーブルが作成されていることがわかります。

mysql> create table test (`select` TEXT);

mysql> show create table test;
+-------+----------------------------------------------------------------------------+
| Table | Create Table                                                               |
+-------+----------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `select` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

現在SQLモードには何も設定がされていない状態で、今度は"記号を使ってテーブルを作成してみようと思います。先ほど作成したテーブルを削除してから、テーブルを作成します。

mysql> drop table test;

mysql> create table test ("select" TEXT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"select" TEXT)' at line 1

このように構文エラーとなってしまいました。続いてSQLモードにANSI_QUOTESを設定して試してみます。

mysql> SET SESSION sql_mode='ANSI_QUOTES';

mysql> create table test ("select" TEXT);
Query OK, 0 rows affected (0.04 sec)

mysql> show create table test;
+-------+----------------------------------------------------------------------------+
| Table | Create Table                                                               |
+-------+----------------------------------------------------------------------------+
| test  | CREATE TABLE "test" (
  "select" text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

このように、作成することができました。ここで気になるのは、show create table文の中で"select"となっている部分でSQLモードが変更となった場合にどうなってしまうのかですが、以下のようにSQLモードを元に戻した場合`記号に置き換わり、問題は起こりません。

mysql> SET SESSION sql_mode='';

mysql> show create table test;
+-------+----------------------------------------------------------------------------+
| Table | Create Table                                                               |
+-------+----------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `select` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

このモードの使いどころとしては、他のDBを利用して作成したコードをMySQLに移植したい場合などに、用意したコードを変更せずとも対応できるという点にあります。またこのモードを使用すると、"で囲われた文字列は文字列として使用することが出来ないので注意が必要です。

mysql> SET SESSION sql_mode='';

mysql> select "test";
+------+
| test |
+------+
| test |
+------+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode='ANSI_QUOTES';

mysql> select "test";
ERROR 1054 (42S22): Unknown column 'test' in 'field list'

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になります。

mysql> SET sql_mode = '';

mysql> select not 1 + 1;
+-----------+
| not 1 + 1 |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

ところが、このSQLモードを設定すると以下のように結果が変わります。

mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';

mysql> select not 1 + 1;
+-----------+
| not 1 + 1 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

こちらはselect (not 1) + 1;として判断され、select 0 + 1;となり最終的に結果が1になりました。

このように計算の結果が変わってしまうため、このSQLモードを利用する際には注意が必要です。

NO_DIR_IN_CREATE

このSQLモードが設定されていると、CREATE文で設定をしたDATA DIRECTORYINDEX DIRECTORYを無視することができます。masterとslaveで、違った構成をディレクトリ構成を取りたい場合などに利用できます。

PIPES_AS_CONCAT

このSQLモードは名前の通り、通常OR演算と同等に扱われる||をCONCAT構文として扱えるようにするモードです。

mysql> SET sql_mode = '';

mysql> SELECT '1' || '1';
+------------+
| '1' || '1' |
+------------+
|          1 |
+------------+
1 row in set (0.01 sec)

通常ではOR演算と同等に判断されていますが、続けてSQLモードを有効にした場合を見てみましょう。

mysql> SET sql_mode = 'PIPES_AS_CONCAT';

mysql> SELECT '1' || '1';
+------------+
| '1' || '1' |
+------------+
| 11         |
+------------+
1 row in set (0.00 sec)

このように文字列が結合され、CONCAT関数と同様の結果が得られました。これらは他のDBとの互換のために用意されたモードです。

REAL_AS_FLOAT

このSQLモードは通常はdoubleという型のエイリアスであるREALをfloatに変更します。以下のCREATE TABLE文を使って確認してみましょう。

mysql> SET sql_mode = '';
mysql> create table test2(num real);

mysql> show create table test2;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table                                                                            |
+-------+-----------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `num` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

上記のように、real型で作成したカラムはdouble型として定義されていることがわかります。今度はSQLモードを変えて実行していきます。

mysql> drop table test2;
mysql> SET sql_mode = 'REAL_AS_FLOAT';
mysql> create table test2(num real);

mysql> show create table test2;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `num` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

こちらは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モードについて今回までで説明を終えました。これらのモードは安全にアプリケーションを作成する際に非常に心強い味方になると思いますが、反面副作用が大きなモードも多いので注意をして適用をする際にはよく検証を行いましょう。

おすすめ記事

記事・ニュース一覧