SQLモードの設定方法や変更の方法は第60回ですでに説明を行っているので、
ANSI_QUOTES
MySQLの予約語を識別子
このSQLモードを有効にすると`記号以外にも、
まず、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_を設定して試してみます。
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モードが変更となった場合にどうなってしまうのかですが、
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)
このモードの使いどころとしては、
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の演算子の優先順位については、!と同じ優先順位優先順になります。
ここでは例として、select not 1 + 1;というクエリを考えます。通常のsql_select not (1 + 1);からselect not 2;と評価され、
mysql> SET sql_mode = ''; mysql> select not 1 + 1; +-----------+ | not 1 + 1 | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec)
ところが、
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になりました。
このように計算の結果が変わってしまうため、
NO_DIR_IN_CREATE
このSQLモードが設定されていると、CREATE文で設定をしたDATA DIRECTORYやINDEX DIRECTORYを無視することができます。masterとslaveで、
PIPES_AS_CONCAT
このSQLモードは名前の通り、||をCONCAT構文として扱えるようにするモードです。
mysql> SET sql_mode = ''; mysql> SELECT '1' || '1'; +------------+ | '1' || '1' | +------------+ | 1 | +------------+ 1 row in set (0.01 sec)
通常ではOR演算と同等に判断されていますが、
mysql> SET sql_mode = 'PIPES_AS_CONCAT'; mysql> SELECT '1' || '1'; +------------+ | '1' || '1' | +------------+ | 11 | +------------+ 1 row in set (0.00 sec)
このように文字列が結合され、
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)
上記のように、
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
このモードはSTRICT_と同様に厳密モードを設定するモードです。STRICT_ではトランザクションが利用できるストレージエンジンでのみ適用されていたのですがSTRICT_では全てのストレージエンジンに適用されます。しかしながら、
また、STRICT_ではエラーが発生した箇所までしか更新が進みませんが、STRICT_を使用している場合は警告を出して最後まで更新するといった違いもあることに注意をしましょう。
まとめ
詳細なSQLモードについて今回までで説明を終えました。これらのモードは安全にアプリケーションを作成する際に非常に心強い味方になると思いますが、