4.2.6 SQL モード

4.2.6. SQL モード

MySQL サーバは様々な MySQL モードで動作し、モードをクライアント毎に別々に設定できます。この機能により、各アプリケーションがそれぞれの要件に応じてサーバのオペレーティングモードを指定することができるようになります。

MySQL での SQL モードに関する FAQ は、項A.3. 「MySQL 5.1 FAQ — Server SQL Mode」を参照してください。

モードとは、どの SQL シンタックスを MySQL がサポートし、どのようなデータバリデーションチェックを実行するべきかを定義するものです。これにいより、異なる環境で MySQL を使用したり、MySQL を他のデータベースサーバと併用したりするのが容易になります。

デフォルトの SQL モードを指定するには、--sql-mode="modes" オプションで mysqld を立ち上げます。または、Unix では my.cnf に、Window では my.iniに、sql-mode="modes" を記述します。modes とは、カンマ (「,」) で区切られた各モードのリストです。デフォルトは空の状態で、モード設定がないことを意味します。明示したい場合は、modes の値は空にできます。それには、コマンドラインで --sql-mode="" オプションを使用するか、あるいは、Unix では、my.cnf の、Windows では my.ini の sql-mode="" オプションを使用します。

実行中に SQLモードを変更することもできます。それには、SET [GLOBAL|SESSION] sql_mode='modes' 文で sql_mode の値を指定します。 GLOBAL 値を指定するには、SUPER 権限が必要になり、また、それ以降に接続するすべてのクライアント操作に影響します。SESSION 値を設定するには、現在設定を行ったクライアントだけに影響します。クライアントは自身の sql_mode セッション値をいつでも変更できます。

次のステートメントで、現行のsql_mode のグローバル値とセッション値を読み取ることができます。

SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

次に、最も重要な sql_mode 値を示します。

ANSI

このモードは、標準 SQL とより同調できるように、シンタックス (構文) と動作を変更する。
STRICT_TRANS_TABLES

指定された値をトランザクションテーブルに挿入できない場合、クエリの実行を中断する。非トランザクションテーブルでは、値が 1 行ステートメントの場合、または複数行ステートメントの最初の行である場合に、クエリを中断する。詳細は、このセクションでの後述を参照のこと。
TRADITIONAL

MySQL を「従来型の」 SQL データベースシステムにように動作させる。このモードの最もシンプルな特徴は、カラムに不正値を挿入するときに、「警告ではなく、エラーメッセージ」を返すことである。注意：INSERT/UPDATE は、エラーを認識すると即座に中断する。そのため、非トランザクション式のストレージエンジンを使用している場合は、使用しない (推奨)。エラー前に変更したデータがロールバックしないため、更新が「部分的に」完了してしまうことがある。

このマニュアルで、「Strict Mode」と示すときは、STRICT_TRANS_TABLES、STRICT_ALL_TABLES の少なくなくともどちらかが有効化しているモードである、という意味です。

次のリストはサポートしている全モードの説明です。

ALLOW_INVALID_DATES

日付の完全チェックを行わずに、月は 1 から 12 まで、日は 1 から 31 までであることだけをチェックする。これは、たとえば、Web アプリケーションなどで年月日をそれぞれのフィールドで取得し、(日付に関するバリデーションなしで) ユーザが指定した通りに保存する場合に便利である。このモードは DATE と DATETIME のカラムに適用する。TIMESTAMP カラムは常に有効な日付を必要とするため、対象外である。

サーバでは月日の値は正当である必要があり、単に 1 から 12 そして 1 to 31 の範囲であるだけではいけない。Strict Mode を無効化した場合、'2004-04-31' のような入力は無効とみなし、'0000-00-00' と修正される。そのときには、警告が出される。Strict Mode を有効にした場合、無効とみなす日はエラーとなる。'2004-04-31' を許すには、ALLOW_INVALID_DATES を有効にする。
ANSI_QUOTES

引用文字 ‘`’ のように、‘"’ を識別子として扱う。文字列の引用文字ではない。このモードを有効にした場合には、‘`’ を識別子として使用できる。ANSI_QUOTES を有効にした場合には、識別子として解釈されるため、リテラル文字列の引用には二重引用符を使用できなくなる。
ERROR_FOR_DIVISION_BY_ZERO

INSERT または UPDATE 中に、0で除算 (または MOD(X,0)) すると、Strict Mode でエラーを生成する (そうでなければ、警告)。このモードを有効にしない場合、MySQL は 0 除算に対して、NULL を返す。INSERT IGNORE または UPDATE IGNORE では、MySQL が 0 除算に対して警告を生成するが、操作結果は NULL になる。
HIGH_NOT_PRECEDENCE

NOT 演算子の優先順位は、NOT a BETWEEN b AND c を NOT (a BETWEEN b AND c) として構文解析するようなプログラム式である。古い MySQL バージョンでは、(NOT a) BETWEEN b AND c として構文解析している。以前の優先順位動作は、SQL モードの HIGH_NOT_PRECEDENCE を使用すると可能になる。
```
mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 0
mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 1
```
IGNORE_SPACE

関数名と ‘(’ 文字のスペースを許可する。これは、組込み関数名を予約語として扱うようにする。そのため、関数名と同一の識別子を引用符で囲む必要がある。詳細は項8.2. 「識別子」を参照のこと。たとえば、COUNT() という関数があった場合、count をテーブル名として使用すると、次のようなステートメントでエラーが発生する。
```
mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax
```
テーブル名は次のようにする。
```
mysql> CREATE TABLE `count` (i INT);
Query OK, 0 rows affected (0.00 sec)
```
IGNORE_SPACEモードは、ユーザ定義関数 (UDF) または格納された関数ではなく、組み込み関数に適用する。IGNORE_SPACEを有効化しているかどうかに関わらず、ユーザ定義関数または格納された関数の後ろにスペースを入れることは常に可能である。

IGNORE_SPACE に関する詳細は、項8.2.4. 「関数名の構文解析と名前解決」を参照のこと。
NO_AUTO_CREATE_USER

GRANT 文が自動的に新規ユーザを作成しないようにする。空でないパスワードも指定した場合を除く。
NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO は AUTO_INCREMENT カラム処理に影響する。通常。次のシーケンス番号をカラムに生成するときには、NULL または 0 を挿入する。NO_AUTO_VALUE_ON_ZERO は 0 の動作を抑制するため、NULL が次のシーケンス番号を生成する。

このモードでは、0 をテーブルの AUTO_INCREMENT カラムに格納する。しかし、0 を保存するということは、推奨できる方法ではない。たとえば、mysqldump コマンドでテーブルにダンプして、リロードする場合に、MySQL は, 0 という値に遭遇すると、新たなシーケンスン番号を生成し、テーブルにダンプしたものとは異なる内容になるという結果を生む。ダンプファイルをリロードする前に NO_AUTO_VALUE_ON_ZERO を有効にすると、この問題を回避できる。現在、mysqldump は、自動的にその出力に、NO_AUTO_VALUE_ON_ZERO を有効にするステートメントを含むようになったので、これまでの問題を回避できる。
NO_BACKSLASH_ESCAPES

文字列内のエスケープ文字としてのバックスラッシュ (‘\’) を無効にする。このモードを有効にすると、バックスラッシュが特殊文字でなく通常の文字として扱われる。
NO_DIR_IN_CREATE

テーブル作成時に、INDEX DIRECTORY と DATA DIRECTORY の命令をすべて無視する。このオプションはスレーブのレプリケーションサーバで役立つ。
NO_ENGINE_SUBSTITUTION

デフォルトのストレージエンジンの自動置換 (substitution) を防ぐ。これは、CREATE TABLE のようなステートメントが、無効化した、またはコンパイルしたストレージエンジンを指定するときのこと。エラーで知らせる。
NO_FIELD_OPTIONS

MySQL 独自のカラムオプションを SHOW CREATE TABLE の出力に印字しない。このモードは mysqldump コマンドのポータビリティモードで使用される。
NO_KEY_OPTIONS

MySQL 独自のインデックスオプションを SHOW CREATE TABLE の出力に印字しない。このモードは mysqldump コマンドのポータビリティモードで使用される。
NO_TABLE_OPTIONS

MySQL 特化のテーブルオプション (ENGINE など) を SHOW CREATE TABLE の出力に印字しない。ポータビリティモードで mysqldump コマンドを使用する。

NO_UNSIGNED_SUBTRACTION

整数引き算操作で、オペランド (演算数) の一つに符号がない場合には、結果を UNSIGNED としてマークしない。つまり、引き算の結果はモードに効力がある場合は常に符号があり、オペランドの一つに符号がない場合でも同様。. たとえば、テーブル t1 のカラム c2 のタイプと、テーブル t2 の c2 のタイプを比較すると、次のようになる。

mysql> SET SQL_MODE='';
mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
mysql> DESCRIBE t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c2    | bigint(21) unsigned |      |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+

mysql> SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
mysql> DESCRIBE t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c2    | bigint(21) |      |     | 0       |       |
+-------+------------+------+-----+---------+-------+

ノート：これは BIGINT UNSIGNED はすべての文脈で完全には使用できないことを意味する。項11.8. 「キャスト関数と演算子」を参照のこと。.

mysql> SET SQL_MODE = '';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+

mysql> SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+

NO_ZERO_DATE

Strict Mode では、'0000-00-00' は有効な日として扱わない。IGNORE オプションで「ゼロ」の日付を挿入する。Strict Mode ではない場合は、この日付は有効になるが、警告が出る。
NO_ZERO_IN_DATE

Strict Mode では、月日に 0 があった場合は、許可されない。IGNORE オプションで併用した場合は、MySQL が '0000-00-00' と入れ替える。Strict Mode ではない場合は、日付は有効になるが、警告が出る。
ONLY_FULL_GROUP_BY

SELECT リストのクエリを許可しない。このリストは GROUP BY 節で名付けていない未集約のカラムを指す。次のクエリは、address が GROUP BY 節で名付づけていないので、このモードを有効にした場合は無効になる。
```
SELECT name, address, MAX(age) FROM t GROUP BY name;
```
MySQL 5.1.11 以後、このモードは、GROUP BY 節で名付けていない HAVING. の未集約カラムへのリファレンスを制限する。
PIPES_AS_CONCAT

|| を連結演算子として扱う。 (CONCAT() と同様に。) OR のシノニムとしては扱わない。
REAL_AS_FLOAT

REAL を FLOAT のシノニムとして扱う。デフォルトでは、MySQL が REAL を DOUBLE のシノニムとして扱う。
STRICT_ALL_TABLES

すべてのステーレジエンジンに対して、Strict Mode を有効にする。無効データは排除の対象になる。詳細は、次のパラグラフを参照のこと。
STRICT_TRANS_TABLES

トランザクションのストレージエンジンに対して、Strict Mode を有効にする。非トランザクションのストレージエンジンに対しても可能な場合ある。詳細は次のパラグラフを参照のこと。

Strict Mode は、MySQL が無効または不明な入力値をどのように処理するかを制御します。何らかの理由で値は無効になることがあります。たとえば、カラムに対して違うデータの入力、範囲を超える入力などがあった場合です。値が不明であるということは、挿入する新規のレコードに、カラム値がない場合で、定義に、明示的な DEFAULT 節がないときです。

トランザクションテーブルでは、無効または不明な値がクエリにある場合はエラーになります。これは、STRICT_ALL_TABLES または STRICT_TRANS_TABLES のどちらかのモードが有効になっている場合に起こります。クエリは中断、そしてロールバックの対象になります。

最初の行で挿入または更新するときに、bad 値が発生する場合、非トランザクションのテーブルでは、どちらのモードでも同様に動作します。クエリ処理は中断し、テーブルはそのまま変更なしの状態になります。クエリを挿入または複数行を変更した場合に、bad 値が2行目以降に発生する場合は、結果はどの制限オプションを有効に設定しているかに依存します。

STRICT_ALL_TABLES では、MySQL はエラーを返し、残りの行を無視する。そのような場合は、最初の段階の行がまだ挿入中または更新中であり、部分的な更新を得ることになり、それが予期していたものとは異なる可能性がある。これを回避するには、単行ステートメントを使用すると、テーブルを変更することなく、中断できる。
STRICT_TRANS_TABLESでは、MySQL は無効値を、カラムに対して最も近い有効値に置換し、調整した値を挿入する。値が不明である場合は、MySQL は、カラムのデータタイプに明示的なデフォルト値を挿入する。どちらの場合でも、MySQL はエラーではなく、警告を出し、クエリ処理を続行する。明示的なデフォルトに関しては、項10.1.4. 「データタイプデフォルト値」を参照のこと。

Strict Mode では、'2004-04-31' を無効な日付として扱います。'2004-04-00' または「ゼロ」日など、0 が部分的に含まれた日付は無効ではない。これらも無効として扱うには、Strict Mode の他に、NO_ZERO_IN_DATE と NO_ZERO_DATE の SQL モードも追加する。.

Strict Mode を使用しない場合、つまり、STRICT_TRANS_TABLES または STRICT_ALL_TABLES のどちらかを有効にした場合、MySQL は無効または不明な値の代わりに調整した値を挿入し、警告を出す。Strict Mode では、INSERT IGNORE または UPDATE IGNORE を使用して、このような動作を導くことが可能。項12.5.4.31. 「SHOW WARNINGS 構文」を参照のこと。

次に示すモードは特化型のモードで、前述したモード値のコンビネーションの省略表現でもあります。

説明にあるモード値は、最新の MySQL バージョンで利用できます。古いバージョンの場合は、コンビネーションモードで新しいバージョンでだけ使えるモードを使用しているため、利用できません。

ANSI

REAL_AS_FLOAT、 PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE に相当。項1.8.3. 「ANSIモードでのMySQLの実行」を参照のこと。
DB2

PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS に相当。
MAXDB

PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER に相当。
MSSQL

PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS に相当。
MYSQL323

NO_FIELD_OPTIONS、 HIGH_NOT_PRECEDENCE に相当。
MYSQL40

NO_FIELD_OPTIONS、 HIGH_NOT_PRECEDENCE に相当。
ORACLE

PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER に相当。
POSTGRESQL

PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS に相当。
TRADITIONAL

STRICT_TRANS_TABLES、 STRICT_ALL_TABLES、 NO_ZERO_IN_DATE、 NO_ZERO_DATE、 ERROR_FOR_DIVISION_BY_ZERO、 NO_AUTO_CREATE_USER に相当。